How to Check If a Value Is in List in Excel (10 Ways)

Consider a dataset about some products of a company. We will check whether specific values in the Product column exist.

check if a value is in list in Excel

Method 1 – Using Find & Select to Check If a Value Is in a List

We are searching for the product Banana.

check if a value is in list in Excel

Go to the Home tab, select Find & Select, and pick Find.

Find & Select

The Find and Replace dialog box will appear.
Write down the name of the product you are looking for in the Find what box (Banana)
Select the following: Within → Sheet; Search → By Rows; Look in → Values.
Press Find All.

Find & Select

You will get the cell positions of the product Banana in the list.

check if a value is in list in Excel

Method 2 – Using ISNUMBER and MATCH Functions to Check If a Value Is in a List

We have some items in the Item column which we want to check in the list of the products in the Product column. The check result will appear in the Result column.

check if a value is in list in Excel

Select the output cell F5.
Insert the following formula:

=ISNUMBER(MATCH(E5,$B$5:$B$10,0))

The MATCH function will return the position of the value in the E5 cell in the range $B$5:$B$10 if it is found. Otherwise, it will return #N/A. ISNUMBER will return TRUE if the value is a number (i.e. if MATCH finds something).

ISNUMBER+MATCH function

Hit Enter and drag down the Fill Handle tool.

ISNUMBER+MATCH function

Here are the results.

ISNUMBER+MATCH function

Method 3 – Using the COUNTIF Function

We’ll use the same conditions as in Method 2.

check if a value is in list in Excel

Select the output cell F5.
Insert the following formula

=COUNTIF($B$5:$B$10,E5)>0

COUNTIF will return how many times the check value appears in the array, so it will be greater than 0 if it finds a result. The output will then be TRUE.

COUNTIF function

Hit Enter and drag down the Fill Handle.

COUNTIF function

Results:

check if a value is in list in Excel

Method 4 – Using IF and COUNTIF Functions

We’ll use the same dataset as before.

check if a value is in list in Excel

Select the output cell F5.
Insert the following formula:

=IF(COUNTIF($B$5:$B$10,E5)>0,"Matched","Not Matched")

$B$5:$B$10 is the range where you are checking your desired value and E5 is the value which you are looking for.

When COUNTIF finds the value in the list, it will return a number of occurrences of this value, so it will be greater than 0. IF will then return Matched. Otherwise, it will return Not Matched if the value is not in the list.

IF+COUNTIF function

Hit Enter and drag down the Fill Handle tool.

IF+COUNTIF function

Results

check if a value is in list in Excel

Method 5 – Checking a Partial Match with Wildcards

In the following table, we have Apple and Berry in the Item column but they are not exact matches to the product names (we have modified Apple in the dataset to Green Apple).

check if a value is in list in Excel

Select the output cell F5.
Insert the following formula:

=IF(COUNTIF($B$5:$B$10,"*"&E5&"*")>0,"Matched","Not Matched")

E5 is the check value, where “*” is joined with this value by using the Ampersand operator. “*” is used to replace zero or more characters.

checking partial match

Hit Enter and drag down the formula with the Fill Handle.

checking partial match

Results

check if a value is in list in Excel

Method 6 – Using the OR Array Function to Check If a Value Is in a List

We’ll use the same dataset.

check if a value is in list in Excel

Select the output cell F5.
Insert the following formula:

=OR($B$5:$B$10=E5)

OR function

Hit Enter and drag down the Fill Handle to fill the other cells. If you are using any version other than Excel 365, press Ctrl + Shift + Enter instead of pressing Enter.

OR function