Google Sheet: How to compare two columns and find the exact or partial match?

Farlar 0
Viewed 33

I have to compare two columns and highlight the text when it's exact or partial match in google sheet.

My data:

Working Data (A Column) Matching List data( B Column)
Jhon Jhon Doe
Cycle Industries LTD Cycle Industries
Cycle Industries Manufacture Company
Cycle Industries Engineering Company
Food
Engineering Company

I will give entry to the column A, and it will match the column B data and marked the match one.

I have tried this custom formula in conditional formating:

=COUNTIFS(B:B,A1)>0

But its only marked the exact match data. How can I find the maximum partially match data and exact match data also?

google-sheets-formula google-sheets
1 Answers

If you want the double match you can use:

=OR(ARRAYFORMULA(SUM(--REGEXMATCH($B$2:$B,A2))),--REGEXMATCH(A2,TEXTJOIN("|",TRUE,$B$2:$B)))

The first match if A is in B, 2nd match if B is in A, you can use one of these or combined as I wrote, this marks every entry in A except food

bye.

Terio0
Related