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.

**Table of Contents**Expand

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

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

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

**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!