**Method 1 – Use the COUNTIF Function to Highlight Cells That Have Text from a List**

We have a list of Fruit 1 (**B4:B14**) containing several fruit names. In another list, Fruit 2 (**D4:D9**), we have some other fruit names. We will highlight the names from Fruit 2 in the list Fruit 1.

**Steps:**

- Select the dataset (
**B5:B14**).

- Go to
**Home**and select**Conditional Formatting**(in the**Styles**group).

- Go to
**New Rule**from the**Conditional Formatting**drop-down.

- The
**New Formatting Rule**window will open. Choose the**Rule Type: Use a formula to determine which cells to format**. - Insert the formula below in the field:
**Format values where this formula is true**.

`=COUNTIF($D$5:$D$9,B5)`

- Click on
**Format**.

- Choose the highlight color from
**Fill**and click**OK**.

- Click
**OK**to close the dialog box.

- You will see all the cells containing text from the list Fruit 2 highlighted.

**Method 2 – Apply the MATCH Function to Highlight Cells That Have Text from a List**

**Steps:**

- Select the dataset (
**B5:C14**).

- Go to
**Home,**then to**Conditional Formatting,**and select**New Rule**. - Choose the
**Rule Type: Use a formula to determine which cells to format**. - Use the following formula in the field:
**Format values where this formula is true**.

`=MATCH($B5,$E$5:$E$9,0)`

- Click on the
**Format**button and choose the highlight color from the**Fill**tab. - Click
**OK twice**to close the dialog boxes.

- All the cells containing text from the list Fruit 2 are highlighted in Fruit 1.

**Read More:** How to Highlight Selected Text in Excel

**Method 3 – Combining SUM and COUNTIF Functions to Highlight Cells**

We have a dataset named Books (**B4:B12**), containing some book names. In another list Text (**D5:D8**), we have a list of single words. We will highlight the words of Text in dataset Books.

**Steps:**

- Select the dataset (
**B5:B12**).

- Go to the
**Home**tab and select**Conditional Formatting**drop-down. - Select
**New Rule**. - The
**New Formatting Rule**window will show up. Choose the**Rule Type: Use a formula to determine which cells to format**. - Use the following formula in the field:
**Format values where this formula is true**.

`=SUM(COUNTIF(B5,"*"&$D$5:$D$8&"*"))`

- Click on the
**Format**button and select the highlight color from the**Fill**tab. - Click
**OK twice**

- Cells containing text from the list Text are highlighted in the Books.

**Read More:** How to Highlight Cells in Excel Based on Value

**Method 4 – Use COUNT and SEARCH Functions to Highlight Cells That Contain Text from a List**

**Steps:**

- Select the dataset (
**B5:B14**).

- Go to
**Home,**then to**Conditional Formatting,**and select**New Rule**. - Choose the
**Rule Type: Use a formula to determine which cells to format**. - Use the following formula in the field:
**Format values where this formula is true**.

`=COUNT(SEARCH(D$5:D$9,B5))`

- Click on the
**Format**button and choose the highlight color from the**Fill**tab. - Click
**OK twice**to close the dialog boxes.

- Cells containing fruit names from the list
**Fruit 2**are highlighted in**Fruit 1**.

The **SEARCH **function counts the number of characters at which a specific character or text string is first found, reading left to right. The **COUNT** function counts the number of cells in a range that contains numbers.

**Read More: **How to Highlight Cells Based on Text in Excel

**Method 5 – Apply a Formula and a Drop-Down List to Highlight Cells from a List**

**Steps:**

- Create the drop-down list from some fruit names in cell
**D5**. (Use the Data Validation option and insert the values manually or via a formula into the Source panel.)

- Select the dataset (
**B5:B14**).

- Go to
**Home**and select**Conditional Formatting**. - Choose
**New Rule**. - The
**New Formatting Rule**dialog will appear. Choose the**Rule Type: Use a formula to determine which cells to format**. - Insert the following formula in the field:
**Format values where this formula is true**.

`=$B5=$D$5`

- Click on the
**Format**button and select the highlight color from the**Fill**tab. - Click
**OK**twice

- Cells containing fruits from the drop-down list are highlighted.

- If you change the fruit name from the drop-down, the highlighted cells change accordingly.

**Method 6 – Excel ISNUMBER and FIND Functions to Highlight Cells from a List**

**Steps:**

- Create the drop-down list from some fruit names in
**Cell D5**.

- Select the dataset (
**B5:B14**).

- Go to
**Conditional Formatting**and choose**New Rule**. - Choose the
**Rule Type: Use a formula to determine which cells to format**. - Insert the following formula in the field:
**Format values where this formula is true**.

`=ISNUMBER(FIND($D$5,B5))`

- Click on the
**Format**button and choose the highlight color from the**Fill**tab. - Click
**OK twice**to close the dialog boxes.

- Here’s the result.

The **FIND **function returns the starting position of one text string within another text string (case-sensitive). The** ISNUMBER** function checks whether a value is a number and returns** TRUE** or **FALSE**.

**Method 7 – SUMPRODUCT, ISNUMBER, and SEARCH Function Combination to Highlight Cells That Contain Text from a List**

**Steps:**

- Select the dataset (
**B5:B12**).

- Select
**New Rule**from the**Conditional Formatting**drop-down. - The
**New Formatting Rule**dialog box will appear. - Choose the
**Rule Type: Use a formula to determine which cells to format**. - Insert the following formula in the field:
**Format values where this formula is true**.

`=SUMPRODUCT(--ISNUMBER(SEARCH($D$5:$D$8,B5)))>0`

- Click on the
**Format**button and choose the highlight color from the**Fill**tab. - Click
**OK twice**to close the dialog boxes.

- Cells containing book names with the text from the list Text are highlighted.

** How Does the Formula Work?**

**➤ SEARCH($D$5:$D$8,B5)**

This part of the formula returns:

{**#VALUE!;#VALUE!;1;5**}

The** SEARCH** function returns the position of the value. But it returns an error (**#VALUE!**) if the value is not found.

**➤ ISNUMBER(SEARCH($D$5:$D$8,B5))**

The** ISNUMBER** function converts all error results to **FALSE** and other results to **TRUE**.

This part returns:

{**FALSE;FALSE;TRUE;TRUE**}

**➤ –ISNUMBER(SEARCH($D$5:$D$8,B5))**

This part returns:

{**0;0;1;1}**

The double minus (**—**) sign converts all** FALSE **and** TRUE **values to** 0** and** 1**.

**➤ SUMPRODUCT(–ISNUMBER(SEARCH($D$5:$D$8,B5)))>0**

This part returns:

{**TRUE;FALSE;TRUE;FALSE;FALSE;TRUE;FALSE;TRUE**}

Finally, the **SUMPRODUCT **function tests the previous result against zero (**0**) and thus guides the **Conditional Formatting **rule to highlight cells.

**Download the Practice Workbook**

**Related Articles**

- How to Highlight Blank Cells with Conditional Formatting in Excel
- How to Highlight Cell If Value Is Less Than Another Cell in Excel
- How to Highlight Cells in Excel but Not Print
- Highlight Cell Using the If Statement in Excel

**<< Go Back to Highlight Cell | Highlight in Excel | Learn Excel**

I have tried each one of your suggestions above and they are all hightlighting the cell above instead of the cell the information is in.

Hello TAMMY,

Thanks for reaching out to us.

Although I couldn’t fully grasp your query, I’m assuming you want to highlight the cells in the Fruit 2 list (referring to Method 1 for example) instead of the Fruit 1 list.

If that’s the case, you have to select the Fruit 2 range (D4:D9) first. Then, click Home > Conditional Formatting and follow the rest of the steps.

However, in the Format values where this formula is true field, you have to insert =COUNTIF($B$5:$B$14,D5)

If every other thing is okay, the cells will get highlighted.

Feel free to reach out to me at [email protected] if the above suggestions didn’t meet your requirement.

Good luck!

Same problem as TAMMY had. I’ve tried several formulas but it’s highlighting either one above or the one bleow cell.

Hello,

Thanks for your comment.

The formulas are all right. They are working very nicely in our case. You have to insert the formulas carefully in the conditional formatting. Make sure you have put the cell references either absolute or relative correctly. Or if you can provide more information about your dataset then we could help.

If you have other queries let us know in the comment.

Regards,

Sajid Ahmed

Exceldemy