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