Hi,
Am trying to find a way to lookup any word from within a sentence(from one cell) that doesn't have a specific position or number of characters?
For example:
In cell A3 I've got "The cat is brown", I want to lookup the word Cat and return the Type from the lookup table "Animal"
| A | B | C | D | E | |
| 1 | Data | Lookup table | |||
| 2 | Desc. | Result needed | Lookup | Type | |
| 3 | The cat is brown | Animal | Cat | Animal | |
| 4 | Parrot can fly | Bird | Parrot | Bird |
Hi Prashani,
You can use this formula:
=INDEX($E$3:$E$4,SUMPRODUCT(ISNUMBER(SEARCH($D$3:$D$4,A3))*ROW($1:$2)))
As explained in this post on search string in Excel for a list of words.
Mynda
Thanks Mynda, looks like this is going to work... Yay!! so happy that with excel there is always a way 
AND as always thanks for your help.
Dear Mynda,
I follow your formula
=INDEX($E$3:$E$4,SUMPRODUCT(ISNUMBER(SEARCH($D$3:$D$4,A3))*ROW($1:$2))) As i add more row to data and lookup table, A5= He can sing A6= She can write D5= He D6= She E5= Human E6 = Human the formula does not work. The result for B3 #N/A Please advice. Thank you
Hello David,
It is always better if you also upload a sample file.
What Excel version do you have? If you do not have 365 version then you most likely need to end the formula with CTRL + SHIFT + ENTER, as it is an array formula.
Another source of error, as you have extended the data with two more rows, have you altered the formula so it includes the added rows?
Lastly, your choice of lookup words makes the formula result less accurat, as the word He is also found in the words The and She (as the SEARCH function do not care of what case the text has). I don't get an #N/A error in cell B3 because of this, I instead get Human as result instead of Animal (because SUMPRODUCT results to 4) and in cell B6 (where you look after the word She) I get #REF! error, that is because SUMPRODUCT results as 7 (due to two TRUE lookup results, one for He and the second for She), but we only have four rows in our INDEX so there is no data.
So to conclude, this formula works, but is not foolproof. The more data you add, the more likely it will be that you get incorrect results. If you instead of SEARCH use FIND, you do a case sensitive lookup and because the lookup word He is with a capital H you now no longer get a result for the *he in The and She (if you of course don't spell it as THe and SHe).
So, once again, the formula works with added data. Do note that if the SUMPRODUCT part results as 0, INDEX will give the list of types (column E) as is, regardless of lookup values.
Br,
Anders
