Is there a google sheets formula that matches two tables to find approximate matches?

Question

I am trying to see how you can match two data sets, but only return the matched value. see image of an example data set

I want the formula to read List 1 and compare to List 2 and be able to denote that row 2,3,5,6 and 8 contains values that are similar to what is in List 2

I used the following formula, but the match rate was low

arrayformula(if(isblank(G7:G50000)=TRUE,"", vlookup("*"&G7:G50000&"*",Dropdowns!K:K,1,false)))

Tried this formula, but Aggregate formula does not work in Google Sheets:

IFERROR(INDEX(Dropdowns!K:K,AGGREGATE(15,6,ROW(Dropdowns!$K$2:$K$500)/(ISNUMBER(SEARCH(Dropdowns!$K$2:$K$500,G8))),1)),"")

Tried the following formula, but "contains" needs to be specific and it doesn't allow me to search from a table.

=query(K:K,"Select K where K contains 'Lead'")

Can you post a "from this -> to this" example that you want to achieve? — Aerials
What kind of contents is it? numbers or strings? . Please show some data and your desired result. — Krzysztof Dołęgowski
Its strings. I have edited the question to include more info — JD12

Krzysztof Dołęgowski Krzysztof Dołęgowski · Accepted Answer · 2021-03-03T21:29:42

The problem with "similarity" is very complex and can be understood in many ways.

What you show in your example is checking whether a string in table 2 is included in table 1.

You can test it using regexextract:

=arrayformula(ifna(REGEXEXTRACT(B3:B7,join("|",F3:F4))))

This formula lets you build longer tables and only limit is 50 000 characters for table 2 (together with | separator).

Is there a google sheets formula that matches two tables to find approximate matches?

1 Answers