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


[Table="class: grid"][tr][td="bgcolor: #DCE6F1"][/td][td="bgcolor: #DCE6F1"]

A​

[/td][td="bgcolor: #DCE6F1"]

B​

[/td][td="bgcolor: #DCE6F1"]

C​

[/td][td="bgcolor: #DCE6F1"]

D​

[/td][td="bgcolor: #DCE6F1"]

E​

[/td][/tr]
[tr][td="bgcolor: #DCE6F1"]

1​

[/td][td]

Text​

[/td][td]

Result​

[/td][td][/td][td]

ITEM​

[/td][td]

RESULT​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

2​

[/td][td]

blah item3​

[/td][td]

Result3​

[/td][td][/td][td]

Item1​

[/td][td]

Result1​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

3​

[/td][td]

item2 blah blah​

[/td][td]

Result2​

[/td][td][/td][td]

Item2​

[/td][td]

Result2​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

4​

[/td][td]

item1​

[/td][td]

Result1​

[/td][td][/td][td]

Item3​

[/td][td]

Result3​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

5​

[/td][td]

blah item5 blah​

[/td][td]

Result5​

[/td][td][/td][td]

Item4​

[/td][td]

Result4​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

6​

[/td][td][/td][td][/td][td][/td][td]

Item5​

[/td][td]

Result5​

[/td][/tr]

[tr][td="bgcolor: #DCE6F1"]

7​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[/table]

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.