How to Find Missing Data Between Two Excel Columns

Comparing two lists to find missing or new items is a common task. Did the new inventory list arrive? Which customers from last month's list didn't make a purchase this month? Excel doesn't have a single "Find Missing" button, but it has two very effective methods. This guide shows you how to use Conditional Formatting and the MATCH function to get your answer.

Method 1: Conditional Formatting (Visual Comparison)

This is the fastest way to visually spot which items from List A are missing from List B.

Step-by-step instructions:

  1. Select the first list (List A) that you want to check for missing items.
  2. Go to Home → Conditional Formatting → New Rule.
  3. Select "Use a formula to determine which cells to format".
  4. Enter the formula to find values in List A that are NOT in List B. Assuming List B is in column D, the formula would be: =COUNTIF($D:$D, $A1)=0.
  5. Click Format, choose a bright fill color (e.g., yellow), and click OK.

Any cell in List A that is formatted with the fill color is missing from List B. This method is excellent for one-time, visual analysis.

Method 2: Using the MATCH Function (Create a List of Missing Items)

With the MATCH function, you can generate a actual list of the missing items in a new column. This is much more powerful than a simple visual highlight.

Step-by-step instructions:

  1. Insert a new column next to your first list (List A).
  2. In the first cell of the new column (e.g., B1), enter the following formula: =ISNA(MATCH(A1, D:D, 0)). This checks if the value in A1 exists in column D.
  3. Drag this formula down for all items in List A.
  4. The formula will return TRUE for items that are NOT found in List B (missing), and FALSE for items that are found.
  5. Use Excel's filter feature to show only the rows where the helper column is TRUE. You can then copy these visible cells to a new location.

This creates a dynamic list. If you change data in List B, the list of missing items will update automatically.

Pro Tip: Using VLOOKUP to Find Missing Data

VLOOKUP is another classic Excel function for this task. It looks for a value in another list and returns a result or an error. Here's how to find missing items using VLOOKUP:

Formula: =IFERROR(VLOOKUP(A1, D:D, 1, 0), "Missing")

This formula will either return the value (if found) or the text "Missing" (if not found).

Use Online List Compare Tool

If you want to skip Excel formulas entirely, You can also use our free online list comparison tool. Just paste your two lists, click "Compare" and you can immediately see the results:

← Back to all blog posts