by Whether there's summer knocking on our doors or winter invading Westeros, we still work in Google Sheets and have to compare different pieces of tables with one another. In this article, I'm sharing ways of matching your data and giving away tips on doing that swiftly.
Compare two columns or sheets
One of the tasks you may have is to scan two columns or sheets for matches or differences and identify them somewhere outside the tables.
Compare two columns in Google Sheets for matches and differences
I'll start by comparing two cells in Google Sheets. This way lets you scan entire columns row by row.
Example 1. Google Sheets – compare two cells
For this first example, you will need a helper column in order to enter the formula into the first row of the data to compare:
=A2=C2
If cells match, you'll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:
Tip. To compare columns from different files, you need to use the IMPORTRANGE function:
=A2=IMPORTRANGE("spreadsheet_url","Sheet1!A2")
Example 2. Google Sheets – compare two lists for matches and differences
- A neater solution would be to use the IF function. You'll be able to set the exact status for identical and different cells:
=IF(A2=C2,"Match","Differ")
Tip. If your data is written in different cases and you'd like to consider such words as different, here's the formula for you:
=IF(EXACT(A2,C2),"Match","Differ")Where EXACT considers the case and looks for the complete identicals.
- To identify only rows with duplicate cells, use this formula:
=IF(A2=C2,"Match","") - To mark only rows with unique records between cells in two columns, take this one:
=IF(A2=C2,"","Differ")
Example 3. Compare two columns in Google Sheets
-
There's a way to avoid copying the formula over each row. You can forge an array IF formula in the first cell of your helper column:
=ArrayFormula(IF(A2:A=C2:C,"","Differ"))This IF pairs each cell of column A with the same row in column C. If records are different, the row will be identified accordingly. What is nice about this array formula is that it automatically marks each and every row at once:
-
In case you'd rather name the rows with identical cells, fill the second argument of the formula instead of the third one:
=ArrayFormula(IF(A2:A=C2:C,"Match",""))
Example 4. Compare two Google Sheets for differences
Oftentimes you need to compare two columns in Google Sheets that belong inside a huge table. Or they can be entirely different sheets like reports, price lists, working shifts per month, etc. Then, I believe, you can't afford to create a helper column or it can be quite difficult to manage.
If this sounds familiar, don't worry, you can still mark the differences on another sheet.
Here are two tables with products and their prices. I want to locate all cells with different contents between these tables:
Start with creating a new sheet and enter the next formula into A1:
=IF(Sheet1!A1<>Sheet2!A1,Sheet1!A1&" | "&Sheet2!A1,"")
Note. You must copy the formula over the range equal to the size of the biggest table.
As a result, you will see only those cells that differ in contents. The formula will also pull records from both tables and separate them with a character you enter into the formula:
Tip. If the sheets to compare are in different files, again, just incorporate the IMPORTRANGE function:
=IF(Sheet1!A1<>IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),Sheet1!A1&" | "&IMPORTRANGE("2nd_spreadsheet_url","Sheet1!A1"),"")
Tools for Google Sheets to compare two columns and sheets
Of course, each of the above examples can be used to compare two columns from one or two tables or even match sheets. However, there are a few tools we created for this task that will benefit you a lot.
Compare sheets add-on
This first one will compare two (& more!) Google sheets and columns for duplicates or uniques in 5 steps. Make it mark the found records with a status column (that can be filtered, by the way) or color, copy or move them to another location, or even clear cells and delete entire rows with dupes whatsoever.
I used the add-on to find the rows from Sheet1 that are absent from Sheet2 (and vice versa) based on Fruit and MSRP columns:
Then I saved my settings into one scenario. Now I can quickly run them without going through all steps again whenever records in my tables change. I just need to start that scenario from the Google Sheets menu:
If you're feeling excited about this tool, go ahead and click that image below to install it from the Google Workspace Marketplace. You'll notice how much time it saves you :)
This help page will gently guide you in case you're stuck on any step.
Compare sheets cell by cell
This on is also part of the Compare Sheets collection. It will compare your Google Sheets for differences. Whether you have two or more tables, it will check them all cell by cell and create one thorough report with differences from all sheets grouped accordingly.
Here's an example of the same two tables. The add-on creates one report with not only different cells (marked with yellow) but also unique rows (marked with red and blue):
Video: How to work with the comparison report
To look at the report and all its parts closely, feel free to read this tutorial or watch this demo video:
Try both add-ons for yourself and notice how much time they save you. :)
Compare data in two Google Sheets and fetch missing records
Comparing two Google Sheets for differences and repeats is half the work, but what about missing data? There are special functions for this as well, for example, VLOOKUP. Let's see what you can do.
Find missing data
Example 1
Imagine you have two lists of products (columns A and C in my case, but they can simply be on different sheets). You need to find those presented in the first list but not in the second one. This formula will do the trick:
=ISERROR(VLOOKUP(A2,$C:$C,1,0))
How does the formula work:
- VLOOKUP searches for the product from A2 in the second list. If it's there, the function returns the product name. Or else you will get an #N/A error meaning the value wasn't found in column C.
- ISERROR checks what VLOOKUP returns and shows you TRUE if it's the value and FALSE if it's the error.
Thus, cells with FALSE are what you're looking for. Copy the formula to other cells to check each product from the first list:
Note. If your columns are in different sheets, your formula will reference one of them:
=ISERROR(VLOOKUP(A2,Sheet2!$C:$C,1,0))
Tip. To get by with a one-cell formula, it should be an array one. Such formula will automatically fill all cells with results:
=ArrayFormula(ISERROR(VLOOKUP(A2:A10,$C:$C,1,0)))
Example 2
Another smart way would be to count all appearances of the product from A2 in column C:
=IF(COUNTIF($C:$C, $A2)=0, "Not found", "")
If there's absolutely nothing to count, the IF function will mark cells with Not found. Other cells will remain empty:
Example 3
Where there's VLOOKUP, there's MATCH. You know that, right? ;) Here's the formula to match products rather than count:
=IF(ISERROR(MATCH($A2,$C:$C,0)),"Not found","")
Tip. Feel free to specify the exact range of the second column if it remains the same:
=IF(ISERROR(MATCH($A2,$C2:$C28,0)),"Not found","")
Pull matching data
Example 1
Your task may be a bit fancier: you may need to pull all missing information for the records common for both tables, for example, update prices. If so, you'll need to wrap MATCH in INDEX:
=INDEX($E:$E,MATCH($A2,$D:$D,0))
The formula compares fruits in column A with fruits in column D. For everything found, it pulls the prices from column E to column B.
Example 2
As you may have guessed, another example would use the Google Sheets VLOOKUP function that we described some time ago.
Yet, there are a few more instruments for the job. We described them all in our blog as well:
Merge sheets using the add-on
If you're tired of formulas, you can use our Merge Sheets add-on to quickly match and merge two Google sheets.
Alongside its basic purpose to pull the missing data, it can also update existing values and even add non-matching rows. You can see all changes in colour or in a status column that can be filtered.
The 2.0 version of Merge Sheets will merge not just 2 tables (one main with one lookup) but multiple sheets in a row (one main with several lookups). The data from the lookup sheets will be added to your main one by one: as you added them in the add-on. Lots of additional options will make your merge as comprehensive as you need.
Video: How to use Merge Sheets add-on for Google Sheets
Check out this video about the Merge Sheets add-on. Though it features just 2 sheets, it paints a clear picture of the add-on possibilities:
Conditional formatting to compare data in two Google Sheets
There's one more standard way Google offers to compare your data – by colouring matches and/or differences via conditional formatting. This method makes all records you're looking for stand out instantly. Your job here is to create a rule with a formula and apply it to the correct data range.
Highlight duplicates in two sheets or columns
Let's compare two columns in Google Sheets for matches and colour only those cells in column A that tally with cells in the same row in column C:
- Select the range with records to color (A2:A10 for me).
- Go to Format > Conditional formatting in the spreadsheet menu.
- Enter a simple formula to the rule:
=A2=C2 - Pick the color to highlight cells.
Tip. If your columns change in size constantly and you want the rule to consider all new entries, apply it to the entire column (A2:A, assuming the data to compare starts from A2) and modify the formula like this:
=AND(A2=C2,ISBLANK(A2)=FALSE)
This will process entire columns and ignore empty cells.
Note. To compare data from two different sheets, you'll have to make other adjustments to the formula. You see, conditional formatting in Google Sheets doesn't support cross-sheet references. However, you can access other sheets indirectly:
=A2=INDIRECT("Sheet2!C2:C")
In this case, please specify the range to apply the rule to – A2:A10.
Compare two Google sheets and columns for differences
To highlight records that don't match cells on the same row in another column, the drill is the same as above. You select the range and create a conditional formatting rule. However, the formula here differs:
=A2<>C2
Again, modify the formula to make the rule dynamic (have it consider all newly added values in these columns):
=AND(A2=C2,ISBLANK(A2)=FALSE)
And use the indirect reference to another sheet if the column to compare with is there:
=A2<>INDIRECT("Sheet1!C2:C")
Note. Don't forget to specify the range to apply the rule to – A2:A10.
Compare two lists and highlight records in both of them
Of course, it's more likely the same records in your columns will be scattered. The value in A2 in one column will not necessarily be on the second row of another column. In fact, it may appear much later. Clearly, this requires another method of searching for the items.
Example 1. Compare two columns in Google Sheets and highlight differences (uniques)
To highlight unique values in each list, you must create two conditional formatting rules for each column.
Color column A: =COUNTIF($C$2:$C$9,$A2)=0
Color column C: =COUNTIF($A$2:$A$10,$C2)=0
Here are the uniques I've got:
Example 2. Find and highlight duplicates in two columns in Google Sheets
You can colour common values after slight modifications in both formulas from the previous example. Just make the formula count everything greater than zero.
Color dupes between columns in A only: =COUNTIF($C$2:$C$9,$A2)>0
Color dupes between columns in C only: =COUNTIF($A$2:$A$10,$C2)>0
Tip. Find many more formula examples to highlight duplicates in Google Sheets in this tutorial.
3 quickest ways to match columns and highlight records
Conditional formatting can be tricky sometimes: you may accidentally create a few rules over the same range or apply colors manually over cells with rules. Also, you have to keep an eye on all ranges: the ones you highlight via rules and those you use in the rules themselves. All of these may confuse you a lot if you're not prepared and not sure where to look for the problem.
Luckily, our Compare Sheets collection for Google Sheets has 3 user-friendly solutions for you.
Video: What is Compare Sheets collection
Add-on to compare & highlight duplicates or uniques
Compare sheets for duplicates is intuitive enough to help you match different tables within one file or two separate files, and highlight those uniques or dupes that may sneak into your data.
Here's how I highlighted duplicates between just two tables based on Fruit and MSRP columns using the tool:
I can also save these settings into a reusable scenario. If the records update, I will call for this scenario in just a click and the add-on will immediately start processing all the data. Thus, I avoid tweaking all those settings over the add-on steps repeatedly. You will see how scenarios work in the example above and in this tutorial.
Add-on to compare Google sheets and highlight differences
Compare sheets cell by cell doesn't fall behind. It sees all differences between two columns or sheets. In fact, it compares as many sheets as you need, even from different files. Usually, one of these tables acts as your main one, and you compare it with others. The add-on highlights differences on those other sheets so you could spot them instantly:
Video: How to use Compare Sheets Cell by Cell add-on
This help page and the demo video below will give you a better idea of how it compares multiple Google sheets for differences:
Compare two columns and color dupes/uniques
This last tool comes in especially helpful for a simpler task: comparing just two columns within one Google tab. Why especially helpful? Because since both columns are on one sheet, going over 5 steps is too much. Hence, there's only one step with all the necessary settings:
This tutorial discusses every option in detail if you'd like to take a look.
And guess what? This tool is also part of the Compare Sheets collection from the Google Workspace Marketplace.
That's right: you get all 3 tools with just one add-on. Give it a go and you won't regret it! (And if you do, let me know why in the comments section!)
Anyways, all these methods are now at your disposal – experiment with them, modify and apply them to your data. If none of the suggestions help your particular task, feel free to discuss your case in the comments down below.
217 comments
Hi,
I'm hoping you can help me, I'm not sure what function to use (or if it's even possible). I have a long list of ingredients condensed in one cell separated by commas and I need to verify it against a list of other ingredients (individually listed in a column). How can I go about this ?
Hi Ariane,
Try this formula:
=ArrayFormula(IF(ISERROR(VLOOKUP(TRANSPOSE(SPLIT(A1,",")),$B$1:$B$20,1,0))=FALSE,"Duplicate",""))
Hi,
I need to change the colour of the name in a list on sheet one to orange if it appears in list 2 (Sheet 2) and Blue if it appears on list 3 (also on sheet 2). What is the best way to do this?
I also have multiple lists on sheet 1 that will need to do the same, still based on the content of sheet 2.
Thanks
Tom
Hi Tom,
I'd suggest looking at 'Example 2. Find and highlight duplicates in two columns in Google Sheets' in this section of the blog post or using a special tool described here to speed up the process.
Thank you so much!
Hi Mam,
I have a sheet of my school lab testing reports. There are many samples in that sheet with their properties like Density mass and volume. These samples have their standard properties .
for eg.
Name Density Volume Mass
Sample A 5 24 9
Sample B 6 35 10
and the standard range of these samples are Sample A Density- 4 Volume- 25 Mass- 9
Sample B Density- 6 Volume- 37 Mass- 9
So i want to add a formatting based upon the product if Sample b has greater values above its standard it will be automatically highlighted .
so after that formatting according to me in sample A 5 will be highlighted (because its above standard)
and in sample B Volume 36 and mass 9 will be highlighted. I think you understood.
Hi Deepak,
If I understand you correctly, you need to create a conditional formatting rule that will check if values in your sample sheet are greater than particular records on a sheet with the standard numbers. A similar case with the examples of the rules is covered in this blog post.
sorry to say but my case is different......
I have 4 columns like Name Density Volume Mass
all sample have their different results and but I want to format if the the sample properties values highlights if the properties are more with standard.
Now tell me how to add formatting....means first i want to add standard value for the sample names and highlights if they are above limit with respect to standard
For me to be able to help you better, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets:
I kindly ask you to shorten the tables to 10-20 rows. If you have confidential information there, you can replace it with some irrelevant data, just keep the format.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment. I'll look into it and try to help.
shared just now please check
I've looked into your file and solved the task with conditional formatting. I used a custom formula with a VLOOKUP function as a rule:
=VLOOKUP($B2,B2:E10,2,0)>VLOOKUP($B2,INDIRECT("Standard!A1:D7"),2,0)
Dear Mam,
Its not working, whenever i change the value in standard increase or decrease there is no automatically change...also whenever i do the same in the results of product tab there also it does not work
Dear Mam,
I am thankful for the response but its working only for the Mass row...and also whenever the standard changing its not getting changed automatically
I'm sorry, I forgot to mention that I applied the rule to the 'Mass' column only. You just needed to create the same rule for 'Density' and 'Volume' columns. I've just copied the formula for you into those columns as well, please take a look.
Thank you for helping.
=VLOOKUP($B2,B2:E10,2,0)>VLOOKUP($B2,INDIRECT("Standard!A1:D7"),2,0)
will you please tell me what is 2,0 here?
Also please tell me the meaning of each function here.
You're most welcome, Deepak.
You will find the detailed description of the VLOOKUP function and its arguments in this blog post:
Google Sheets VLOOKUP with examples
As for INDIRECT, it's used to reference other sheets in conditional formatting. It is explained here:
Google Sheets and conditional formatting based on another cell text
What an amazing article! But unfortunately I just can't get your instructions to Highlight duplicates in two sheets to work. I have two sheets, one called USD and the second one CNA. On column A of each of them I have a set of dates. I'm trying to have the dates that are duplicates when comparing both sheets to be highlighted on the USD sheet. For that I did the following:
-Selected column A on the sheet called USD
-Conditioning format ranged from A2:A999
- Custom formula =A2=INDIRECT("CNA!A2:A")
Unfortunately it does not work, non of duplicated dates across sheet USD and CNA is highlighted. What am I missing here?
Thank you for you time!
I think I got it, conditioning format on sheet USD and the formula bellow did the trick. Thank you again for the article.
=COUNTIF(INDIRECT("CNA!A1:A");$A1)=1
Thank you for your feedback, Luiz! The way with the INDIRECT that didn't work looks for duplicate records on the same row. If your task is to check for duplicates regardless of their position in a column, you did the right choice with the COUNTIF :)
How would you get that formula to ignore empty cells. I am using it for conditional formatting, it works, it highlights what I want but also the empty cells.
=COUNTIF(INDIRECT("CNA!A1:A");$A1)=0
Hello Sam,
For me to be able to help you better, please consider sharing an editable copy of your spreadsheet and a rule with us: support@apps4gs.com
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
I'll do my best to help.
Just wanted to say thank you so much for this article! It really helped me with a problem at work ?
Thank you for your feedback, Jasmine! Glad to know the info is helpful :)
Actually I need your help. I am trying to fetch data from one sheet to another by comparing name on both the Sheets with the name.
1. I want to get the row where the name "Syed Faizan Ali" and also the name is case insensitive . there is no matter the name is exist in wherever column.
2. Then after fetching the row where "syed faizan ali" case insensitive exist. I want to compare the details from my another sheet with this sheet. And get the matched data and also want to get unmatched data.
please help. I tried many formulas. But I failed.
Hello Amin,
I've got your spreadsheets and will look into them as soon as possible. Thank you for understanding.
Hello Amin,
If I understand your task correctly, for its first part you can use one of the methods described here: Pull matching data. However, since you have lots of small tables on each sheet and the name appears in a different column in each table, you will have to create separate formulas for each table because you need to tell the function where to look for that name.
The same goes for comparing. You can use the ways described here: Compare two columns or sheets. But again, you will have to compare each pair of tables separately since all tables have different structures.
How to Highlight Duplicates from Two or Three Sheets?
Hello Niyas,
Just mention the required sheets in the formula you're using to compare & highlight cells.
Hi,
I'd like a formula to intersect 2 columns.
For instance, if I have A B C D E in column A and K E T A M R G in Column B, I'd like A E in column C
Thanks for your answer
Hi Christophe,
I'm sorry but your task is not clear. For me to be able to help you, please share a small sample spreadsheet with us (support@apps4gs.com) with 2 sheets: (1) a copy of your source data (2) the result you expect to get (the result sheet is of great importance and often gives us a better understanding than any text description). I kindly ask you to shorten the tables to 10-20 rows.
Note. We keep that Google account for file sharing only and don't monitor its Inbox. Please do not email there. Once you share the file, just confirm by replying to this comment.
Thank you for this!! I needed to use conditional formatting to match two columns, could not figure it out for the life of me, and your explanation has made it so simple :)
Glad to know it helped, Kate! You're most welcome!
hi there
i want to know the exact formula to find similar data in "one" column
mean find similar cells in just one column not 2 or more
just one
we have any formula for this?
Hi Atousa,
You can either identify duplicates without the 1st occurrences using a formula like this:
=IF(COUNTIF($A$2:$A2, $A2)>1, "Duplicate", "")
or count duplicates by the example of this article for Excel.
Hello there! This is a great reference, but I'm having issues comparing lists of differing lengths. I have one list that is 400 records, the other list is 2600 records. For whatever reason, I get false positives (or negatives depending on how I run the formula). Is there a way to construct the formula to account for such vastly different list lengths that won't return false pos/negs?
Hello Isaac,
Well, you could incorporate IF and compare only non-empty cells. But that actually depends on the way you chose to compare your lists. Can you please give examples of the contents of your lists and specify the formula you're using for comparison?
Hi Natalia,
How about if you compare values on different columns?
Hi Windy,
All points of this blog post contain examples of comparing 2 columns :)
How can I check duplicate across all sheets? Let say I got 5 different sheets. Thank you
Hello Maricel,
I'm afraid you will have to create formulas to compare each sheet against all other sheets. You'll be able to do that much faster with our add-on.
You can also use Scripts instead. But since we don't cover the programming area, you may try to find a solution here – an overview of Google Apps Script with a lot of helpful content and links.
Or you can combine all tables into one first and then look for dupes/uniques within a sheet.
I want to compare 2 google sheets i.e.one is old sheet and the other is new sheet and having huge amount of data . I want to highlight those values/rows that are modified and deleted in the new sheet by comparing with old sheet.
What formula can I use in conditional formatting to highlight it in the new sheet itself?
Hello Manisha,
If I understand you correctly, it looks like you need to highlight unique values remaining in the old sheet (that are no longer in the new sheet). Here's a formula for you to try on the old sheet:
=COUNTIF(INDIRECT("New sheet!$A$2:$A$10"),$A2)=0
Thanks so much for the quick answers.
I've put the following code in column "c"
=INDEX(E2:E17400 , MATCH(M2&"*", M2:M4901 , 0))
I get this error
Parser error in formula.
The ranges are correct though..
Quinten,
Check if you use the correct delimiters required by your spreadsheet locale. We explained this here: Wrong delimiters
The Excel logic in the link also doesn't solve the issue.
Because it is all based on the fact that the matching queries are on the same row.
I'm searching for matching queries in all the rows of a column.
I'm stuck on how to fix this problem...
Hi Quinten,
Thank you for the confirmation. If you're going to use formulas, you can't build them in column A while processing column A. You'll have to use a helper column and put formulas there.
=INDEX(A2:A7,MATCH(B2&"*", B2:B7, 0))
Hope this helps!
Hi,
I have thousands of data in 2 columns
First one is ordered by export and the other by latest activity. The export columns has more queries than the latest activity. (can't change the export to the latest activity)
I stuck in finding a way to get all the email addresses from the latest activity and check where they are in the export list.
***And important sidenote: the latest activity list is incomplete at the end of each query. As the example below.***
name1@mail.country name3@mail.c
name2@mail.country name4@mail.coun
name3@mail.country name6
name4@mail.country name1@ma
name6@mail.country name5@mail.country
name5@mail.country name2@ma
I need to **find** and **keep** all the duplicates and **remove** all the others
Is this something I can do in Excel automated instead of manually finding each one by `cmd+f`.
I have about 5000 active users I need to filter out.
*fingerscrossed*
Hi Quinten,
This blog post is devoted to solutions in Google Sheets. If you work in Excel, since the platforms are different, I'd advise you to look through the corresponding article about Excel, and, if the solution is not there, post your question under that blog post: Compare two columns in Excel for matches and differences
Sorry for the confusion! I work in Sheets.
I automatically typed Excel out of common habit.
If I have have a list of 8 digit numbers in lets say column A1:A50 and i have a similar (but slightly different) list in column B1:B50, how can i check if the list of numbers in A1:A50 match/occur with B1:B50?
Als i am using google drive Excel for this in Dutch
Hello Ash,
The ways described in this blog post work with complete matches only. As for your records, could you please give me an example of numbers from column A and their similar values from column B?
Thank you for your reply, here is an example:
A B
12178715 12178841
12178784 12179559
12182831 12181891
12182835 12182921
12182843 12183293
12182908 12183535
12183449 12183859
12183486 12183899
12183508 12184062
12183871 12184377
12184246 12184884
12184345 12184908
12184681 12184955
12185347 12184959
12185363 12185770
12185454 12186686
12185471 12186958
12185627 12188055
12185671 12188133
12185719 12188145
12185760 12188219
Thank you, Ash.
And sorry for the late reply.
I can see that only 4-5 first characters are the same between these columns. So how would you like them to match? For example, which one from column B should match the first number in column A?
Hello!
I wanted to match 1 col with 1 item and place value from other Col into specified col.
So I was able to do that with =SUMIF(A1:A100,C1,B1:B100)
But now when I change col A to Dropdown list items and using this formula its giving completely wrong values.
Anyone know any specific formula for doing this with dropdown values?
Hello Shujaat,
A drop-down is a single cell with only one record available at a time. The formula can only look at whatever record is currently selected from the drop-down. Thus, if you want to test all 100 cells in column A against the SUMIF criteria, I'm afraid the drop-down is not an option.