Google Sheet Power Tip : Compare data in two Google sheets or columns for matches and differences

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 with 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=B2

If cells match, you’ll see TRUE, otherwise FALSE. To check all cells in a column, copy the formula down to other rows:
The simplest formula in Google Sheets to compare two cells.

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=B2,"Match","Differ")
    Identify pairs of cells with the IF function.

    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,B2),"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=B2,"Match","")
  • To mark only rows with unique records between cells in two columns, take this one:=IF(A2=B2,"","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:
    Find differences between two columns with the array function.
  • 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

5 1 vote
Article Rating

About Maida
She is Zipsite's all around Zipsiter. Only clocks out when she can barely stand. She sings, bakes, and tries so hard to be fit. But don't judge, she can literally strong arm you no problem. She's currently involved with eCorp, Contrib and VNOC, besides being a Brazilian Jiu Jit Su practitioner.

Subscribe
Notify of
0 Comments
Inline Feedbacks
View all comments
ajax-loader
0
Would love your thoughts, please comment.x
()
x
%d bloggers like this: