SheetSense

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.

fx
=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

  1. 01MATCH(A2,$B$2:$B$100,0) searches the B list for the exact value in A2 and returns its position.
  2. 02If the value is not there, MATCH returns #N/A.
  3. 03ISNUMBER turns a position into TRUE and #N/A into FALSE.
  4. 04IF converts that into a readable label. The $ signs keep the B range fixed as you fill down.

The same logic in four tools

Excel
=IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A")
Google Sheets
=IF(ISNUMBER(MATCH(A2,$B$2:$B$100,0)),"In both","Only in A")
SQL
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;
Python (pandas)
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.