Tom’s Tutorials For Excel: Match From List
The below picture shows one way to see if any member of a collection of smaller strings (what I call “Key Codes”) are found in a much longer Product Code string.
The formulas in the yellow cells search the entire Key Code range of A4:A8. If any Key Code is found to exist in the Long Product Code, a match is made and the particular Key Code is returned to indicate that match.
The formula in cell D4 and copied down to cell D25 is
This is an array formula, which goes on one line as all formulas do, but I broke it into two lines here so it can be read on this web page by all internet browsers.
Recall that for array formulas, you apply them to a cell with Ctrl+Shift+Enter, not just with Enter.