Excel lookup in list of substrings

Question

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:

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!

Answer 1

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))