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

Get FREE Advanced Excel Exercises with Solutions!

If you are looking for some of the easiest ways to check if a value is in a list in Excel, then you are in the right place. To check out your desired value in a large range of data easily you can follow the methods of this article.


How to Check If a Value is in List in Excel: 10 Ways

Here, in the following table, I have some information about some products of a company. I will use this table to demonstrate the ways of checking a value in a list easily. Like in this case the list will be the Product column of this table.

For this purpose, I have used Microsoft Excel 365 version, you can use any other versions according to your convenience.

check if a value is in list in Excel


Method-1: Using Find & Select Option to Check If a Value is in List

You can use the Find & Select Option to check a value in the Product list below. Here, we are searching for the product Banana.

check if a value is in list in Excel

Step-01:
➤Go to Home Tab>>Editing Groups>>Find & Select Dropdown>>Find Option.

Find & Select

Now, the Find and Replace Dialog Box will appear.
➤Write down the name of the product which you are looking in the Find what Box (For this case it is Banana)
➤Select the following
Within→Sheet
Search→By Rows
Look in→Values
➤Press Find All option.

Find & Select

Result:
After that, you will get the cell position of the product Banana in the list.

check if a value is in list in Excel


Method-2: Using ISNUMBER and MATCH Function to Check If a Value is in List

Here, we have some items in the Item column which we want to check in the list of the products in the product column. After checking the result will appear in the Result column. In this method, we are doing this job by using the ISNUMBER function and the MATCH function.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type the following formula

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

Here, 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.

Then ISNUMBER will return TRUE if there is a number otherwise FALSE.

ISNUMBER+MATCH function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

ISNUMBER+MATCH function

Result:
In this way, you will get TRUE for Apple because it is in the Product list and FALSE for Tomato which is not in the list. 

ISNUMBER+MATCH function


Method-3: Using COUNTIF Function

You can use the COUNTIF function to check the items in the Product list.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type the following formula

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

$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 depending on the occurrence of this value and so it will be greater than 0 and so the output will be TRUE otherwise it will be FALSE if the value is not in the list.

COUNTIF function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

COUNTIF function

Result:
Afterward, you will get TRUE for Apple because it is in the Product list and FALSE for Tomato which is not in the list. 

check if a value is in list in Excel


Method-4: Using IF and COUNTIF Function

Here, we are using the IF function and the COUNTIF function for checking the items of the Item column in the Product column.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type 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 depending on the occurrence of this value and so it will be greater than 0 and then IF will return Matched otherwise it will return Not Matched if the value is not in the list.

IF+COUNTIF function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

IF+COUNTIF function

Result:
Afterward, you will get Matched for Apple because it is in the Product list and Not Matched for Tomato which is not in the list. 

check if a value is in list in Excel


Method-5: Checking Partial Match with Wildcard Operators

In the following table, we have Apple and Berry in the Item column but they are not fully matched (we have modified Apple in the dataset to Green Apple for explaining this case) rather than partially matched in the Product list. So, to check the values which are partially matched in the list here we are using the IF function and the COUNTIF function.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type 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 and “*” is joined with this value by using the Ampersand operator. “*” is used for partially matching the values.

When COUNTIF finds the value in the list it will return a number depending on the occurrence of this value and so it will be greater than 0 and then IF will return Matched otherwise it will be Not Matched if the value is not in the list.

checking partial match

➤Press ENTER.
➤Drag down the Fill Handle Tool.

checking partial match

Result:
After that, you will get Matched for Apple because it is in the Product list as Green Apple and Matched for Berry which is in the list as Strawberry and Blackberry. 

check if a value is in list in Excel


Method-6: Using OR Function to Check If a Value is in List

You can use the OR function to check the values of the Item column in the Product column.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Write the following formula

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

$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 the value matches in the list OR will return TRUE otherwise FALSE.

OR function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

OR function

📓Note
If you are using any version other than Microsoft Excel 365, then you have to press CTRL+SHIFT+ENTER instead of pressing ENTER.

Result:
Then, you will get TRUE for Apple because it is in the Product list and FALSE for Tomato which is not in the list. 

check if a value is in list in Excel


Method-7: Using IF Function to Check If a Value is in List

You can use the IF function to check the values of the Item column in the Product column.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Write the following formula

=IF(($B$5:$B$10=$E$5:$E$10),"Matched","Not Matched")

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

When the value matches in the list IF will return Matched otherwise Not Matched.

IF function

➤Press ENTER.

Result:
After that, you will get Matched for Apple, Banana, Blackberry because they are in the Product list and Not Matched for Orange, Jackfruit, and Strawberry which are not in the list. 

IF function

📓Note
If you are using any version other than Microsoft Excel 365, then you have to press CTRL+SHIFT+ENTER instead of pressing ENTER.


Method-8: Using ISERROR and VLOOKUP Function

Here, we are using the IF function, the ISERROR function, and the VLOOKUP function for checking the items of the Item column in the Product column.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type the following formula

=IF(ISERROR(VLOOKUP(E5,$B$5:$B$10,1,FALSE)),"Not Matched","Matched")

VLOOKUP will look for the value of the cell E5 in the $B$5:$B$10 range, where 1 is the column index number and FALSE is for an exact match.

If the value cannot be determined or does not match then The ISERROR function will return TRUE otherwise FALSE.
The IF function will convert TRUE to Not Matched and FALSE  to Matched.

ISERROR+VLOOKUP function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

ISERROR+VLOOKUP function

Result:
Afterward, you will get Matched for Apple because it is in the Product list and Not Matched for Tomato which is not in the list. 

check if a value is in list in Excel


Method-9: Using ISERROR INDEX and MATCH Function

You can use the IF function, the ISERROR function, the INDEX function, and the MATCH function for checking the items of the Item column in the Product column.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type the following formula

=IF(ISERROR(INDEX($B$5:$B$10,MATCH(E5,$B$5:$B$10,0))),"Not Matched","Matched")

MATCH will look for the value of the cell E5 in the $B$5:$B$10 range, where 0 is for an exact match.

If the value cannot be determined or does not match then The ISERROR function will return TRUE otherwise FALSE.
The IF function will convert TRUE to Not Matched and FALSE  to Matched.

INDEX+MATCH function

➤Press ENTER.
➤Drag down the Fill Handle Tool.

INDEX+MATCH function

Result:
Then, you will get Matched for Apple because it is in the Product list and Not Matched for Tomato which is not in the list. 

INDEX+MATCH function


Method-10: Checking Multiple Values in a List

Here, we have an Item List that has different items separated by a comma and we want to match the items of this list in the Product column. For this purpose we will use the IFERROR function, the INDEX function, the SMALL function, the IF function, the COUNTIF function, the MATCH function.

check if a value is in list in Excel

Step-01:
➤Select the output cell F5.
➤Type the following formula

=IFERROR(INDEX($B$5:$B$10, SMALL(IF(COUNTIF(B13, "*"&$B$5:$B$10&"*"), MATCH(ROW($B$5:$B$10), ROW(B5:B10)), ""), COLUMNS($B$13:B13))), "")

$B$5:$B$10 is the range where you are checking your desired value and B13 is the value which you are looking for.
Here, IF will return the Product’s name which will be matched in the list otherwise it will return Blank.

checking multiple values

➤Press ENTER.

Result:
Afterward, you will get Apple because it is on the Product list.

checking multiple values


Practice Section

For doing practice by yourself we have provided a Practice section like below for each method in each sheet on the right side. Please do it by yourself.

practice


Download Workbook


Conclusion

In this article, I tried to cover the easiest ways to check if a value is in a list in Excel effectively. Hope you will find it useful. If you have any suggestions or questions feel free to share them with us.


Further Readings


<< Go Back to Find Value in Range | Excel Range | Learn Excel

What is ExcelDemy?

ExcelDemy - Learn Excel & Get Excel Solutions Center provides online Excel training , Excel consultancy services , free Excel tutorials, free support , and free Excel Templates for Excel professionals and businesses. Feel free to contact us with your Excel problems.
Tanjima Hossain
Tanjima Hossain

TANJIMA HOSSAIN is a marine engineer who enjoys working with Excel and VBA programming. For her, programming is a handy, time-saving tool for managing data, files, and online tasks. She's skilled in Rhino3D, Maxsurf C++, MS Office, AutoCAD, and Excel & VBA, going beyond the basics. She holds a B.Sc. in Naval Architecture & Marine Engineering from BUET and is now a content developer. In this role, she creates tech-focused content centred around Excel and VBA. Apart from... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo