Using VLOOKUP inside IFNUMBER(Search) to extract text in cell to lookup

Originally Posted by FlameRetired

An after thought. Change the formula above to "standardize" the term in A1 like this

Formula:

=LOOKUP(2,1/(ISNUMBER(FIND($A$30:$A$100,SUBSTITUTE(A1," ","_")))*($A$30:$A$100<>"")),$B$30:$B$100)

Then in A30:A100 have the search terms all with a leading and trailing "_" underscore.

See the attached. Rows 5:26 are hidden.

Almost Perfect. I put it in an IFERROR and now it is perfect. There are possibilities where I may not have a value it can find, so I will have it leave the cell blank.
Here is what it looks like now:

=IFERROR(LOOKUP(2,1/(ISNUMBER(FIND($A$30:$A$100,SUBSTITUTE(A1," ","_")))*($A$30:$A$100<>"")),$B$30:$B$100),"")

Thank you! and everyone else for your suggestions. It's great having so many knowledgeable people to support each other.