Compare two columns for matches
MATCH looks for each value in the other column. If it finds a position, ISNUMBER is true and the row is labeled as in both lists.
=IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A")The sheet: Column A is this month's customer list and column B is last month's. You want to see which customers in A also appear in B.
How it works, step by step
- 01MATCH(A2,$B$2:$B$100,0) searches the B list for the exact value in A2 and returns its position.
- 02If the value is not there, MATCH returns #N/A.
- 03ISNUMBER turns a position into TRUE and #N/A into FALSE.
- 04IF converts that into a readable label. The $ signs keep the B range fixed as you fill down.
The same logic in four tools
=IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A")=IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A")SELECT a.customer,
CASE WHEN b.customer IS NULL THEN 'Only in A' ELSE 'In both' END AS status
FROM list_a a
LEFT JOIN (SELECT DISTINCT customer FROM list_b) b ON b.customer = a.customer;import numpy as np
df_a["status"] = np.where(df_a["customer"].isin(df_b["customer"]), "In both", "Only in A")Mistakes that break it
- !MATCH ignores capitalization in Excel and Sheets, but isin is case sensitive and SQL = depends on the database collation.
- !Trailing spaces make identical names look different. Clean both columns with TRIM.
- !Run it a second time with the columns swapped to find values that are only in B.
- !Numbers stored as text in one column will not match numbers in the other.
Common questions
- Is COUNTIF an alternative?
- Yes, =IF(COUNTIF($B$2:$B$100,A2)>0,"In both","Only in A") gives the same labels and is easier to read.
- How do I highlight the matches instead?
- Use conditional formatting with the formula =COUNTIF($B$2:$B$100,A2)>0.
Got a version of this with your own ranges?
Paste it into the free translator and get the same breakdown for your exact formula. To run it against a real file, upload the CSV and describe the formula you need in a sentence.