Search code examples
regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Is it possible to find as much matching words from different sentences located in different columns in Google Sheets?

There are sentences in column A and B. I'm trying to find which sentences have the most matching words and return that sentence from column B.

+-----------------------+-----------------------+----------------------+
|Column A               |Column B               |Return most matched   |
+-----------------------+-----------------------+----------------------+
|this is a black car    |Twinkle little star    |that is a black couch |
+-----------------------+-----------------------+----------------------+
|there is a red cat     |London Bridge is Fallin|red cat is in the hat |
+-----------------------+-----------------------+----------------------+
|I see a twinkle star   |red cat is in the hat  |Twinkle little star   | 
+-----------------------+-----------------------+----------------------+
|London tower is standin|that is a black couch  |London Bridge is Fallin
+-----------------------+-----------------------+----------------------+

Beginner spreadsheet programmer here. Not sure if this is even possible? Could someone please point me in the right direction?

I've googled many that match single words but not match most matched words in sentences.

Solution

  • paste in C2 and drag down:

    =ARRAYFORMULA(QUERY({B$2:B, MMULT(IFERROR(--REGEXMATCH(SPLIT(LOWER(B$2:B), " "), 
 "^"&SUBSTITUTE(LOWER(A2), " ", "$|^")&"$"), 0), 
 ROW(INDIRECT("A1:A"&COLUMNS(SPLIT(B$2:B, " "))))^0)}, 
 "select Col1 order by Col2 desc limit 1", 0))

    0