Finding partial text string matches between two columns

Question

I have a list of values in Column A (test), and a list of values in Column B (test 2). Many names in Column B contain a partial match with at least 1 value in column A.

Example:

Column A Global Investors

Column B Global Investors Capital

What kind of formula could I run that could show me the location of a partially matched value in column A?

Answer 1

try:

=ARRAYFORMULA(IFNA("A"&MATCH(IFNA(REGEXEXTRACT(LOWER(B2:B), 
 LOWER(TEXTJOIN("|", 1, SORT(A2:A, 1, 0))))), A:A, 0)))

---

for jump link you can do:

=ARRAYFORMULA(IF(B2:B="",,HYPERLINK("#gid=151561529&range="&
 IFNA("A"&MATCH(IFNA(REGEXEXTRACT(LOWER(B2:B), 
 LOWER(TEXTJOIN("|", 1, SORT(A2:A, 1, 0))))), A:A, 0)), "jump")))

where you just change a #gid of the sheet