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.

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".

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