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.

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.

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

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.

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

Hit Enter and drag down the Fill Handle tool.

Here are the results.

Method 3 – Using the COUNTIF Function

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

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.

Hit Enter and drag down the Fill Handle.

Results:

Method 4 – Using IF and COUNTIF Functions

We’ll use the same dataset as before.

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.

Hit Enter and drag down the Fill Handle tool.

Results

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

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.

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

Results

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

We’ll use the same dataset.

Select the output cell F5.
Insert the following formula:

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

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.