Reference Spreadsheet >> Spreadsheet - I need a formula in column B that searches through column A for text values that exist in the legend (column E), then if text is found, assign corresponding value that exists in column F.
The alternative for this is to use a formula, but instead of this, I want to use a table that I can modify rather than modifying the formula each time I need to update values. This is the formula I currently have
=IF(ISNUMBER(SEARCH("First",A3)),"One",(IF(ISNUMBER(SEARCH("Second",A3)),"Two",(IF(ISNUMBER(SEARCH("Third",A3)),"Three",(IF(ISNUMBER(SEARCH("Fourth",A3)),"Four",(IF(ISNUMBER(SEARCH("Fifth",A3)),"Five")))))))))
Alternate without CSE or the volatile OFFSET function.
In B3 as,
Fill down as necessary.
For a case-sensitive lookup, change SEARCH to FIND.
Confirmed with ctrl+shift+enter
Late answer...
=LOOKUP(2^15,SEARCH($E$3:$E$8,A3),$F$3:$F$8)