Method 1 – Using the XLOOKUP Function to Check If a Cell Contains Text and Return the Value in Excel
Use the XLOOKUP Function.
The sample dataset contains students’ Names. You want to assign Passwords to these students.
Steps:
- Select C5 and enter the following formula.
=XLOOKUP(B5,{"Jim","Harry","Emma"},{"J111","H123","E129"},"N/A",0,1)
- Press Enter and drag down the Fill Handle to AutoFill the rest of the cells.
- Passwords based on students’ Names will be displayed.
B5 is the lookup_value, {“Jim”,”Harry”,”Emma”} is the lookup_array, {“J111″,”H123″,”E129”} is the return_array, “N/A” is if_not_found, 0 is match_mode and 1 is search_mode.
Method 2 – Applying Combined Functions to Check If a Cell Contains Text and Extract the Value in Excel
Combine the IF, ISNUMBER, and SEARCH functions.
To check if the Name contains “J” :
Steps:
- Enter the following formula in C5 and press Enter.
=IF(ISNUMBER(SEARCH("J*",B5)),"Yes","")
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Formula Breakdown
- The SEARCH function finds “J*” in B5.
- The ISNUMBER checks if the result of the SEARCH function is a number.
- The IF function returns “Yes” if the result of the ISNUMBER function is TRUE. Otherwise, it returns Blank.
Method 3 – Checking If a Cell Contains Text and Return the Value with OR Conditions
Use the OR function combined with the above functions.
Steps:
- Select C5 and enter the following formula.
=IF(OR(ISNUMBER(SEARCH("Jacket",B5)),ISNUMBER(SEARCH("M",B5))),"Yes","No")
- Press Enter and drag down the Fill Handle to AutoFill the rest of the cells.
- This is the output.
Formula Breakdown
- The SEARCH and ISNUMBER functions find “Jacket” and “M” size in B5.
- The OR function applies the OR condition in the formula.
- The IF function returns “Yes” if the cell contains either “Jacket” or “M”.
Method 4 – Finding If a Cell Contains Text and Returning a Value with AND Conditions
Use the AND function.
Steps:
- Enter the following formula in C5 and press Enter.
=IF(AND(ISNUMBER(SEARCH("Jacket",B5)),ISNUMBER(SEARCH("M",B5))),"Yes","No")
- Drag down the Fill Handle to AutoFill the rest of the cells.
- This is the output.
Formula Breakdown
- The SEARCH and ISNUMBER functions find “Jacket” and “M” size in B5.
- The AND function applies the AND condition to the formula.
- The IF function returns “Yes” if the cell contains both “Jacket” and “M”.
Method 5 – Utilizing the IF, SUM & COUNTIF Functions to Check If a Cell Contains Text with Multiple Conditions
Use the IF, SUM and COUNTIF functions.
You want to search White and S size products.
Steps:
- Select C5 and enter the following formula.
=IF(SUM(COUNTIF(B5,{"White*","*S"})),"Yes","No")
- Press Enter and drag down the Fill Handle to AutoFill the rest of the cells.
- This is the output.
Formula Breakdown
- The COUNTIF function counts the number of “White*” or “*S” in B5.
- The SUM function adds those numbers.
- The IF function returns “Yes” if the cell contains “White*” or “*S”.
Method 6 – Checking If a Cell Contains Text and Return the Value with OR & AND Conditions
Search Black color M size products or Red color L size products.
Steps:
- Enter the following formula in C5 and press Enter.
=IF(OR(AND(C5="Black",D5="M"),AND(C5="Red",D5="L")),"Yes","No")
- Drag down the Fill Handle to see the result in the rest of the cells.
- This is the output.
Formula Breakdown
- The AND function checks “Black” in C5 and “M” in D5. It also checks “Red” in C5 and “L” in D5.
- The IF function returns “Yes” if the cell contains “Black” and “M” or “Red” and “L”.
Method 7 – Using a Nested IF Function to Return the Value with Multiple Conditions in Excel
Search Black color products.
Steps:
- Enter the following formula in C5 and press Enter.
=IF(COUNTIF(B5,"*Black*"),"Yes",IF(B5<>"*Black*","No",""))
- Drag down the Fill Handle to AutoFill the rest of the cells.
- This is the output.
Formula Breakdown
- The COUNTIF function performs a logical_test in the IF function to count the number of “*Black*” in B5.
- If the result is TRUE, it returns “Yes”.
- Otherwise, it will perform another IF function and return “No”.
Practice Section
Practice here.
Download Practice Workbook
<< Go Back to Text | If Cell Contains | Formula List | Learn Excel
Get FREE Advanced Excel Exercises with Solutions!
