Arin Islam
Looking for ways to check if a cell contains one of several values in Excel? Then, this is the right place for you. Using different functions, you can easily do it in Excel. Here, you will find 4 simple ways to check if a cell contains one of several values in Excel.
Download Practice Workbook
You can download the workbook to practice yourself.
4 Simple Ways to Check If Cell Contains One of Several Values in Excel
Here, we have a dataset containing a Works to Do list and some values which we want to find from this list. Now, using this dataset we will show you how you can check if any cell contains one of these values using different functions.
1. Combine IF, SUMPRODUCT, ISNUMBER & SEARCH Functions to Check If Cell Contains One of Several Values
In the first method, we will combine the IF, SUMPRODUCT, ISNUMBER and SEARCH functions to check if the cells in the given list contains one of the values that we are searching for.
Steps:
- Firstly, select Cell C5 and insert the following formula.
=IF(SUMPRODUCT(--ISNUMBER(SEARCH($E$5:$E$7,B5))),"Yes","No")- Then, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Now, you will get Yes or No as result depending on one of those values found in the cell.
🔎 How Does the Formula Work?
- To start with, we used the SEARCH function to find cell range E5:E7 in Cell B5.
- After that, we used the ISNUMBER function to check if the result of the SEARCH function is a number.
- Next, we used the SUMPRODUCT function to add those numbers.
- Lastly, the IF function returns “Yes” if the resultant of the SUMPRODUCT function is greater than 0, otherwise, it returns “No”.
Read More: How to Use IF Statement with Yes or No in Excel (3 Examples)
2. Check If Cell Contains One of Multiple Values Using TEXTJOIN, IF & COUNTIF Functions Together
You can also use the TEXTJOIN, IF and COUNTIF functions together to check if a cell contains one of the values from the Find list.
Here are the steps.
Steps:
- In the beginning, insert the following formula in Cell C5 and press Enter.
=TEXTJOIN(",",TRUE,IF(COUNTIF(B5,"*"&$E$5:$E$7&"*"),$E$5:$E$7,""))- After that, drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Finally, you will be able to check if a cell contains one of the several values that we are searching for, and the corresponding find value will be shown in the Result column.
🔎 How Does the Formula Work?
- Firstly, we used the COUNTIF function to count the number of values present in Cell B5 from cell range E5:E7.
- Then, we used the IF function to return the value we found from the cell range or else return Blank if not found.
- Lastly, the TEXTJOIN function is used to join all the values we found. Here, in the function, we used comma (,) as the delimiter.
Read More: How to Prepare IF Statement Contains Multiple Words in Excel
Similar Readings
- How to Use IF Function with OR and AND Statement in Excel
- Use Multiple IF Statements in Excel Data Validation
- How to Use If Statement Based on Cell Color in Excel (3 Examples)
- Dynamic Data Validation List in Excel with IF Statement Condition
- How to Use IF Statement with Not Equal To Operator in Excel
3. Utilize Combined Functions to Find If Cell Contains One of Several Values
Next, we will show you how you can utilize the IFERROR, INDEX, SMALL, IF, MATCH, ROW and COLUMNS functions to find if a cell contains one of several values in Excel. This will be able to show you the specific value it found in the cell like the previous method.
Steps:
- To start with, insert the following formula in Cell C5 and press Enter.
=IFERROR(INDEX($E$5:$E$7,SMALL(IF(COUNTIF($B5,"*"&$E$5:$E$7&"*"),MATCH(ROW($E$5:$E$7),ROW($E$5:$E$7)),""),COLUMNS($E$5:$E5))),"")- After that, drag down the Fill Handle tool to AutoFill the formula.
- Thus, you can check if a cell contains one of several values that you are looking for.
🔎 How Does the Formula Work?
- In the beginning, we used the ROW function to find the row number of cell range E5:E7.
- Next, we applied the COLUMNS function to calculate the number of columns in the given range.
- Then, we used the COUNTIF function to count the number of values present in Cell B5 from cell range E5:E7.
- After that, MATCH function is used to return the relative position of the cell range E5:E7.
- Now, we used the result of COUNTIF function as logical_test, result of MATCH function as value_if_true and Blank as value_if_false in the IF function.
- Afterward, in the SMALL function, we inserted the result of the IF function as array and the result of the COLUMNS function as k.
- Next, in the INDEX function, we inserted the cell range E5:E7 as array and the result of the SMALL function as row_num.
- Lastly, we used the IFERROR function to avoid any error that may occur if the cell does not contain any of the values we are looking for and will result in Blank in the case.
Read More: How to Use If Then Else Statement in Excel VBA (4 Examples)
4. Apply INDEX and MATCH Functions to Check If Cell Stores One of Several Values in Excel
In the final method, you will find a way to check for one of several values in a cell and then return a value corresponding to that find value in Excel. To do that we will use the INDEX, MATCH, ISNUMBER and SEARCH functions. While using this method, you will find a #N/A error for the cells which do not contain any of the values you are looking for.
Follow the steps given below to use this method in your dataset.
Steps:
- Firstly, select Cell C5 and insert the following formula.
=INDEX($F$5:$F$7,MATCH(TRUE,ISNUMBER(SEARCH($E$5:$E$7,B5)),0))- Then, press Enter and drag down the Fill Handle tool to AutoFill the formula for the rest of the cells.
- Thus, you can check if a cell contains one of several values that you are looking for.
🔎 How Does the Formula Work?
- To start with, we used the SEARCH function to find cell range E5:E7 in Cell B5.
- After that, we used the ISNUMBER function to check if the result of the SEARCH function is a number.
- Next, in the MATCH function, we used TRUE as lookup_value, the result of ISNUMBER function as lookup_array and 0 as match_type.
- Finally, in the INDEX function, we inserted the cell range F5:F7 as array and the result of the MATCH function as row_num.
Read More: Excel IF Statement with VLOOKUP for Multiple Conditions Range
Practice Section
In the article, you will find an Excel workbook like the image given below to practice on your own.
Conclusion
So, in this article, we have shown you 4 ways to check if a cell contains one of several values in Excel. I hope you found this article interesting and helpful. If something seems difficult to understand, please leave a comment. Please let us know if there are any more alternatives that we may have missed. And visit ExcelDemy for many more articles like this. Thank you!
Related Articles
- How to Use Conditional Formatting If Statement Is Another Cell
- Excel IF Statement Between Two Numbers (4 Ideal Examples)
- How to Find Sum If Cell Color Is Green in Excel (4 Easy Methods)
- Use Wildcard with If Statement in Excel (5 Methods)
- Show Cell Only If Value Is Greater Than 0 in Excel (2 Examples)
