Search formula with lookup

[bobby_smith]

Hi All,

I'm trying to write an alternative to formula below:
If(isnumber(search ("Item1",a2)),"Result1",if(isnumber(search("Item2",a2)),"Result2",if(.....

The formula is looking for a specific word in string of text and if found, returning a result.

I'm looking for a more efficient way in the event I have a long list of words I would like to search for and not having to write a long If formula.

I was thinking that I can integrate the search into a Vlookup / Lookup/ Index Match formula so that once the word is found in the string, I can lookup from a table.
I've however not been able to create such a formula.

Your assistance will be greatly appreciated.

Thanks

 

[Marcelo Branco]

Create a table Item - Result and use LOOKUP

Something like this - table in columns D:E

	A​	B​	C​	D​	E​
1​	Text​	Result​		ITEM​	RESULT​
2​	blah item3​	Result3​		Item1​	Result1​
3​	item2 blah blah​	Result2​		Item2​	Result2​
4​	item1​	Result1​		Item3​	Result3​
5​	blah item5 blah​	Result5​		Item4​	Result4​
6​				Item5​	Result5​
7​

Formula in B2 copied down
=LOOKUP(9.99E+307,SEARCH(D$2:D$6,A2),E$2:E$6)

M.

 

[bobby_smith]

Thanks Marcelo, this is exactly what I wanted.