How to Compare Two Columns in Google Sheets

Google Sheets has an expansive set of features that offer a streamlined, fast way to compare data across columns.

Tasks like manually trying to find matching cells, pointing out differences, and identifying duplicates can take a very long time.

However, with the right formulas, Google Sheets can automate the process and return results almost instantaneously.

This tutorial covers how to compare two columns in Google Sheets in many different useful ways.

This Article Covers:
How to Compare Two Cells for Matching Data
How to Compare Two Columns for Row Matches
How to Return a Pre-defined Value for Matches and Differences
How to Make a Conditional Comparison across Two Columns
How to Compare Columns to Search for Missing Data
How to Compare Each Row in One Column for a Match in Another Column
How to build a list of duplicates between two columns
Compare two columns to find the highest and lowest values
Sumit
You May Also Like the Following Tutorials:
How to Compare Two Cells for Matching Data
One of the simplest comparisons you can do in Google Sheets is to check if two cells have the same value.

The formula for this comparison is: “=cell1=cell2”

This formula will return “TRUE” if the cells have the same data and “FALSE” if they do not.

Below are the steps to compare two cells for a match:

Select the cell you want the results to appear in (yellow arrow).
Enter the formula “=” first cell identifier “=” second cell identifier (blue arrow). In this case, we are comparing cell A2 with cell B2 so our formula is “=A2=B2”.
Comparing two cells with a simple formula
That’s it!

How to Compare Two Columns for Row Matches
Let’s take comparing two cell values to a more powerful level: comparing two columns for matches across rows.

We’ll use the same “=cell1=cell2” formula as last time, but we’ll make sure we’re using two columns in the same row.

Again, this formula will return “TRUE” if the cells have the same data and “FALSE” if they do not.

Below are the steps to compare cells for a match across rows:

Select the top cell of the column you want the results to appear in (yellow arrow). In the example case, we want to use C2.
Enter the formula “=” first cell identifier “=” second cell identifier (blue arrow). In this case, we are comparing cells in the same row. Use A2 and B2 to make the formula “=A2=B2”.Formula to compare two cells in the same row
Drag the square in the bottom right corner of the comparison cell down to the last row you want to compare, in the example case we want to compare down to row 6.Fromula on entire columns comapring cells in two columns
This will apply the formula to compare the cell in the first column with the second column in each row.

How to Return a Pre-defined Value for Matches and Differences
Using “TRUE” and “FALSE” for matches makes sense to the person who set up the formula.

However, it doesn’t provide context to someone looking at the spreadsheet without knowledge of the formula. Instead of returning “TRUE” or “FALSE”, you can modify the formula to return different text.

The formula we’ll be using here is:

=IF(Cell1=Cell2,”True Replacement Text”,”False Replacement Text”)

The formula with the chosen values “Same” for “TRUE” and “Different” for “FALSE” will look like:
=IF(A2=B2,”Same”,”Different”)

Below are the steps to compare cells for a match across rows with redefined “TRUE” and “FALSE” values:

Select the top cell in the comparison column (yellow arrow).
Enter the =IF(Cell1=Cell1,”True Replacement Text”,”False Replacement Text”) as the cell formula (blue arrow). We’re using =IF(A2=B2,”Same”,”Different”) in this example.
Drag the square in the bottom right corner of the comparison cell down to the last row you want to compare (black arrow).
Formula to compare cells and get descriptive result
The “Compare” column now states if the values for columns “A” and “B” are the same or different. Adding context to your comparisons makes your Google Sheet easier for other people to understand.

How to Make a Conditional Comparison across Two Columns
When comparing cells in Google Sheets, we’re not just limited to checking if they’re the same or different. We can also apply a conditional statement to return a useful comparison.

These comparisons can get complex, so we’ll use a simple example to learn how to do it.

The formula we’ll be using here is:

=IF(Conditional Statement (<, >, =, etc),”Cell 1 True Text”,”Cell 2 True Text”)
With the values filled in to return “Shop 1” or “Shop 2” as the less expensive option: