How to compare two rows in Google Sheets

In this tutorial, you will learn how to compare two columns in Google Sheets.

It’s not uncommon when working in Google Sheets to want to compare two columns to see how they differ from each other. For example, if you exported the same data from different sources or on different dates, you might want to see what changed between exports.

It can be frustrating to try and find any differences manually, but fortunately it’s not hard to set up formulas that make it quick and easy to compare columns, and this tutorial will show you how.

Prepping the Columns

• Prepping the Columns
• Comparison Formula
• Summary

The best way to compare columns is to compare the values in each row. For example, if you’re comparing columns A and B, you’d compare A1 to B1, A2 to B2, etc. This works when the data in the two columns is in the same order, but if the two columns are sorted differently the results might be inconsistent. To get around this issue, you can first sort the data in each of your columns by following these steps:

Step 1

Select only the first column and open the Data menu and hover over Sort Range, then choose a sorting option

Step 2

Repeat Step 1 for the other comparison column

Comparison Formula

Once your columns are sorted the same, here’s how to compare them:

Step 1

Select a cell at the top of a blank column to do the comparison. In this example, we’ll exclude headers from the comparison so will start in row 2

Step 2

Type the following array formula: “=ARRAYFORMULA(IF(A2:A=B2:B, “match”, “MISMATCH”))”. IN this formula, the ranges A2:A and B2:B are the columns to compare, excluding headers, and the strings in quotation marks are the messages to display if the values for a row match or not, respectively. The IF function checks for a matching condition and chooses the message to display accordingly, and the ARRAYFORMULA function makes the formula output expand to display the calculation for all comparison rows

Step 3

Hit enter and comparison will populate for the entire column

Step 4

You can use the comparison as it is, but if you want to highlight mismatched rows to make them easier to spot, select the comparison column and open the Format menu, then choose Conditional Formatting

Step 5

Under Format Cells If, choose the Text Is Exactly option and type “MISMATCH” in the field below

Step 6

Select the desired highlight color from the Fill Color menu and click Done

Step 7

You can now more quickly skim the comparison column to find mismatches

Summary

Example Spreadsheet: Make a copy of the example spreadsheet

In this tutorial, I covered how to compare two columns in Google Sheets. Want more? Check out all the Google Sheets Tutorials.

You might also like: