CODEWITHSUNDEEP
regexgoogle-sheetsgoogle-sheets-formulaarray-formulaspartial-matches
PatFran
PatFran

Reputation: 29

Finding partial text string matches between two columns

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?

Upvotes: 1

Views: 332

Answers (1)

player0
player0

Reputation: 1

try:

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

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

0

where you just change a #gid of the sheet

Upvotes: 2

Related Questions