1

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.

2
  • Welcome to Web Applications Stack Exchange. You are asking to to get the row and column positions of matches, which is seldom useful information in itself. Is this an XY problem? Dec 8, 2022 at 7:47
  • Google Sheets and Microsoft Excel have different feature sets. Please remove the [excel] tag. Its description explicitly states "Questions with this tag should ONLY be about how Microsoft Excel interacts with a specific web application. General questions about Microsoft Excel are off-topic here but can be asked at Super User." Dec 8, 2022 at 14:02

2 Answers 2

0

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

0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.