Excel Compare Two Columns for Missing Values

To compare two columns in Excel and find missing values, use the VLOOKUP function with an IFERROR wrapper to flag items that appear in one column but not the other. For example, =IFERROR(VLOOKUP(A2,$B$2:$B$10,1,FALSE),"Missing") returns “Missing” when a value in column A is not found in column B. This article explains several methods, how to choose between them, and how to handle common pitfalls like duplicates, blank cells, and case sensitivity.

Why Compare Columns?

Comparing two lists is a common task in data cleanup, reconciliation, and audit work. You might need to verify that every employee ID on a timesheet exists in a master roster, that all product SKUs in a sales file are in the inventory list, or that the email addresses in a newsletter signup sheet match a customer database. Finding missing values helps you spot data entry errors, incomplete imports, or records that were deleted. The goal is usually to produce a list of items that are in one column but absent from the other, or to highlight them in place.

Method 1: VLOOKUP with IFERROR

The classic approach uses VLOOKUP to search for each value in the first column against the second column. If the value is not found, VLOOKUP returns a #N/A error, which IFERROR converts into a readable label like “Missing”.

In a new column next to your first list, enter: =IFERROR(VLOOKUP(A2,$B$2:$B$100,1,FALSE),"Missing")
Drag the formula down to cover all rows in column A.
Filter or sort by the new column to see all rows labeled “Missing”.

This works for text, numbers, and dates, but VLOOKUP always searches the first column of the lookup range. If your second list is not in column A of its own range, adjust the range accordingly. The FALSE argument ensures an exact match, which is what you want for finding missing values; TRUE would find approximate matches and give misleading results.

Method 2: MATCH with ISNA

MATCH returns the position of a value in a range, or #N/A if not found. Combined with ISNA, it gives a TRUE/FALSE result that is easy to filter.

=ISNA(MATCH(A2,$B$2:$B$100,0))

This returns TRUE when the value in A2 is NOT in column B. You can then filter for TRUE to see the missing items. This method is slightly cleaner than VLOOKUP because it doesn’t require a third argument for the return column, and it works the same for text and numbers. It also avoids the “#N/A” display entirely.

Method 3: Conditional Formatting

If you prefer to highlight missing values directly in the column without adding a formula, use conditional formatting with a formula rule.

Select the range in column A that you want to check (e.g., A2:A100).
Go to Home > Conditional Formatting > New Rule > Use a formula to determine which cells to format.
Enter: =COUNTIF($B$2:$B$100,A2)=0
Set a fill color and click OK.

Now every cell in column A that does not appear in column B will be highlighted. This is great for visual inspection, but it does not give you a separate list. You can combine it with a filter or copy the highlighted cells manually if you need a standalone list.

Method 4: Power Query (Get & Transform)

For large datasets or one-time comparisons, Power Query offers a robust way to find missing values without formulas. In Excel 2016 and later, it’s built in as Get & Transform.

Select your first column, go to Data > From Table/Range (create a table if prompted).
In the Power Query editor, rename the query to “List1”.
Load the second column similarly as “List2”.
In List1, go to Home > Merge Queries, and join with List2 using a Left Anti join.
Click OK, then Close & Load. The resulting table contains only the values from List1 that are not in List2.

Power Query is especially useful when your columns are in different files or when you need to repeat the comparison regularly—you can refresh the query. It also handles large data faster than formulas.

How to Choose the Right Method

The best method depends on your data size, how often you need to repeat the task, and whether you want a visual highlight or a separate list.

Small to medium data (under 10,000 rows): VLOOKUP or MATCH formulas are simple and easy to understand.
Visual inspection only: Conditional formatting is the fastest way to see missing values in context.
Large data or repeated use: Power Query is more efficient and doesn’t clutter your sheet with formulas.
One-time comparison: Any method works; formulas are often quicker to set up.

If you need to compare both directions (find values in A missing from B, and values in B missing from A), you’ll need to repeat the process with the columns swapped.

Common Problems and Pitfalls

Several issues can cause false “missing” results or missed matches.

Leading/trailing spaces: “John” and “John ” are different. Use TRIM to clean data: =TRIM(A2) and compare the trimmed values.
Case sensitivity: VLOOKUP and MATCH are case-insensitive by default. If you need to distinguish “apple” from “Apple”, use EXACT in an array formula or a helper column.
Numbers stored as text: A number formatted as text won’t match a real number. Convert text to numbers using VALUE() or the Text-to-Columns wizard.
Duplicates: If column A has duplicates, each duplicate will be checked individually. That’s usually fine, but if you want a unique list of missing values, use Remove Duplicates on a copy of the data first.
Blank cells: Blank cells will be considered missing if the other column has no blanks. Filter out blanks before comparing if they are not meaningful.
Hidden characters: Non-printing characters from imported data can break matches. Use CLEAN() to remove them.

Frequently Asked Questions

How do I compare two columns for missing values in Excel 2010?

All the formula methods (VLOOKUP, MATCH, COUNTIF) work in Excel 2010. Power Query is not available in 2010, but you can use the same formulas or a pivot table to find missing values. For a pivot table approach, put both columns in one list with a source identifier, then use a pivot to see which items only appear in one source.

Can I compare two columns in different sheets?

Yes, simply reference the other sheet in your formula. For example, =VLOOKUP(A2,Sheet2!$A$2:$A$100,1,FALSE) searches for A2 in column A of Sheet2. The same works with MATCH and COUNTIF.

How do I find missing values in two columns and list them?

Use the VLOOKUP or MATCH method to flag missing values, then filter the column to show only the “Missing” rows. Copy those cells to a new location. Alternatively, use Power Query with a Left Anti join to directly extract a list of missing values.

Why does VLOOKUP return #N/A for values that look identical?

This is usually caused by invisible differences like trailing spaces, non-breaking spaces (character 160), or numbers stored as text. Use TRIM and CLEAN to normalize both columns, and ensure numbers are truly numeric.

How do I compare two columns for missing values and highlight them in both columns?

Apply conditional formatting to each column separately. For column A, use =COUNTIF($B$2:$B$100,A2)=0; for column B, use =COUNTIF($A$2:$A$100,B2)=0. This highlights values in each column that are missing from the other.

Is there a way to compare two columns for missing values without using formulas?

Yes, you can use Power Query (Get & Transform) as described above, or use the built-in Remove Duplicates feature on a combined list. To use Remove Duplicates, copy both columns into one, remove duplicates, then compare the unique list against each original column using a formula or conditional formatting.

General steps for current mainstream versions; menus can differ by device and version. See the disclaimer or report an error.

More in this bed

The whole Tech How-To bed →