If you’re using Google Sheets and you have two separate columns of data in which you’re looking for matches, there’s a pretty easy way to highlight the matched items. You can also do it the other way around and highlight items that don’t match. You’re typically doing this because your lists are long and you can’t do it visually. However, for this example the lists are purposely small so it’s easy to see.
Conditional Formatting
We’re going to do it through Conditional Formatting. Go to your menu, then to Format, and select Conditional formatting. The Conditional formatting menu options will pop up on the right-hand side and there’s everything you need right here.
First, apply the conditional formatting to the range which, in this shared spreadsheet (which is also embedded near the end of this page), is C1 to C10. Note that the range is different in the video but the process is the same. You can select the range with your mouse or type it in as C1:C10.
At first, the range should be highlighted because it is set to highlight if it’s not empty. Change this to a custom formula, which is at the bottom. Click on Custom formula is and then it gives you a box that’s waiting for the formula.
You are going to be typing in a formula just like you’re typing into a spreadsheet cell. One of the differences though is now Google Sheets is not going to help you. Once you start typing the function, no helper text will pop up to explain the function. If you want help, type the formula into a cell in the spreadsheet and it’ll help you build it. Then, you can paste it into the custom formula box.
Video explanation
MATCH formula
You want to use a function called MATCH. MATCH is going to look at each cell in the range and check to see if it exists in the other range that you specify. The first parameter that you want, it’s going to do this one cell at a time, is C1. Now type a comma and Sheets will be looking for the range that you want to try to look up C1 in to see if it’s a duplicate. You don’t want this range to shift down every time it evaluates a cell on the right., so use dollar signs before the row numbers to fix the range as such A$1:A$7.
Next, your spreadsheet wants to know if this is going to be an exact match. Enter a 0 (zero) for yes so if it finds exactly Yellow. Now close it off with the parentheses. Your code should look like this:
=MATCH(C1,A$1:A$7,0)
The box that contains this function should have changed from red to blue meaning that the function is now a valid function. It should be working after the box turns blue. You can see it highlighted the color red in green because red is on the list on the left but it didn’t highlight orange because orange isn’t in that list. If you click done, you’ve highlighted everything that exists in the other list.
=ISNA(MATCH(C1,A$1:A$7,0))
If you want to do this the opposite way, and highlight the items in this list that aren’t in the list on the left, wrap your formula in the function called ISNA. The ISNA function is saying – look, if the MATCH function doesn’t work, highlight it. Not if it does work. After the ISNA, put a parenthesis and then go to the very end and close it with the parenthesis.
View of Sheet with ISNA applied
Live examples in Sheets
Using a plugin
As an alternative to the options above, you may want a plugin to do the heavy lifting for you. I like to use a plugin called Power Tools. This will give you a menu option with, among other things, several options to deal with duplicates. You can use the plugin to combine or remove duplicated rows.
Conclusion
After following this tutorial, you should be able to highlight matches or differences between two columns. If you have any questions, feel free to leave a comment below.
0 thoughts on “Google Sheets – Compare two lists for matches or differences”
Leave a Reply
You must be logged in to post a comment.
Meggan Kaiser says:
Thanks, great tutorial, love that you included a video 😀
admin says:
Thanks! The videos are a lot more fun to make.
Max says:
Super helpful and saved me a lot of time! You should set up a donation link so I can buy you a beer!
admin says:
Just buy one for someone random tonight and we’ll be good.
Adam Woolhether says:
Thanks for the tip!
Is possible to do this if the base-comparison list (A1:A7 in this example) is on another sheet of the spreadsheet?
For example, I tried:
=MATCH(C1,’Different Sheet 2′!A$1:A$7,0)
But it said invalid.
Adam Woolhether says:
Nevermind, Sheets just informed me that conditional formatting cannot reference a separate sheet.
Alexandra L Miller says:
This is not working for me in either direction to reliably find matches or differences. I’m trying to see if the students in my school have filled out a google form so I’m attempting to match last names in two different columns. I’m applying the match formula to check the whole school roster against the list of names from the forms. I’m gonna refer to these columns at Roster and Forms to try and explain what I’ve tried. I have used =Match(first cell of Roster, first cell of Forms:last cell of Forms,0) and I have used ISNA in front as well. These do highlight different names, but I keep getting highlights for names that are present in both lists, same spelling, same capitalization, no invisible characters in cells, no matter what I try…
Alexandra L Miller says:
Okay, I immediately figured it out after posting…the formula works great, we have a ton of student’s names misspelled on our roster…
Xtina says:
If I then want to delete the matched names – is there a way to do it using a formula ?
Prolific Oaktree says:
You could make a new list without dupes using the UNIQUE function.
Pete says:
Thanks for posting this, the formula works great but I want the formatting to apply to a row of cells rather than just the cell that is matched. Usually, just putting a $ before the column works but in this case, it still only formats the single cell. Is there a workaround/have I missed something?
I’m using this as my conditional format so that it checks if a value in column B can be found in column R. I’ve set the range as A1:H
=match($b1, r$3:r,0)
Prolific Oaktree says:
Try $R3 not R$3
mywebsite says:
Once you part with all the kids, you’ll find it quite… can you file divorce papers online
Victoria says:
You’ve just resolved for me in 10 minutes what I’ve spent 5 hours trying to figure out how to do. THANKYOU!!!