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

Here is what I'm trying to do:

I want to search the text in Cell A1 for any of the items in the Lookup table column A30 through A100 and return the value from Vlookup table Column B30-B100 in B1. I have a lot of possible variables, so naming them specifically in the formula will not work due to limited about of characters allowed in cell formula.

Here are three examples of how it should look based on what is entered in A1:
A1= TOY_STORAGE_RM_HI_EXAMPLE B1=ROOM
A1= THE DR IS OPEN B1= DOOR
A1= THE DB IS ERRATIC B1= DEBOUNCE


VLOOKUP TABLE IS A30:B100

A30= RM B30=ROOM
A31= DR B31=DOOR
A32= DB B32=Debounce
A33 through B100 will be a whole list of key words to search for in A1

I was trying to use something like ISNUMBER(SEARCH(VLOOKUP(A1,A:30:B:100,2),A1)) in my formula but it only results in "FALSE"

ANY ideas how to make this work?

I'm setting it up soy that they will be unique.

Originally Posted by Ron Coderre

Is it possible/probable that you'll have more than one matched item?

Originally Posted by FlameRetired

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

Formula:

Please Login or Register  to view this content.

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.

Why the number 2 in the Lookup_Value? I played around with it and it seems as long as the number is any whole number greater than zero, it works?