If cell contains one of many values

This tutorial shows how to check if a cell contains at least one of many items and return a value using an Excel formula, with the SUMPRODUCT function

Excel

If cell contains one of many values using SUMPRODUCT function

EXCEL FORMULA 1. If cell contains one of many values

EXCEL

Hard coded formula

Cell reference formula

=SUMPRODUCT(--(B5={"Tennis","Cricket","Netball"}))>0

=SUMPRODUCT(--(B5=$F$5:$F$7))>0

GENERIC FORMULA

=SUMPRODUCT(--(cell={value1,value2,...}))>0

ARGUMENTS
cell: A cell that contains the value that you want to check if it's one of the values that you are testing for.
value1: The first value that that you want check for if it's equal to the selected cell
value2: The second value that that you want check for if it's equal to the selected cell

GENERIC FORMULA

=SUMPRODUCT(--(cell=rng_values))>0

ARGUMENTS
cell: A cell that contains the value that you want to check if it's one of the values that you are testing for.
rng_values: A range of values that you want check for if they are equal to the selected cell.

EXPLANATION

This formula uses the SUMPRODUCT function to check if a selected cell contain one of many values. The formula will return a value of TRUE if the one of the values is equal to the value in the selected cell, otherwise it will return a value FALSE.

Click on either the Hard Coded or Cell Reference button to view the formula that has the text value to be tested for directly entered into the formula or referenced to a range of cells.