regex - 是否可以从位于 Google 表格的不同列中的不同句子中找到尽可能多的匹配词？

Question

A 列和 B 列中有句子。我试图找出哪些句子有最匹配的单词并从 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
+-----------------------+-----------------------+----------------------+

初学者电子表格程序员在这里。不确定这是否可能？有人可以指出我正确的方向吗？

我用谷歌搜索了很多匹配单个单词但不匹配句子中大多数匹配单词的单词。

标签： regexgoogle-sheetsgoogle-sheets-formulaarray-formulasgoogle-sheets-query

Answer 1

粘贴在C2中并向下拖动：

=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))