Looking for ways to check if a cell contains text and then return value with multiple conditions in Excel? Then, this is the right place for you. Using different functions, you can easily do it in Excel. Here, you will find 7 easy ways to check if a cell contains text, then return value with multiple conditions in Excel.

## Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel: 7 Ways

Now, we will show you how to use different functions to check for specific text in cells under multiple conditions to return values according to your wish in Excel.

### 1. Use XLOOKUP Function to Check If Cell Contains Text to Return Value in Excel

In the first method, we will use **the XLOOKUP Function** to check if a **cell contains text** to **return value** with **multiple conditions** in Excel.

Here, we have a dataset containing the **Names **of some students. Now, we will assign **Passwords **for these students according to their names.

**Steps:**

- Firstly, select
**Cell C5**and insert the following formula.

`=XLOOKUP(B5,{"Jim","Harry","Emma"},{"J111","H123","E129"},"N/A",0,1)`

- Then, press
**Enter**and drag down the**Fill Handle**tool to AutoFill the formula for the rest of the cells.

- Thus, you will get the
**Passwords**for the students according to their**Names**.

Here, in the **XLOOKUP **function, we inserted **Cell B5** as **lookup_value**, **{“Jim”,”Harry”,”Emma”}** as **lookup_array**, **{“J111″,”H123″,”E129”}** as **return_array**, **“N/A”** as **if_not_found**, **0 **as **match_mode **and **1 **as **search_mode**.

### 2. Apply Combined Functions to Check If Cell Contains Text to Extract Value in Excel

Now, suppose you want to check if a cell contains a specific text or letter in your dataset. You can check it by applying the **IF**, **ISNUMBER**, and **SEARCH** functions in Excel.

Here, we will check if the provided **Name **of the students contains **“J”** in it or not.

Here are the steps.

**Steps:**

- In the beginning, insert the following formula in
**Cell C5**and press**Enter**.

`=IF(ISNUMBER(SEARCH("J*",B5)),"Yes","")`

- After that, drag down the
**Fill Handle**tool to**AutoFill**the formula for the rest of the cells.

- Finally, you will find the texts containing
**“J”**in them.

🔎** How Does the Formula Work?**

- Firstly, we used the
**SEARCH**function to find**“J*”**in**Cell B5**. - Then, we used the
**ISNUMBER**to check the result of the**SEARCH**function is a number. - Lastly, the
**IF**function returns**“Yes”**if the resultant of the**ISNUMBER**function is**TRUE**, otherwise, it returns**Blank**.

### 3. Check If Cell Contains Text Then Return Value with OR Conditions

Next, we have a dataset containing the **color**, **variety, **and **size **of some **Products**. From this dataset, we want to search for **Jacket or M **size products. To do this, we will use **the OR function** in addition to the above functions.

**Steps:**

- Firstly, select
**Cell C5**and insert the following formula.

`=IF(OR(ISNUMBER(SEARCH("Jacket",B5)),ISNUMBER(SEARCH("M",B5))),"Yes","No")`

- 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 specific text then return value with
**OR**conditions.

🔎** How Does the Formula Work?**

- In the beginning, we used the
**SEARCH**and**ISNUMBER**functions to find**“Jacket”**and**“M”**size in**Cell B5**. - Next, we used the
**OR**function to apply the**OR**condition in the formula. - Finally, we used the
**IF**function to return**“Yes”**if the cell contains either**“Jacket”**or**“M”**.

### 4. Find If Cell Contains Text Then Return Value with AND Conditions

Additionally, you can also search for both **Jacket and M **size products in Excel using those functions. However, in this case, you will have to use **the AND function** instead of the **OR **function.

**Steps:**

- To start with, insert the following formula in
**Cell C5**and press**Enter**.

`=IF(AND(ISNUMBER(SEARCH("Jacket",B5)),ISNUMBER(SEARCH("M",B5))),"Yes","No")`

- After that, drag down the
**Fill Handle**tool to**AutoFill**the formula.

- Thus, you can check if a cell contains specific text, then return value with
**AND**conditions.

🔎** How Does the Formula Work?**

- Firstly, we used the
**SEARCH**and**ISNUMBER**functions to find**“Jacket”**and**“M”**size in**Cell B5**similarly like the previous method. - However, we used the
**AND**function. In this case, to apply the**AND**condition in the formula. - Finally, we used the
**IF**function to return**“Yes”**if the cell contains both**“Jacket”**and**“M”**.

### 5. Utilize IF, SUM & COUNTIF Functions to Check If Cell Contains Text with Multiple Conditions

You can also use the **IF**, **SUM** and **COUNTIF** functions to check if a cell contains specific text with multiple conditions. Here, we will search for **White **and **S **size products from the list. These functions will work similarly to **OR **condition.

**Steps:**

- Firstly, select
**Cell C5**and insert the following formula.

`=IF(SUM(COUNTIF(B5,{"White*","*S"})),"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 see that cells containing
**White**or**S**size products returning**“Yes”**as result.

🔎** How Does the Formula Work?**

- To start with, we used the
**COUNTIF**function to count the number of**“White*”**or**“*S”**size in**Cell B5**. - Next, we used the
**SUM**function to add those numbers. - Finally, we used the
**IF**function to return**“Yes”**if the cell contains**“White*”**or**“*S”**.

### 6. Check If Cell Contains Text Then Return Value with OR & AND Conditions

Now, we will show you how you can check for text values with both **OR **and **AND **conditions in Excel. Here, we will search for **Black **color **M **size products **or Red **color **L **size products from the list.

**Steps:**

- In the beginning, insert the following formula in
**Cell C5**and press**Enter**.

`=IF(OR(AND(C5="Black",D5="M"),AND(C5="Red",D5="L")),"Yes","No")`

- After that,
**AutoFill**the formula for the rest of the cells.

- Thus, you can check for specific text values with both
**OR**and**AND**conditions in Excel.

🔎** How Does the Formula Work?**

- Firstly, we used the
**AND**functions to check**“Black”**in**Cell C5**and**“M”**size in**Cell D5**. - Again, we used this function to check
**“Red”**in**Cell C5**and**“L”**size in**Cell D5**. - Finally, we used the
**IF**function to return**“Yes”**if the cell contains**“Black”**and**“M”**or else**“Red”**and**“L”**.

### 7. Use Nested IF Function to Return Value with Multiple Conditions in Excel

Lastly, we will use **the Nested-IF function **along with the **COUNTIF **function to check for **Black **color products from the given list.

**Steps:**

- To start with, insert the following formula in
**Cell C5**and press**Enter**.

`=IF(COUNTIF(B5,"*Black*"),"Yes",IF(B5<>"*Black*","No",""))`

- After that, drag down the
**Fill Handle**tool to**AutoFill**the formula.

- Thus, you can check for any specific text value and then return the value using the
**Nested-IF**function in Excel.

🔎** How Does the Formula Work?**

- Firstly, we used the
**COUNTIF**function as**logical_test**in the**IF**function to count the number of**“*Black*”**in**Cell B5**. - Then, If the resultant is
**TRUE**it will return**“Yes”**. - Otherwise, it will go into another
**IF**function and return**“No”**.

## Practice Section

In the article, you will find an Excel workbook like the image given below to practice on your own.

**Download Practice Workbook**

## Conclusion

So, in this article, we have shown you 7 ways to check if a cell contains text, then return value with multiple conditions 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.

**<< Go Back to Text | If Cell Contains | Formula List | Learn Excel**