Highlight Cells That Contain Text from a List in Excel (7 Easy Ways)

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.


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

Use COUNTIF Function to Highlight Cells that have Text from a List

  • Go to Home > Conditional Formatting (Styles group).

Use COUNTIF Function to Highlight Cells that have Text from a List

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

Use COUNTIF Function to Highlight Cells that have Text from a List

  • 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)

Use COUNTIF Function to Highlight Cells that have Text from a List

  • Choose the highlight color from the Fill Click OK.

Use COUNTIF Function to Highlight Cells that have Text from a List

  • Again click OK to close the dialog box.

Use COUNTIF Function to Highlight Cells that have Text from a List

  • 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).

Apply Excel MATCH Function to Highlight Cells that have Text from a List

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

Apply Excel MATCH Function to Highlight Cells that have Text from a List

  • 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).

Combination of  SUM & COUNTIF Functions to Highlight Cells that Contain Text from a List

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

Combination of  SUM & COUNTIF Functions to Highlight Cells that Contain Text from a List

  • 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).

Use COUNT & SEARCH Functions to Highlight Cells that Contain Text from a List

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

Use COUNT & SEARCH Functions to Highlight Cells that Contain Text from a List

  • 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


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.

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

  • Select the dataset (B5:B14).

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

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

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

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

Excel ISNUMBER and FIND Functions to Highlight Cells from a List

  • Select the dataset (B5:B14).

Excel ISNUMBER and FIND Functions to Highlight Cells from a List

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

Excel ISNUMBER and FIND Functions to Highlight Cells from a List

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

SUMPRODUCT, ISNUMBER, and SEARCH Functions Combination to Highlight Cells that Contain Text from a List

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

SUMPRODUCT, ISNUMBER, and SEARCH Functions Combination to Highlight Cells that Contain Text from a List

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


Related Articles

Hosne Ara

Hosne Ara

Hi, This is Hosne Ara. Currently, I do write for ExcelDemy. I have a long experience working with different industries and I have seen how vast the scope of Microsoft Excel is. So, eventually, I started to write articles on Excel and VBA. Basically, my articles are targeted to help people who are working in Excel. By profession, I am an Engineer. Materials and Metallurgical Engineering is my major. Besides, I am a certified Project Manager (PMP) too. I have worked with Power Plant and IT industry earlier. As a person, I am detail-oriented and love doing research. Establishing a greener world is one of my mottos.

We will be happy to hear your thoughts

Leave a reply

5 Excel Hacks You Never Knew

Genius tips to help you unlock Excel's hidden features

FREE EMAIL BONUS

ExcelDemy
Logo