How to Use Google Sheets to Compare Two Columns

Wondering how to use Google Sheets to compare two columns? I love how easy Google Sheets makes streamlining data, especially when I remember how complicated manual tasks used to be!

With the right formulas, Google Sheets automates the process, returning results almost instantaneously. My tutorial will show you how to compare two columns in Google Sheets with various methods.

How to Compare Two Cells for Matching Data

To ease you into things, the simplest comparison is checking whether two cells have the same value.

The formula I’ve used for this comparison is: “=cell1=cell2”

This formula will return “TRUE” if the cells have the same data and “FALSE” if they don’t. To check whether two cells are equal in Google Sheets:

1. Select the cell you want the results to appear in (yellow arrow).
2. Enter the formula (blue arrow):
   

• =first cell identifier 
• =second cell identifier.
  

In this case, I’m comparing cell A2 with cell B2. This formula would be “=A2=B2”.

Note: You can also try to use the alternative function ISTEXT.

The Easiest Way to Compare Two Columns for Matches Across Rows

Let’s take things to the next level: comparing two columns for matches across entire rows.

We’ll use the same “=cell1=cell2” formula as last time, but we’ll use 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.

To compare cells for a match across rows:

1. Select the top cell of the column you want the results to appear in (yellow arrow). In the example case, I’ve used C2.
2. Enter the formula identified by the blue arrow:
   

• =first cell identifier 
• =second cell identifier. 

In this case, I’m comparing cells in the same row. Use A2 and B2 to make the formula “=A2=B2”.

3. Drag the square in the bottom-right corner of the comparison cell down to the last row you’re comparing. In this example, I want to compare rows through row 6. This will apply the formula to compare the cell in the first column with the second column in each row.

Comparing and Highlighting Matching Rows with Conditional Formatting

To highlight matching rows in Google Sheets using conditional formatting, you can follow these steps:

1. Select the range by highlighting the cells to which you’ll apply conditional formatting. In this case, you’d highlight Column A and Column B.
2. In the drop-down menu, select Format > Conditional Formatting to set up a conditional formatting rule.
3. In the conditional formatting window, select “Format cells if” dropdown > “Custom formula is.” 
4. Insert the following formula: =$A1=$B1. This will check whether cell A1’s value is equal to cell B1’s. You can make any cell adjustments to suit your requirements. 
5. Next, select any sort of formatting options you prefer, such as fill color, text color, etc.
6. Click “Done.” You should now see highlighted rows where numerical values in Column A and B match. You may need to adjust the formula and cell references to suit your aims.

Returning a Pre-Defined Value for Matches & Differences Between Two Columns

Using “TRUE” and “FALSE” for matches makes sense to the person who set up the formula.

However, it doesn’t provide context to people who don’t understand that formula. Instead of returning “TRUE” or “FALSE”, you can modify your formula to return different text

=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”)

To compare cells for a match across rows with redefined “TRUE” and “FALSE” values:

1. Select the top cell in the comparison column. In this example, I’ve chosen Column C (yellow arrow).
2. Enter the =IF(Cell1=Cell1,”True Replacement Text”,”False Replacement Text”) as the cell formula (blue arrow). In this example, I used =IF(A2=B2,”Same”,”Different”).
3. Select the square in the bottom-right corner of the comparison cell and drag down to the last row you’re comparing (black arrow).

The “Compare” column should now show whether the values for columns “A” and “B” are the same or different.

How to Make Conditional Comparisons Across Two Columns

When you compare two columns in Google Sheets, the results aren’t limited to “same” or “different.” You can also apply a conditional statement to compare Sheets cell by cell.

These comparisons can get fairly complex, so I’m using a simple formula: 

=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″)

To compare cells across rows with predefined conditional-response values:

1. Select the top cell in the comparison column (yellow arrow).
2. Enter the =IF(Conditional Statement (<, >, =, etc),”Cell 1 True Text”,”Cell 2 True Text”) as the cell formula (blue arrow). For this example, I’ve used =IF(B2<C2,”Shop 1″,”Shop 2″).
3. Drag down from 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 you which shop has the lower price on a given product.

A Note About Less Than vs. Equal or Less Than Conditions

Since I’ve used the < (i.e., “Less Than”) condition, “Pears” came back as cheaper at Shop 2, even though both shops have the same price. If I used <= (i.e, “Equal or Less Than”), the formula would have returned Shop 1.

Comparing Columns to Search for Missing Data in Google Sheets

Google Sheets can compare columns to see whether a selected cell in one column appears in any other column. This formula is extremely useful for finding missing information:

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

For this example, I’m using the following formula:

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

Note: This formula might seem confusing because I want to return the response “No” when the formula is true.

1. Select the top cell in the comparison column (yellow arrow).
2. Enter the formula (blue arrow). In the example. I’m trying to see whether the Shop 1 product is available at Shop 2, so I’ve used:  =IF(COUNTIF($C:$C, $A2)=0, “No”, “Yes”) to compare the selected A-column cell with all of column C. 
   1. The =0 expression means the product is not found. The result will return “No” if this is true and “Yes” if false.
3. Select the square in the bottom-right corner of the comparison cell and drag down to the last row you’re comparing (black arrow).

In this example, because Shop 2 doesn’t carry oranges, the result will return “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.

Related: How to use COUNTIF function in Google Sheets.

Comparing Each Row in One Column for a Match in Another Column

Rather than looking for what’s missing, you can use Google Sheets to see what values exist in both columns. For example, you can compare two stores’ inventory to develop a list of products that are available at both:

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

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

In this scenario, I’m using the following formula:

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

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

To search columns for matching data:

1. Select the top cell in the comparison column (yellow arrow).
2. Enter the formula (blue arrow). In the example, I’m using =VLOOKUP(A2, $B$2:$B$5, 1, FALSE) to compare the selected Column A cell against all of Column B’s values. A value will be returned if there’s a match in column C.
3. Select the square in the bottom-right corner of the comparison cell and drag down to the last row you’re comparing (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 dataset.

Building a List of Duplicate Values Between Two Columns

When sorting data in Google Sheets, 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)

In my example, I’m using this formula:

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

This formula will check all of column B for a match in the selected column A cell. We’re using “>0” (i.e., greater than zero) as the condition. If true, we’ll return to the selected column A cell value. If a duplicate isn’t found, the formula returns a blank value. In this example, I’m sorting my kitchen tools between two shelves and am trying to locate duplicates. 

To search columns for duplicate data:

1. Select the top cell in the comparison column (yellow arrow).
2. Enter the formula. In this example, I’ve used =IF(COUNTIF($B:$B, $A2)>0, $A2,””).
3. Select the square in the bottom-right corner of the comparison cell and drag down to the last row you’re comparing (black arrow).

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

Compare Columns in Google Sheets to Find Highest & Lowest Values

Google Sheets can identify the highest and lowest values across two columns on the same sheet. We do so by using the functions MAX and MIN. 

To find the greatest value:

1. Select the cell you want to show the results (yellow arrow).
2. Enter the formula =max(range start:range end) in the cell (blue arrow). In the example case, I’ve used the range B2:C5.

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

Productivity Spot for All of Your Spreadsheet Needs

These are just a handful of ways to compare data across two columns in Google Sheets. You can modify these techniques to do incredibly powerful data comparisons, like sorting a record collection or analyzing a business’ product lineup. 

To build your understanding of Google Workspace, why not check out Udemy’s range of courses? With thousands of online seminars, there are endless ways to build your skills!