How to Check If Cell Contains One of Several Values in Excel

Get FREE Advanced Excel Exercises with Solutions!

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.


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.

Dataset for Checking If Cell Contains One of Several Values in Excel


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.

Combining IF, SUMPRODUCT, ISNUMBER & SEARCH Functions to Check If Cell Contains One of Several Values in Excel

  • Now, you will get Yes or No as result depending on one of those values found in the cell.

Values Found After Checking Cells Using IF, SUMPRODUCT, ISNUMBER & SEARCH Functions

🔎 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


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.

Checking If Cell Contains One of Several Values Using TEXTJOIN, IF & COUNTIF Functions in Excel

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

Results Found After Checking Cells Using TEXTJOIN, IF & COUNTIF Functions in Excel

🔎 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


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.

Utilizing Combined Functions to Check If Cell Contains One of Several Values in Excel

  • Thus, you can check if a cell contains one of several values that you are looking for.

Values Found After Checking Cells Utilizing Combined Functions in Excel

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

Dataset to Apply INDEX and MATCH Functions to Check If Cell Contains One of Several Values in Excel

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.

Applying INDEX and MATCH Functions to Check If Cell Contains One of Several Values in Excel

  • Thus, you can check if a cell contains one of several values that you are looking for.

Results Found After Checking Cells Using INDEX, MATCH, ISNUMBER and SEARCH Functions in Excel

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

Practice Section to Check Cell Containing One of Several Value


Download Practice Workbook

You can download the workbook to practice yourself.


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. Thank you!


Related Articles

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.
Arin Islam
Arin Islam

Anowara Islam Arin, a graduate of Civil Engineering from Khulna University of Engineering & Technology, Bangladesh, serves as a technical content creator in the ExcelDemy project. Possessing a passion for innovation and critical thinking, she actively embraces challenges. Beyond her engineering background, Arin exhibits a keen interest in Excel, having authored numerous articles on Excel & VBA-related issues to simplify the experience for users facing obstacles in Excel. Besides, she is also interested in Advanced Excel,... Read Full Bio

We will be happy to hear your thoughts

Leave a reply

Advanced Excel Exercises with Solutions PDF

 

 

ExcelDemy
Logo