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.
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.
Step-01:
➤Go to Home Tab>>Editing Groups>>Find & Select Dropdown>>Find Option.
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.
Result:
After that, you will get the cell position of the product Banana in the list.
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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
Method-3: Using COUNTIF Function
You can use the COUNTIF function to check the items in the Product list.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
📓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.
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.
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.
➤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.
📓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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
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.
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.
➤Press ENTER.
➤Drag down the Fill Handle Tool.
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.
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.
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.
➤Press ENTER.
Result:
Afterward, you will get Apple because it is on the Product list.
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.
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
- Lookup Value in Column and Return Value of Another Column in Excel
- How to Find Top 5 Values and Names in Excel
- Find Text in Excel Range and Return Cell Reference
- How to Search Text in Multiple Excel Files
- [Solved!] CTRL+F Not Working in Excel
- How to Get Top 10 Values Based on Criteria in Excel
- How to Create Top 10 List with Duplicates in Excel
<< Go Back to Find Value in Range | Excel Range | Learn Excel