Extract 1st matching substring from cell

I am trying to make a simple formula to populate a cell with the first matching substring found in a data cell (from a fixed list of substring keys).

Example of desired results:

Substring data: {X, YY, ZZ} (typically 6 to 15 values in real world use)

Data..........Calculated result..........Comment
AAX.................X........................Substring X was found in Data.

ABC.............[empty]...................None of substrings were (X, YY, ZZ were found)

XYYZZ.............X......................(only first match need be returned)

ABCZZ.............ZZ....................Substring ZZ found etc….



I am looking for something cleaner than nested if statements since there are likely to be a lot of values and different variations. The work would then be to copy the formula and populate the list of substrings to search for in the data.

I tried some array formulas using search() but no luck so far.

Thanks,
Mike

Try (assuming your first string is in A2):

Please Login or Register  to view this content.

Which must be confirmed with CTRL+SHIFT+ENTER not just ENTER... you will see { } brackets appear.. then copy down..

You can replace the substring: {"X","YY","ZZ"} with a range containing those values (without quotes).

Any changes to the formula require reconfirmation using CSE keys.

With
A1:A4 containing this list:
AAX
ABC
XYYZZ
ABCZZ

and....F1:F3 containing this list
X
YY
ZZ

Try this...

Please Login or Register  to view this content.

Copy that formula down through B4.

In the above example, the formulas return these values:

Please Login or Register  to view this content.

Is that something you can work with?