XLSX xlookup to Search for a word/phrase return matches

Occasional Visitor

Hello!

Disclaimer:  It's been years since I have worked in excel and desperately need help.

 

I have a spreadsheet (example below) where one column has paragraphs of text (Column C - "3rd Party Description") that I have to identify key words columns D-F. I need a formula that will take the word or phrase in D2 and search all of column B (SOR Description) and return multiple Record IDs from column A, then again start with the word or phrase in E2 and do the same, return the multiple Record ID where that word or phrase in column B.  There may also be some nulls, where it will not find any of the words or phrases.Screen Shot 2021-11-15 at 12.37.23 PM.png

1 Reply

@MMC32 

Perhaps

image.png

with

=IFERROR(
  TEXTJOIN( ",", 1,
     FILTER( $A$2:$A$7,
             MMULT(--ISNUMBER( SEARCH( IF( ISBLANK(D2:F2), UNICHAR(9999), D2:F2), $B$2:$B$7) ),
                   {1;1;1} ) ) ),
"")