Check Whether a Value Appears in a List

Excel & Google Sheets

How it works

COUNTIF counts how many cells in the list equal A2 — at least 1 means the vendor is on the list, 0 means it isn't, and >0 turns that count into TRUE or FALSE for IF. The $ anchors on $D$2:$D$5 are essential: they lock the list range so it doesn't slide down a row each time you copy the formula. Matching is case-insensitive ("borealis" matches "Borealis") but otherwise exact — "Acme" won't match "Acme Co". Unlike VLOOKUP, there's no #N/A to catch when the value is absent; a zero count is a perfectly clean answer.

$D$2:$D$5

The reference list, locked with $ so it stays put when you copy the formula down.

COUNTIF(…,A2)>0

Counts exact matches for this row's value; more than zero means it's on the list.

"Approved","Not approved"

The two labels IF hands back.

When to use it

Use it to validate vendors against an approved list, SKUs against the current catalog, customer IDs against an active-accounts list, or expense categories against the chart of accounts.

Common mistakes

• ✕Forgetting the $ anchors on the list.

  ✓COUNTIF(D2:D5,A2) shifts to D3:D6, D4:D7… as you copy down, so later rows check against a shrinking list. Lock it: $D$2:$D$5.

• ✕Comparing row by row with =IF(A2=D2,…).

  ✓That only tests whether row 2 of each column happens to match. COUNTIF searches the whole list for each value, which is what "is it on the list" means.

• ✕Near-matches counted as misses.

  ✓"Acme" vs "Acme Co" or a trailing space both count as different values. Standardize names first (TRIM, consistent naming), or you'll reject vendors that are genuinely approved.