Excel lookup in list of substrings

3
votes

I am looking for a formula in MS Excel to check if a cell, containing a sentence, matches (at least) one substring in a given range, and if it does, return that substring-value.

I can best explain it using following example. If the sentence (on the right) contains one of the fruits defined in the table (on the left), the result should return the (first) matching fruit:

New example

Preferable, the lookup function should be case insensitive. Also, it is not possible for me to use VBA - it would need to be performed using Excel-native functions.

Thank you in advance!

1
excelexcel-formula
Will there be only 1 substring in each sentence? If not, how do you want to handle multiple matches? – jblood94
Yes, normally, the substrings will be complex enough so that only one substring would occur in each sentence. In case multiple would occur, it is fine to just return the first found result (similar to VLOOKUP). – Ad Fundum

1 Answers

3
votes

You can use SEARCH for that, in an array formula. Then use INDEX and MATCH with the results of SEARCH:

=INDEX(Substrings, MATCH(TRUE, ISNUMBER(SEARCH(Substrings, Sentence)), 0))

Since it's an array formula, you need to use Ctrl+Shift+Enter after typing out the formula instead of pressing only Enter.

EDIT: I forgot to mention that the above formula is case sensitive. Thanks jblood94 for mentioning it. The following formula uses LOWER to make it case insensitive.

=INDEX(Substrings, MATCH(TRUE, ISNUMBER(SEARCH(LOWER(Substrings), LOWER(Sentence))), 0))