No doubt, in Google Sheets, to highlight matches or differences in two lists you should use conditional formatting.

The custom formula rule that you may want to use in conditional formatting may not be the same that you use to extract the matches or differences.

To highlight matches or differences, you can use the functions Match or Countif. I am using the Countif here.

I am going to use the exact same formula for highlighting the matches or differences! Yes, you heard me right. Do you know how?

Highlight Matches: In this case, I will highlight the matching values in either of the two lists (List 1 or List 2) with my choice of color other than white.

You can decide the matching value in which list to highlight (List 1 or List 2). To demonstrate both, I am using two tables.

In the first table, I am going to highlight matching values in List 2 (Column B) and in the second table the same in List 1 (Column D).

Screenshot 1:

Highlight Differences: In this, I will use the same above formula but will highlight the matches with white color.

Then I’ll set one more conditional formatting rule to highlight non-blanks. That makes the effect of highlighting differences in two lists!

Screenshot 2:

Why I am using the same formula?

The reason, I am not limiting the List range in a limited row. The range will be open (infinite) so there may be blank rows at the end of the list and I don’t want the highlighting to affect such cells.

From the examples below you can understand all these.

All About Highlighting Matches or Differences in Two Lists in Google Sheets

I am going to provide you the formulas that highlight matches or differences in two lists in Google Sheets.

In each of the above, I mean highlighting matches or differences, there are two formulas involved.

One for highlighting the first list and the second for the second list. You can use the formula that you want.

The Formula to Highlight Matches in Two Lists in Google Sheets

(Refer to Screenshot # 1)

Conditional Format/Highlight Matches in List 1

Here I am using the data in the range D2:E (Table 2). So in conditional formatting (Format > Conditional formatting) select the range D2:D and apply the below formula in the custom field.

=ArrayFormula(countif($E$2:$E,$D2:$D))>0

Conditional Format/Highlight Matches in List 2

Here I am using the data in the range A2:B (Table 1). So in conditional formatting select the range B2:B and apply the below formula in the custom field.

=ArrayFormula(countif($A$2:$A,$B2:$B))>0

The Formula to Highlight Differences in Two Lists in Google Sheets

(Refer to Screenshot # 2)

We are discussing how to highlight matches or differences in two lists in Google Sheets. In that, I have already covered the Matching part.

See now how to highlight the differences. Here the formulas are the same as above. But there is one difference in applying these.

The earlier two formattings I have applied in Sheet1. The below one I am going to apply in Sheet2 (a copy of the tables in Sheet1 without formatting applied).

I am clarifying this because you may see the same cell references again in the formulas.

Conditional Format/Highlight Differences in List 1

Here again, my data is in the range D2:E, I mean, I am using Table 2.

To highlight differences in the first column, in conditional formatting, select the range D2:D and apply the below formula in the custom field.

=ArrayFormula(countif($E$2:$E,$D2:$D))>0

Choose the color white and that is important.

Now you should apply one more conditional format setting in the same range as below.

Conditional Format/Highlight Differences in List 2

Here my data is in the range A2:B (Table 1) So in conditional formatting select the range B2:B and apply the below formula in the custom field.

=ArrayFormula(countif($A$2:$A,$B2:$B))>0

Here also set the color to white and then add another rule (see the just above screenshot) as earlier.

This way you can highlight matches or differences in two lists in Google Sheets.

Additional Tips

How to highlight matches or differences in two lists when List 1 is in Sheet1 and List 2 is in Sheet2 (Two Different Sheet Tabs)?

Here you must use the Indirect function. I’ll explain how?

If you try to apply conditional formatting between two sheet tabs without Indirect, you would probably see the following alert.

Conditional format rule cannot refer to a different Sheet.

In the below example I am highlighting the matches when the lists are in two sheet tabs.

My List 1 is in Sheet1 A2:A and List 2 is in Sheet2 B2:B.

Select the range A2:A in Sheet1 and use this formula.

=ArrayFormula(countif(indirect("Sheet2!$B$2:$B"),$A2:$A))>0

Hope this makes sense.

That’s all. Enjoy!