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
