Find Values Missing From Another List

Excel & Google Sheets

How it works

COUNTIF(B:B,A2) counts how many times the value in A2 appears anywhere in column B. If that count is zero, the value never appears in the second list, so the formula returns Missing; any count of one or more returns In list. Copy the formula down and every value in column A gets checked. Unlike a row-by-row comparison, this works even when the two lists are sorted differently or have different lengths, because it searches the entire column rather than the matching row.

COUNTIF(B:B,A2)

Counts how many times the value in A2 appears anywhere in column B.

=0

TRUE when the value was never found — that's what makes it missing.

"Missing"

What to show when the value doesn't exist in the other list.

"In list"

What to show when the value appears at least once.

When to use it

Use this when reconciling any two lists: invitees vs. registrants, last month's customers vs. this month's, payroll vs. the HR roster, or orders placed vs. orders shipped.

Common mistakes

• ✕Checking the lists in only one direction.

  ✓This formula finds A-values missing from B. To find B-values missing from A, add a second column with =IF(COUNTIF(A:A,B2)=0,"Missing","In list").

• ✕Hidden spaces make matching values look missing.

  ✓"Ana Torres " with a trailing space won't match "Ana Torres". Clean both columns with TRIM first, or compare on =IF(COUNTIF(B:B,TRIM(A2))=0,"Missing","In list").

• ✕Comparing numbers stored as text against real numbers.

  ✓COUNTIF treats the text "104" and the number 104 as different values. Convert one side — multiply the text column by 1, or use Data > Text to Columns — so both are the same type.