# Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel (2023)

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.

7 Easy Ways to Check If Cell Contains Text Then Return Value with Multiple Conditions in Excel

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

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

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

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

(Video) Excel: Check IF Cell CONTAINS TEXT From LIST

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

Practice Section

Conclusion

Check If Cell Contains Text with Multiple Conditions.xlsx

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

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.

(Video) Excel IF Function: If Cell Contains Specific Text - Partial Match IF Formula

### 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”.
(Video) Excel IF Formula: Simple to Advanced (multiple criteria, nested IF, AND, OR functions)

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

(Video) Excel: If Cell Contains ONE of MANY THINGS 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. ## 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. And visit ExcelDemy for many more articles like this. Thank you!

(Video) Assign Categories Based on Keywords in Excel | Check if Cell Contains Text From List

## FAQs

### How do you write an IF THEN statement in Excel with multiple conditions? ›

Another way to get an Excel IF to test multiple conditions is by using an array formula. To complete an array formula correctly, press the Ctrl + Shift + Enter keys together. In Excel 365 and Excel 2021, this also works as a regular formula due to support for dynamic arrays.

How do I return a cell value based on multiple criteria in Excel? ›

To do an Excel lookup with multiple criteria, you can use the INDEX and MATCH functions.
1. The INDEX function can return a value from a specific place in a list.
2. The MATCH function can find the location of an item in a list.
Dec 10, 2022

Can I do an IF formula with multiple conditions? ›

The multiple IF conditions in Excel are IF statements contained within another IF statement. They are used to test multiple conditions simultaneously and return distinct values. The additional IF statements can be included in the “value if true” and “value if false” arguments of a standard IF formula.

How do you write an IF-THEN statement conditional? ›

A conditional statement (also called an if-then statement) is a statement with a hypothesis followed by a conclusion. The hypothesis is the first, or “if,” part of a conditional statement. The conclusion is the second, or “then,” part of a conditional statement. The conclusion is the result of a hypothesis.

Can I use an IF and then formula in conditional formatting? ›

But in conditional formatting, IF/THEN/ELSE syntax cannot be applied in a single rule. Conditional formatting is applied using IF/THEN logical test only. It must return TRUE for conditional formatting to be applied.

## Videos

1. Excel: IF Cell Contains Text Then Copy to Another Sheet | Copy Cell Based on Condition
(Chester Tugwell)
2. IF AND OR Formula in Excel with MULTIPLE CONDITIONS
(Presentation Mastery)
3. Return Multiple Match Results in Excel (2 methods)
(Leila Gharani)
4. Excel IF Function with PARTIAL Text Match (IF with Wildcards)
(Leila Gharani)
5. How to Test IF Cell CONTAINS TEXT - Excel Tutorial
(Chester Tugwell)
6. 15 IF Statement Examples in Excel - Simple to Advanced - Workbook Included
(TeachExcel)
Top Articles
Latest Posts
Article information

Author: Prof. An Powlowski

Last Updated: 05/02/2023

Views: 6070

Rating: 4.3 / 5 (44 voted)

Author information

Name: Prof. An Powlowski

Birthday: 1992-09-29

Address: Apt. 994 8891 Orval Hill, Brittnyburgh, AZ 41023-0398

Phone: +26417467956738

Job: District Marketing Strategist

Hobby: Embroidery, Bodybuilding, Motor sports, Amateur radio, Wood carving, Whittling, Air sports

Introduction: My name is Prof. An Powlowski, I am a charming, helpful, attractive, good, graceful, thoughtful, vast person who loves writing and wants to share my knowledge and understanding with you.