How to Compare Two Columns in Google Sheets (for matches & differences)

Sumit
Last updated April 22, 2022

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

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”.

That’s it!

(You can also try to use the alternative function ISTEXT.)

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”.
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.

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).

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:

=IF(B2<C2,"Shop 1","Shop 2")

Below are the steps to compare cells across rows with redefined conditional-response values:

Select the top cell in the comparison column (yellow arrow).
Enter the =IF(Conditional Statement (<, >, =, etc),”Cell 1 True Text”,”Cell 2 True Text”) as the cell formula (blue arrow). We’re using =IF(B2<C2,”Shop 1″,”Shop 2″) 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).

The “Less Expensive” column now tells us which shop has the cheaper price on the given product.

Note that since we used the “<” or “Less Than” condition, “Pears” came back as cheaper at Shop 2, even though both shops have the same price. If we used “<=” or “Equal or Less Than” the formula would have returned “Shop 1.”

How to Compare Columns to Search for Missing Data

Google Sheets can compare columns to see if a selected cell in one column appears at all in another column.

This is useful for finding missing information.

The formula we’re using here is:

=IF(COUNTIF(Compare column start : Compare column End, Selected cell)=0, "True Response", “False Response")

Within the context of our example, we’re using this formula:

=IF(COUNTIF($C:$C, $A2)=0, "No", "Yes")

Note that our formula might be a little confusing because we want to return the response “No” when the formula is true.

Below are the steps to search columns for missing data:

Select the top cell in the comparison column (yellow arrow).
Enter the formula =IF(COUNTIF(Compare column start : Compare column End, Selected cell)=0, “True Response”, “False Response”) (blue arrow). In the example. we are trying to see if the Shop 1 product is available at Shop 2 so we use =IF(COUNTIF($C:$C, $A2)=0, “No”, “Yes”) to compare the selected A-column cell with all of column C. The =0 expression means the product is not found. We return “No” if this is true and “Yes” if it is false.
Drag the square in the bottom right corner of the comparison cell down to the last row you want to compare (black arrow).

In the example, Shop 2 doesn’t carry Oranges, so this returns “No.” Shops 1 and 2 both carry Pears, but they appear in different rows. The formula will return “Yes” since the cell value at A5 exists in Column C.

For more detailed information about the COUNTIF formula check our guide on How to use COUNTIF function in Google Sheets.

How to Compare Each Row in One Column for a Match in Another Column

Instead of looking for what’s missing, you may want to use Google Sheets to see what values exist in both columns. For example, you can compare the inventory of two stores to come up with a list of products available at both stores.

The formula we’re using here is:

=VLOOKUP(Comparison Cell, Comparison Column Range, Retrieve Value, Exact or Approximate Comparison)

Note: Use “FALSE” for exact comparison and “TRUE” for approximate comparison”

Within the context of our example, we’re using this formula:

=VLOOKUP(A2, $B$2:$B$5, 1, FALSE)

We’re trying to compare the value in cell A2 with all the values in column B. If the condition of our VLOOKUP comparison comes back as an exact match (indicated by FALSE), we want to return the matched value (indicated by 1).

Related: VLOOKUP Google Sheets

Below are the steps to search columns for matching data:

Select the top cell in the comparison column (yellow arrow).
Enter the formula =VLOOKUP(Comparison Cell, Comparison Column Range, Retrieve Value, Exact or Approximate Comparison) (blue arrow). In the example, we’re using =VLOOKUP(A2, $B$2:$B$5, 1, FALSE) to compare the selected A column cell against all of column B values. We return the value if it is a match in column C.
Drag the square in the bottom right corner of the comparison cell down to the last row you want to compare (black arrow).

Note: The example color-codes the products to illustrate how they line up and which result value gets returned. #N/A is returned when the comparison cell doesn’t exist in the searched data.

How to build a list of duplicates between two columns

When sorting data into a spreadsheet, you may only want a given value to appear once between two columns. You can use Google Sheets to build a list of duplicates across columns.

The formula we’re using here is:

=IF(COUNTIF(Comparison Column, Comparison Cell)>0, Return True, Return False )

Within the context of our example, we’re using this formula:

=IF(COUNTIF($B:$B, $A2)>0, $A2,"")

Our example is checking all of column B for a match in the selected column A cell. We’re using “>0” or greater than zero as the condition. If it is true, we bring back the selected column A cell value. If it doesn’t find a duplicate, the formula returns a blank value.

Below are the steps to search columns for duplicate data:

Select the top cell in the comparison column (yellow arrow).
Enter the formula =IF(COUNTIF(Comparison Column, Comparison Cell)>0, Return True, Return False). In our example we’re using =IF(COUNTIF($B:$B, $A2)>0, $A2,””),
Drag the square in the bottom right corner of the comparison cell down to the last row you want to compare (black arrow).

Our example shows someone sorting their kitchen tools between two shelves. We’ve returned a list of items in the black ellipse appear on both shelves.

Note: The blue background highlights the location of the duplicate item across all columns.

Compare two columns to find the highest and lowest values

Google Sheets can identify the highest and lowest value across two columns on a single sheet. The functions “MAX” and “MIN” can bring back the highest and lowest values in a range respectively.

Below are the steps to find the greatest value:

Select the cell you want to show the results (yellow arrow).
Enter the formula =max(range start : range end) in the cell (blue arrow). In the example case, we’re using the range B2:C5.

If you are trying to find the lowest value in the data group, simply use =min(range start:range end) instead of =max().

These are some of the many ways you can compare data across two columns in Google Sheets.

Depending on what you need Google Sheets to do, you can modify these techniques to do incredibly powerful data comparisons.

The uses can be as simple as sorting an inventory of your record collection or as complex as analyzing a business’s product lineup.

I hope this tutorial helped you find new, useful ways to compare data in Google Sheets!

Sumit

Spreadsheet Expert at Productivity Spot | + posts

Sumit is a Google Sheets and Microsoft Excel Expert. He provides spreadsheet training to corporates and has been awarded the prestigious Excel MVP award by Microsoft for his contributions in sharing his Excel knowledge and helping people.