Find Values Not in an Allowed List

Excel Formulas › Auditing & Error-Proofing

All versionsCOUNTIF

Catch typos and rogue entries by flagging anything that isn’t in your approved list — a misspelled department, an invalid code. COUNTIF against the master list returns zero for unknowns.

Quick formula: flag entries not in the allowed list:
=IF(COUNTIF(allowed_list, A2) = 0, "Invalid", "OK")
Count the entry in the master list; zero means it's not an approved value.

Functions used (tap for the full reference guide):

COUNTIF

The example

"Saless" isn’t a valid department.

AB
1EntryCheck
2SalesOK
3SalessInvalid

The formula

The formula:

=IF(COUNTIF(allowed_list, A2) = 0, "Invalid", "OK") // not in master list

How it works

How it works:

  1. COUNTIF(allowed_list, A2) counts the entry in the master list — 0 means unrecognized.
  2. Wrap in IF to flag Invalid for cleanup or rejection.
  3. It’s the formula counterpart to a data-validation dropdown — useful for auditing data already entered.
  4. Normalize with TRIM/UPPER first so case and spacing don’t cause false flags.

Validate after the fact. Data validation stops bad entries going forward, but it doesn’t fix the thousand rows already in the file. A COUNTIF-against-master flag audits the existing data, surfacing every value that slipped in before the rules existed — the cleanup companion to a validation list.

Try it: interactive demo

Live demo

Entry and allowed list.

Check:

Variations

Count invalids

How many bad:

=SUMPRODUCT(--(COUNTIF(allowed, entries)=0))

Case-insensitive key

Normalize first:

=IF(COUNTIF(allowed, TRIM(UPPER(A2)))=0,"Invalid","OK")

Closest match (365)

Suggest a fix:

=XLOOKUP(A2, allowed, allowed, "?", 1)

Pitfalls & errors

Both must be normalized. Apply the same TRIM/UPPER to the list and the entry.

Wildcards in COUNTIF. Values with * or ? need escaping — rare but real.

Blanks. Empty cells count as not-in-list — decide if that’s “Invalid” or skipped.

Practice workbook

📊
Download the free Find Values Not in an Allowed List practice workbook
A not-in-list sheet with the count, normalized, and closest-match variants, plus 4 challenges with answers. No sign-up required.

Frequently asked questions

How do I find values not in an allowed list in Excel?
Use =IF(COUNTIF(allowed_list, A2)=0, "Invalid", "OK") — zero count means the value isn't approved.
How is this different from data validation?
Validation blocks new bad entries; this audits data already in the file, flagging values entered before the rules.
How do I avoid false flags from case or spaces?
Normalize both sides with TRIM/UPPER before the COUNTIF.

Stop fighting formulas. Learn them in a day.

This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.

See the Formulas & Functions Class

Related formulas: Reconcile two lists · Check if contains · Data validation dropdown

Function references: COUNTIF