Google Sheets: return multiple values of substring search

Question

Lets say I had a sheet like this

	COLA	COLB	COLC
ROW1	blah	blahbingo	blah
ROW2	blahmagic	blah	blah
ROW3	bingoblah	blah	magicblah

And then I had a sheet like:

R	C		Substrings
			magic
			bingo

What formula could I use to get that second sheet to do this:

R	C		Substrings
ROW1	COLB		magic
ROW2	COLA		bingo
ROW3	COLA
ROW3	COLC

I want to find everywhere in sheet1 that one of the substrings match a cell and then return row header and column header of that cell. I'm specifically using Google Sheets but also tagged excel since there is so much overlap btw the 2 products.

Answer 1

You can apply something like this:

=LAMBDA(data,FILTER(data,data<>""))(flatten(map(A1:D1000,LAMBDA(each,IF(REGEXMATCH(each,join("|", FILTER(H1:H100,H1:H100<>""))),"R"&ROW(each)&"C"&COLUMN(each),"")))))

Change the ranges. In my example H contains the words you want to look up

Answer 2

To get a list of the row labels in column A, the column labels in row 1, and the matching value, use this pattern:

=arrayformula( 
  lambda( 
    rowLabels, columnLabels, data, searchKeys, 
    lambda( 
      row, col, data, queryStatement, 
      split( 
        query( 
          flatten(row & "→" & col & "→" & data), 
          queryStatement, 
          0 
        ), 
        "→" 
      ) 
    )(
      filter(rowLabels, len(rowLabels)), 
      filter(columnLabels, len(columnLabels)), 
      filter( filter(data, len(rowLabels)), len(columnLabels) ), 
      "where Col1 contains" & join( " or Col1 contains ", 
        filter("'" & searchKeys & "'", len(searchKeys)) 
      ) 
    )
  )(Sheet1!A2:A, Sheet1!B1:D1, Sheet1!B2:D, D2:D)
)

...where D2:D contains the search keys you want to match to the data.