No doubt, highlighting matches or differences in two lists in Google Sheets is crucial for many tasks. Luckily, we can easily achieve this by utilizing conditional formatting. In this article, I will guide you through the process and provide you with helpful formulas.
Highlight Matches
To highlight matches in two lists, you can use functions like Match or Countif. In this case, I’ll be using Countif. Surprisingly, I’ll be using the exact same formula to highlight both matches and differences. Let me explain how.
In the first scenario, I’ll be highlighting the matching values in either List 1 or List 2 with a color of my choice. For demonstration purposes, I’ll be using two tables. In the first table, I’ll highlight the matching values in List 2 (Column B), and in the second table, I’ll do the same for List 1 (Column D).
Highlight Differences
For highlighting differences, I’ll be using the same formula as above, but this time I’ll be highlighting the matches with a white color. Additionally, I’ll set another conditional formatting rule to highlight non-blank cells. This effectively highlights the differences between the two lists.
“But why am I using the same formula?”, you may wonder. The reason is simple. I don’t want the highlighting to affect blank cells, and by keeping the range open, including potential blank rows at the end of the list, I avoid this issue.
You can gain a better understanding of all these concepts through the examples below.
All About Highlighting Matches or Differences in Two Lists in Google Sheets
In this section, I will provide you with the formulas that allow you to highlight matches or differences in two lists in Google Sheets. Keep in mind that for each scenario, highlighting matches or differences, there are two formulas involved. One is used to highlight the first list, and the other is used for the second list. You can choose the formula that suits your needs.
The Formula to Highlight Matches in Two Lists in Google Sheets
(Refer to Screenshot #1)
Conditional Format/Highlight Matches in List 1
To highlight matches in List 1, use the following formula in the custom field of the conditional formatting (Format > Conditional Formatting) for the range D2:D:
=ArrayFormula(countif($E$2:$E,$D2:$D))>0
Conditional Format/Highlight Matches in List 2
To highlight matches in List 2, use the following formula in the custom field of the conditional formatting for the range B2:B:
=ArrayFormula(countif($A$2:$A,$B2:$B))>0
The Formula to Highlight Differences in Two Lists in Google Sheets
(Refer to Screenshot #2)
Now let’s talk about highlighting the differences. The formulas used for highlighting matches are the same, but the way we apply them differs.
Conditional Format/Highlight Differences in List 1
To highlight differences in List 1, select the range D2:D, which corresponds to Table 2. Apply the following formula in the custom field of the conditional formatting:
=ArrayFormula(countif($E$2:$E,$D2:$D))>0
Choose the color white, as it is essential for this step. Additionally, apply another conditional format setting in the same range.
Conditional Format/Highlight Differences in List 2
For highlighting differences in List 2, select the range B2:B, which corresponds to Table 1. Apply the following formula in the custom field of the conditional formatting:
=ArrayFormula(countif($A$2:$A,$B2:$B))>0
Once again, set the color to white and add another rule, as shown in the previous screenshot.
This process allows you to effectively highlight matches or differences in two lists using 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)?
When dealing with lists in two different sheet tabs, you must use the Indirect function. If you try to apply conditional formatting between two sheet tabs without using Indirect, you will likely encounter an alert.
In the example below, I am highlighting matches when the lists are in two different sheet tabs. List 1 is located in Sheet1 A2:A, and List 2 is located in Sheet2 B2:B. To accomplish this, select the range A2:A in Sheet1 and use the following formula:
=ArrayFormula(countif(indirect("Sheet2!$B$2:$B"),$A2:$A))>0
I hope this explanation makes sense. Now go ahead and enjoy highlighting matches or differences in your Google Sheets!
For more information and helpful resources, visit Crawlan.com.
