WWorkTools
EXCEL · PRACTICAL GUIDE

Find Missing Values in Excel

Mark items present in one list but absent from another.

Practice EXCELLearning path

What this solves

Find materials in a PO list that are missing from an inventory extract. This pattern is useful when the business question is clear but a generic tutorial is too abstract.

The rule

COUNTIF is a simple existence check.

=IF(COUNTIF($D$2:$D$500,A2)=0,"Missing","")
Google Sheets: The core pattern is also usable in Google Sheets; exact function support and locale separators can differ.

Real-work checklist

  1. Define the business key and expected grain before writing the formula, query or markup.
  2. Test the pattern on a small known sample where you can verify the answer manually.
  3. Check missing values, duplicates and data types before trusting a large result.
  4. Scale to the real file or database only after the logic is proven.

Common mistake

Relative ranges can shift when formulas are copied; lock lookup ranges with $ when appropriate.

Try it next

PracticeChange the example and verify the result.Use a toolMove from a small example to real work.Related contentFind guides, errors and scenarios on this topic.