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

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.

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

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


Similar Readings


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

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


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

Arin Islam

Arin Islam

Hello, I'm Arin. I graduated from Khulna University of Engineering and Technology (KUET) from the Department of Civil Engineering. I am passionate about learning new things and increasing my data analysis knowledge as well as critical thinking. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to it.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo