比较两列并找出缺失值：Excel 与 Google Sheets

Which versions this applies to

All of the results below come from Google Sheets on November 12, 2026. COUNTIF and MATCH are in older Excel as well, per Microsoft’s documentation. FILTER and XLOOKUP need a current Excel (Microsoft 365 / Excel 2021) or Google Sheets. I did not re-test these lists in desktop Excel today. In Google Sheets, FILTER spilled on its own; a column of IF/COUNTIF flags needed ARRAYFORMULA.

The lists I used

Column A (five SKUs): SKU-101, SKU-103, SKU-104, SKU-107, SKU-110.

Column B (four SKUs): SKU-103, SKU-104, SKU-108, SKU-110.

What should come out:

• In A, not in B: SKU-101, SKU-107
• In B, not in A: SKU-108
• In both: SKU-103, SKU-104, SKU-110

I also kept a sixth A cell, SKU-103 with a trailing space, and later a lowercase sku-103, as traps.

Flag each row with COUNTIF, MATCH, or XLOOKUP

Against B2:B5, starting in row 2 of column A:

=COUNTIF($B$2:$B$5,A2)
=MATCH(A2,$B$2:$B$5,0)
=XLOOKUP(A2,$B$2:$B$5,$B$2:$B$5,"missing")

Results:

COUNTIF is the simplest flag: 0 means the value is not in B. MATCH with match type 0 (exact) returns the position in B, or #N/A if it is missing — that #N/A is a genuine miss, not a broken formula, as in Excel & Google Sheets Lookup Errors. XLOOKUP is the same lookup with a readable fallback; I passed "missing" as if_not_found so the cell would not sit on #N/A.

If you only need a yes/no column, COUNTIF is enough. Pull MATCH or XLOOKUP in when you also want the position or a cleaned copy of the B value.

List the missing values with FILTER

To spill the A values that are not in B, in one cell:

=FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)=0)

That spilled SKU-101 and SKU-107. I did not wrap it in ARRAYFORMULA; FILTER took care of the array in Google Sheets.

The other direction, B values that are not in A:

=FILTER(B2:B5,COUNTIF(A2:A6,B2:B5)=0)

That spilled SKU-108.

The values in both lists:

=FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)>0)

That spilled SKU-103, SKU-104, and SKU-110, in A’s order, not B’s.

If you want a flag next to every A row rather than a spill of the misses, wrap IF and COUNTIF in ARRAYFORMULA in Sheets:

=ARRAYFORMULA(IF(A2:A6="","",IF(COUNTIF(B2:B5,A2:A6)=0,"missing from B","in both")))

That returned missing from B, in both, in both, missing from B, in both down the five SKUs. Current Excel can often enter the IF/COUNTIF form as a dynamic array without ARRAYFORMULA; I did not verify that in desktop Excel today.

Trailing spaces and letter case

The sixth A cell was SKU-103 plus a trailing space. Against the same B2:B5 (SKU-103 with no space):

=COUNTIF($B$2:$B$5,A7)     → 0
=MATCH(A7,$B$2:$B$5,0)     → #N/A
=XLOOKUP(A7,$B$2:$B$5,$B$2:$B$5,"missing")    → missing

The extra space made a value that exists in B look missing. TRIM fixed it:

=MATCH(TRIM(A7),$B$2:$B$5,0)     → 1
=XLOOKUP(TRIM(A7),$B$2:$B$5,$B$2:$B$5,"missing")    → SKU-103

If a column looks identical and still comes back as missing, check length and TRIM before you rewrite the formula. See TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160) for the stubborn cases (non-breaking spaces, and so on).

Letter case was the opposite problem: it did not make a miss. I compared lowercase sku-103 with SKU-103 in B:

=COUNTIF($B$2:$B$5,"sku-103")    → 1
=MATCH("sku-103",$B$2:$B$5,0)    → 1
=XLOOKUP("sku-103",$B$2:$B$5,$B$2:$B$5,"missing")    → SKU-103
=EXACT("sku-103","SKU-103")      → FALSE

COUNTIF, MATCH, and XLOOKUP all treated the two as the same SKU. EXACT confirmed they are not the same text. If you have to treat sku-103 as different from SKU-103, do not rely on these three functions alone; you need an EXACT-based test (the conditional-formatting version is in the highlight duplicates article).

Excel and Google Sheets differences

In this Sheets file, FILTER spilled without ARRAYFORMULA, and a row-by-row IF/COUNTIF flag list needed ARRAYFORMULA. XLOOKUP is available here and in current Excel; it is not in Excel 2016 or 2019 according to Microsoft. In an older Excel workbook, COUNTIF and MATCH (or IFNA(MATCH(...),...)) still flag the misses; you just will not get a dynamic FILTER spill.

A further comparison of XLOOKUP, INDEX/MATCH, and VLOOKUP is in XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?.

Common mistakes

• Forgetting to lock the B range ($B$2:$B$5) when you fill a COUNTIF or MATCH down column A. The lookup window will slide and invent misses.
• Treating #N/A from MATCH as a broken formula. For a value that is not in B, #N/A is the expected exact-match result.
• Leaving a trailing space in one column. SKU-103 plus a space had COUNTIF 0 and XLOOKUP "missing" until I wrapped the lookup value in TRIM.
• Assuming COUNTIF is case-sensitive. sku-103 matched SKU-103 in this test.
• Wrapping FILTER in ARRAYFORMULA out of habit, or forgetting ARRAYFORMULA around a column of IF/COUNTIF flags in Sheets. Only the second form needed it here.

FAQ

How do I list values in column A that are not in column B?

=FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)=0) spilled SKU-101 and SKU-107 from the test lists. For a flag on each row, COUNTIF(...) = 0 is enough.

How do I list values that appear in both columns?

=FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)>0) spilled SKU-103, SKU-104, and SKU-110 in A’s order.

Why does a value I can see in both columns still show as missing?

In this file the cause was a trailing space on SKU-103. TRIM made MATCH return 1 and XLOOKUP return SKU-103. Case was not the cause: lowercase sku-103 already matched.

Is the comparison case-sensitive?

Not for COUNTIF, MATCH, or XLOOKUP in this test. Use EXACT if you need case to matter (EXACT("sku-103","SKU-103") was FALSE).

Do I need ARRAYFORMULA in Google Sheets?

For FILTER, no, not in this file. For a column of IF/COUNTIF labels entered in one cell, yes: the ARRAYFORMULA version filled five flags correctly.

Will FILTER work in Excel 2016?

Microsoft does not list FILTER or XLOOKUP for Excel 2016 or 2019. Use COUNTIF or MATCH there, or upgrade to a current Excel. I did not run an Excel 2016 workbook as part of this test.

Related tutorials

• Highlight Duplicates Across Two Columns in Google Sheets (Conditional Formatting)
• XLOOKUP vs INDEX MATCH vs VLOOKUP: Which Should You Use in 2026?
• Excel & Google Sheets Lookup Errors: The Complete Troubleshooting Guide
• TRIM Not Working in Excel? Remove Non-Breaking Spaces (CHAR 160)

Live-test findings

Google Sheets, November 12, 2026. Lists A: SKU-101, SKU-103, SKU-104, SKU-107, SKU-110. Lists B: SKU-103, SKU-104, SKU-108, SKU-110. Row flags: COUNTIF 0/1/1/0/1, MATCH #N/A/1/2/#N/A/4, XLOOKUP missing/SKU-103/SKU-104/missing/SKU-110. FILTER of A not in B spilled SKU-101, SKU-107. FILTER of B not in A spilled SKU-108. FILTER of both spilled SKU-103, SKU-104, SKU-110. ARRAYFORMULA flags: missing, in both, in both, missing, in both. Trailing-space SKU-103 was a miss until TRIM, which made MATCH 1 and XLOOKUP SKU-103. Lowercase sku-103: COUNTIF 1, MATCH 1, XLOOKUP SKU-103, EXACT FALSE.

Sources

• Microsoft Support: FILTER function: https://support.microsoft.com/en-us/office/filter-function-f4f7cb66-82eb-4767-8f7c-4877ad80c759
• Microsoft Support: COUNTIF function: https://support.microsoft.com/en-us/office/countif-function-e0de10c6-f885-4e71-abb4-1f464816df34
• Microsoft Support: MATCH function: https://support.microsoft.com/en-us/office/match-function-e8dffd45-c762-47d6-bf89-533f4a37673a
• Microsoft Support: XLOOKUP function: https://support.microsoft.com/en-us/office/xlookup-function-b7fd680e-6d10-43e6-84f9-88eae8bf5929
• Google Docs Editors Help: FILTER: https://support.google.com/docs/answer/3093197
• Google Docs Editors Help: COUNTIF: https://support.google.com/docs/answer/3093480