AI Excel Generator
← ALL GUIDES

How to Find Missing Values Between Two Lists in Excel

Updated November 4, 2026 · AI Excel Generator

To find missing values between two lists in Excel, compare each value in one list against the other and flag entries with no match.

The right method depends on whether you want a reusable formula, a visual highlight, or a separate list of missing items.

Set up the example lists

Suppose the first list is in cells A2:A6 and the second list is in B2:B5:

  • List A: Apple, Banana, Cherry, Date, Fig
  • List B: Apple, Cherry, Fig, Grape

Banana and Date are in List A but not List B.

Grape is in List B but not List A.

To compare both directions, check each list separately; a one-way comparison only identifies values missing from the list you compare against.

Use COUNTIF to mark values that are missing

In C2, enter this formula to check whether each value in List A appears in List B:

=IF(COUNTIF($B$2:$B$5,A2)=0,"Missing from List B","Found")

Fill the formula down through C6.

The expected results are:

  • Apple: Found
  • Banana: Missing from List B
  • Cherry: Found
  • Date: Missing from List B
  • Fig: Found

COUNTIF counts how many times the value in A2 occurs in B2:B5.

A count of zero means there is no match.

The dollar signs keep the lookup range fixed as you fill the formula down, while A2 changes to A3, A4, and so on.

To check which values in List B are absent from List A, use this formula in D2 and fill it down through D5:

=IF(COUNTIF($A$2:$A$6,B2)=0,"Missing from List A","Found")

This method works in modern Excel and older versions that support COUNTIF, including Excel 2016 and Excel 2019.

It also works in Google Sheets, though this article focuses on Excel.

Use XMATCH or MATCH for a presence check

If you prefer a match function, XMATCH can check whether each item in A2:A6 is present in List B:

=IF(ISNA(XMATCH(A2,$B$2:$B$5)),"Missing from List B","Found")

XMATCH is available in Microsoft 365 and Excel 2021 or later.

For older Excel versions, use MATCH with ISNA:

=IF(ISNA(MATCH(A2,$B$2:$B$5,0)),"Missing from List B","Found")

The final argument, 0, requests an exact match.

Without it, MATCH may use approximate matching under certain conditions, which is unsuitable for a straightforward list comparison.

Return a separate list of missing values

In Microsoft 365 or Excel 2021 and later, use FILTER with COUNTIF to return only the values from List A that are absent from List B:

=FILTER(A2:A6,COUNTIF(B2:B5,A2:A6)=0,"No missing values")

The formula spills its results into cells below it.

For the example, the result is Banana and Date.

If every value from List A appears in List B, the formula displays “No missing values.” Keep the cells below the formula clear so the spilled results have room to appear.

To return the opposite set, switch the ranges:

=FILTER(B2:B5,COUNTIF(A2:A6,B2:B5)=0,"No missing values")

This returns Grape.

Dynamic array functions such as FILTER are not available in Excel 2019 or earlier.

In those versions, use a helper column with COUNTIF, then filter for “Missing from List B.”

Highlight missing values with conditional formatting

Conditional formatting can make missing entries easy to spot without adding a results column:

  1. Select A2:A6.
  2. On the Home tab, choose Conditional Formatting, then New Rule.
  3. Choose the rule type that uses a formula to determine which cells to format.
  4. Enter =COUNTIF($B$2:$B$5,A2)=0.
  5. Choose a fill color or another format, then apply the rule.

The relative reference A2 adjusts for each selected cell, while the dollar signs lock the comparison range.

To highlight values in List B that are absent from List A, apply a second rule to B2:B5 using =COUNTIF($A$2:$A$6,B2)=0.

Compare lists without treating duplicates as differences

The formulas above test whether a value appears at least once.

They do not compare how many times each value occurs.

For example, if List A contains “Apple” twice and List B contains it once, both Apple entries in List A count as found.

If the number of occurrences matters, compare counts instead.

For the value in A2, use:

=COUNTIF($A$2:$A$6,A2)-COUNTIF($B$2:$B$5,A2)

A positive result means that value occurs more often in List A; a negative result means it occurs more often in List B.

Because this formula repeats the count for duplicate rows, use a deduplicated list of values if you want one summary row per item.

Check common causes of unexpected missing matches

A formula may flag values as missing even when they look identical.

Check for these issues before changing the comparison method:

  • Extra spaces: “Apple” and “Apple ” are different text values.

    Use TRIM in helper columns to remove leading, trailing, and repeated spaces.
  • Numbers stored as text: The number 123 and the text value “123” may not match as expected.

    Convert both lists to the same data type.
  • Hidden characters: Imported data can contain nonprinting characters.

    CLEAN can remove many control characters; a nonbreaking space may need separate replacement.
  • Different date values: Two dates that display alike may have different underlying values, such as a date with a time component.

    Compare consistent date-only values if time is irrelevant.

Excel's common lookup and counting functions are not case-sensitive.

If “abc” and “ABC” should count as different entries, use a case-sensitive formula based on EXACT instead of relying on COUNTIF.

Choose a method for recurring comparisons

For a quick check, use a helper column with COUNTIF.

Choose FILTER when you want a live, separate list of unmatched values and have a version of Excel with dynamic arrays.

Conditional formatting is useful when a visual review is enough.

For large lists that need repeated cleanup and comparison, Power Query can merge two tables and keep rows with no match, making the steps refreshable when the source data changes.