Lookup & Reference

Excel formula for Check if a value exists in a list

Test whether a value appears anywhere in another column or list.

Copy this formula

=IF(COUNTIF(B:B, A2)>0, "Yes", "No")

How the formula works

COUNTIF counts how many times the value appears; greater than zero means it exists. IF turns that into a readable Yes/No. It's the simplest way to compare two lists for membership.

Examples

=IF(COUNTIF(Approved, A2)>0, "✓", "")→✓

Marks rows whose ID is in the approved list.

=COUNTIF(B:B, A2)>0→TRUE

Returns TRUE or FALSE directly, without IF.

=IF(ISNA(XLOOKUP(A2, B:B, B:B)), "New", "Existing")→Existing

XLOOKUP returns #N/A when the value is missing, which ISNA detects.

Other ways to do this

Using MATCH

=IF(ISNUMBER(MATCH(A2, B:B, 0)), "Yes", "No")

MATCH + ISNUMBER also tests existence.

FAQ

How do I find values in list A missing from list B?

Flag with =IF(COUNTIF(ListB, A2)=0, "Missing", ""), then filter for "Missing".

Is the check case-sensitive?

No. COUNTIF and MATCH ignore case. For a case-sensitive test use =SUMPRODUCT(--EXACT(A2, B2:B100))>0.

Why does it say No when I can see the value in the list?

Usually extra spaces, or a number stored as text on one side. Clean the data with TRIM, or convert the numbers with VALUE.

How do I check against a list on another sheet?

Add the sheet name to the range: =IF(COUNTIF(Sheet2!A:A, A2)>0, "Yes", "No").

Related formulas

Filter rows by criteria with a formula Count cells that meet a condition

Functions used

COUNTIF MATCH ISNUMBER

Need a formula for your own data?

Describe what you want in plain language and let ExcelGPT write, explain, and verify the formula — free to start, no plugin.

Try ExcelGPT free