In this tutorial, I will discuss how to highlight cells that contain text from a list in excel. I will use **Conditional Formatting **to highlight cells. Furthermore, I will use several excel functions and formulas as Conditional Formatting to highlight cells.

**Table of Contents**hide

**Download Practice Workbook**

You can download the practice workbook that we have used to prepare this article.

**7 Ways to Highlight Cells That Contain Text from a List**

**1. Use COUNTIF Function to Highlight Cells That Have Text from a List**

You can use **the COUNTIF function** to highlight cells having the text from a list. For example, I have a list of **Fruit 1** (**B4:B14**) containing several fruit names. In another list, **Fruit 2** (**D4:D9**), I have some other fruit names. Now I will highlight fruit names of** Fruit 2** in the list of **Fruit 1**.

**Steps:**

- First, select the dataset (
**B5:B14**).

- Go to
**Home**>**Conditional Formatting**(**Styles**group).

- Next, go to the
**New Rule**from the**Conditional Formatting**drop-down.

- Consequently, the
**New Formatting Rule**window will show up. Then, choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. Later, click on the**Format**.

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

- Choose the highlight color from the
**Fill**Click**OK**.

- Again click
**OK**to close the dialog box.

- Finally, you will see all the cells containing text from the list
**Fruit 2**is highlighted.

**Read More: How to Highlight a Cell in Excel (5 Methods)**

**2. Apply Excel MATCH Function to Highlight Cells That Have Text from a List**

Now we will use **the MATCH function** to highlight cells having texts from a list.

**Steps:**

- First, select the dataset (
**B5:C14**).

- Go to
**Home**>**Conditional Formatting**>**New Rule**. - The
**New Formatting Rule**window will appear. Then, choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. After that, click on the**Format**button and choose the highlight color from the**Fill**tab.

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

- Click
**OK**>**OK**to close the dialog boxes.

- Consequently, all the cells containing text from the list
**Fruit 2**are highlighted in**Fruit 1**.

**Read More:** **How to Highlight Selected Text in Excel (8 Ways)**

**3. Combination of SUM & COUNTIF Functions to Highlight Cells**

This time I will use a combination of functions to highlight cells containing text from another list. For example, I will combine **the SUM function **along with the **COUNTIF** function to highlight cells. Here, I have a dataset **Books** (**B4:B12**) containing some book names. In another list **Text **(**D5:D8**), I have a list of single words. So, now I will highlight words of **Text** in dataset** Books**.

**Steps:**

- First, select the dataset (
**B5:B12**).

- Then go to the
**Home**tab > select**Conditional Formatting**drop-down. - Next, select
**New Rule**from the**Conditional Formatting**drop-down. - As a consequence, the
**New Formatting Rule**window will show up. Choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. After that, click on the**Format**button and select the highlight color from the**Fill**tab.

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

- Click
**OK**>**OK**to close the dialog boxes.

- Finally, cells containing text from the list
**Text**are highlighted in the**Books**.

Here, the** COUNTIF **function returns the number of cells within the range that meets the given condition. And the **SUM** function adds the number in a range.

**Read More:** **How to Highlight Cells in Excel Based on Value (9 Methods)**

**4. Use COUNT & SEARCH Functions to Highlight Cells That Contain Text from a List**

Likewise in **Method 3**, I will use a combination of functions to highlight cells. For instance, I will use the combination of the **COUNT **and **SEARCH** functions to highlight cells having the text from another list. This time, I will again use two fruit names lists as my dataset.

**Steps:**

- In the beginning, select the dataset (
**B5:B14**).

- Go to
**Home**>**Conditional Formatting**>**New Rule**. - The
**New Formatting Rule**dialog will pop up. Then, choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**.

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

- After that, click on the
**Format**button and choose the highlight color from the**Fill**tab. Click**OK**>**OK**to close the dialog boxes.

- As a consequence, cells containing fruit names from the list
**Fruit 2**are highlighted in**Fruit 1**.

Here, the **SEARCH **function counts the number of characters at which a specific character or text string is first found, reading left to right. On the other hand, 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 [2 Methods]**

**Similar Readings:**

**How to Highlight a Column in Excel (3 Methods)****Fill Cell with Color Based on Percentage in Excel (6 Methods)****How to Highlight Every 5 Rows in Excel (4 Methods)****Highlight Cell Using the If Statement in Excel (7 Ways)****How to Compare Two Excel Sheets and Highlight Differences (7 Ways)**

**5. Apply Simple Formula and Drop Down List to Highlight Cells from a List**

This time, I will use a simple formula and drop-down list to highlight cells. For this purpose, I will create a drop-down list. Then in the **Conditional Formatting** rule, I will put the formula. This time, I will again use two fruit names lists as my dataset.

**Steps:**

- First, create the drop-list from some fruit names in
**Cell D5**.

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

- Go to
**Home**>**Conditional Formatting**. - Next, go to
**Conditional Formatting**drop-down >**New Rule**. - Consequently, the
**New Formatting Rule**dialog will appear. After that, choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. Click on the**Format**button and select the highlight color from the**Fill**tab.

`=$B5=$D$5`

- Click
**OK**>**OK**to close the dialog boxes.

- As a result, cells containing fruits from the drop-down list are highlighted.

- This method works dynamically which means if you change the fruit name from the drop-down list cells highlighted cells change accordingly.

**Read More:**** How to Highlight from Top to Bottom in Excel (5 Methods)**

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

Now, I will use a combination of functions and a drop-down list to highlight cells. For instance, I will use the combination of the **ISNUMBER **and **FIND** functions to highlight cells having the text from another list. This time, I will again use two fruit names lists as my dataset.

**Steps:**

- First, create the drop-down list from some fruit names in
**Cell D5**.

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

- Go to
**Home**>**Conditional Formatting**>**New Rule**. - The
**New Formatting Rule**window will show up. Choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. After that, click on the**Format**button and choose the highlight color from the**Fill**tab.

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

- Click
**OK**after you choose the format color. Again click**OK**to close the dialog boxes.

- Finally, cells containing text from the list
**Text**are highlighted in the**Books**.

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

**Read More:** **How to Highlight Selected Cells in Excel (5 Easy Ways)**

**7. SUMPRODUCT, ISNUMBER, and SEARCH Functions Combination to Highlight Cells That Contain Text from a List**

In this method, I will use** the SUMPRODUCT function** along with **the ISNUMBER function** and **SEARCH **function. I will highlight book names in **Books** (**B4:B12**) with words from the **Text **(**B5:B8**)

**Steps:**

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

- Go to
**Home**>**Conditional Formatting**drop-down. - Next, select the
**New Rule**from the**Conditional Formatting**drop-down. - As a result, the
**New Formatting Rule**dialog box will appear. Choose the**Rule Type: Use a formula to determine which cells to format**. Type the below formula in the field:**Format values where this formula is true**. Then, click on the**Format**button and choose the highlight color from the**Fill**tab.

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

- Click
**OK**>**OK**to close the dialog boxes.

- Finally, 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**}

Here, 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 replies:

{**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 replies:

{**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.

**Related Content:** **Excel Formula Based on Cell Color (5 Examples)**

## Conclusion

In the above article, I have tried to discuss several methods to highlight cells that contain text from a list elaborately. Hopefully, these methods and explanations will be enough to solve your problems. Please let me know if you have any queries.