Improve fuzzy match filter in Excel

Question

I work with a kind of fuzzy match filter, based on additional column with a filter list. The formula is:

=IF(SUMPRODUCT(COUNTIF(A2,"*"&B$2:$B$22&"*"))>0,"Delete","Keep")

In fact there are two formulas, they work on the same kind - they were created on experimenting. The second is:

=IF(SUMPRODUCT(--ISNUMBER(SEARCH($B$2:$B$22,A2))),"Delete","Keep")

• Column A contains data to filter,
• column B (from B2 to B22) contains the filter list,
• in the column C i write "keep", if there is no partly match of the value from column A with values from column B, and write "delete", if there is any partly match.

Q: how to write instead of "delete" the matching value from column B? I can't get this work in both of formulas.

Update After translation of formula by @Jerry

=IFERROR(INDEX($B$2:$B$22,MATCH(9^99,SEARCH($B$2:$B$52,A2))),"Keep")

to

=WENNFEHLER(INDEX($B$2:$B$22;VERGLEICH(9^99;SUCHEN($B$2:$B$52;A2)));"Keep")

with this translation tool (worked for me in other cases errorfree), i get following result:

which is another, than the result by Jerry.

Answer 1

For all who is interested: this formula does the trick:

=IFERROR(LOOKUP(9.99E+307,SEARCH(B$2:B$22,A2),B$2:B$22),"Keep")

Answer 2

If there can be only 1 match, then you can use INDEX and MATCH in an array formula (use Ctrl+Shift+Enter and you will see curly parens around the formula in the formula bar if you did it right):

=IFERROR(INDEX($B$2:$B$22,MATCH(9^99,SEARCH($B$2:$B$22,A2))),"Keep")

If there are more than one match, you will get the last match with the above formula. If you wish in that case to return the first formula, you will have to use --ISNUMBER around the search function, use 1 for the first parameter of MATCH and use exact match (i.e. use 0/FALSE for the 3rd parameter of MATCH.

Of course, you can use COUNTIF(A2,"*"&B$2:$B$22&"*") instead in that case for the inner part of the formula instead of --ISNUMBER(...).