Trouble retrieving the matched criteria using Sear

问题:

I am using the code below successfully to test every cell in a table's column (task_short_text column) to see if they contain a specific word within each one of them. The range of words I am searching within each cell is stored in another sheet (Parameters!$A$2:$A$15). The way this formula has worked is by returning a TRUE or FALSE result, Ex: If the string in the cell contains "The Dog barks" and the word "dog" is in the search range, the output will be TRUE.

Instead of returning TRUE, I want my formula to return the word it found in the search range. So, for example, if the formula finds "dog" in the Parameters sheet, I would like for the return to be "dog" instead of TRUE.

I have tried using the SEARCH function without the SUMPRODUCT and ISNUMBER functions, but this yields an error (#VALUE!)

=SUMPRODUCT(--ISNUMBER(SEARCH(Parameters!$A$2:$A$15,[@[task_short_text]])))>0

回答1:

Here is a simple example to approach this issue:

Formula in cell B2:

=IFERROR(LOOKUP(1,1/COUNTIF(A2,"*"&$D$2:$D$4&"*"),$D$2:$D$4),"")

Drag down.