How to Check if a Value Exists in a List in Excel

Use COUNTIF(list,value)>0 to confirm a value is on a list — then approve, hold or total records based on it.

You will learn to

• Return TRUE when a value is on a list
• Turn the check into an action
• Total the amounts for values that are not on the list

How it works

COUNTIF(list, value) counts how many times a value appears in a list, so COUNTIF(list, value)>0 is TRUE when it appears at least once: =COUNTIF($F$2:$F$7,B2)>0. Unlike MATCH it never returns an error, which makes it easy to drop straight into IF.

COUNTIF also accepts a whole range of values to check. COUNTIF($F$2:$F$7,B2:B8) returns a count for every row, so =SUMPRODUCT((COUNTIF($F$2:$F$7,B2:B8)=0)*C2:C8) totals the invoices from vendors that are missing from the approved list — in one formula.

=COUNTIF($F$2:$F$7,B2)>0