How to Compare Two Columns in Google Sheets

When working with large data sets in Google Sheets, making comparisons among cells or columns can seem like a daunting task.

There may be times when you need to compare the values across different cells or columns to identify matches, differences, or duplicates.

With a little know-how, comparing columns in your spreadsheet is relatively easy if you use the right formulas.

In this tutorial, I will show you how to compare two columns in Google Sheets. I will cover a wide variety of scenarios where you may be comparing matches or differences among the data in your cells.

How to Compare Two Cells for Matches

One of the easiest ways to make a quick comparison of data in your cells in Google Sheets is just to use the equals sign to determine if one cell matches another cell.

The formula to do this is:

=cellreference1=cellreference2

This formula will result in either TRUE if the data in each cell matches, or FALSE if the data does not match.

Here is an example of how to do this in a spreadsheet:

This formula works for comparing two cells for matches or differences. If you copy and paste the formula down on an entire column, you can use it to compare two columns for matches or differences in each row.

Comparing Two Columns with Conditional Statements

We can also make comparisons between two different columns using conditional statements such as greater than (>), less than (<), equal to (=), etc.

One useful way of doing this is to use an IF statement to return one value if your conditional statement evaluates to TRUE and a different value if your conditional statement evaluates to FALSE.

To do that the basic syntax would be:

=IF(Conditional Statement, “Text If True”, “Text If False”)

Here is an example of how this would look in a spreadsheet:

In this example, I have a basic IF statement that compares if the value in column A is greater than the value in column B.

If A is greater, the formula returns “A is Greater”. If column B is greater the formula returns “B is Greater”.

This is just a basic example of a conditional statement that you can use to compare data between two columns, but you can this kind of comparison in much more complex ways if you wish.

VLOOKUP Formula to Compare Two Columns

Sometimes you may have data across multiple columns or sheets that you want to match together so that you can make a comparison.

One thing we can do is use the VLOOKUP function to match data from different columns together so we can make a comparison.

The syntax of the VLOOKUP function is:

=VLOOKUP(search_key, range, index, [is_sorted])

• search_key – this is the value or thing we are searching for
• range – this is the range that we will be searching in
• index – this is the column that will be returned. the first column is considered 1, 2nd column 2 etc…
• is_sorted – this is an optional argument that will be set to TRUE by default. It indicates whether the column we are searching in is sorted. It is recommend to use FALSE in most cases

Now that we have learned the basic syntax of the VLOOKUP function, let’s take a look at how it can be used to match data together from two different columns or sheets to easily make a comparison.

In this example, I have two different sheets with sales data for the same product codes at different stores.

Here is sheet 1:

and here is sheet 2:

So what I want to do now is match together the sales for each product code so I can compare the data for each store. To do that I will be using the VLOOKUP function.

Here is how this is done:

1. In the column where you want to return the matched data type and equals sign (=) and type VLOOKUP. Press tab when you are done to start entering the formula

2. Select the cell the contains your search key (the value you are searching for). Type a comma when you are done

3. Next go to the other sheet and highlight the range of data that you are searching in. The first column in the range should be the column that contains the search key. Type a comma when you are done

4. Next put in the number for the column that you want to return from your range. I want to return the 2nd column so I have placed a 2 in the formula. After you put in the column number that you want to return put a comma, and then type “FALSE”. Add your closing parenthesis and you can press ENTER to calculate the function

5. You can now copy and paste your formula down to additional rows if you need them. It will return the matched data from your lookup range

As you can see in the example above, by using the VLOOKUP function we can match data from different sheets or columns to easily make a comparison. This a very useful skill to learn when working with data that exists across multiple spreadsheets.

Compare Two Columns for Duplicates

Another useful kind of comparison that we can do for different columns in Google Sheets is comparing data for duplicate values.

In this example, I have two columns with different names, and I will use a formula to quickly tell me which values exist in both columns:

To tell me which values are duplicates, I will be using the COUNTIF function.

This formula will tell me which values from column A exist in column B.

Here is how this will look in a spreadsheet:

The formula used in this example is:

=COUNTIF($B$2:$B$9,A2)

The first argument in this formula is the range that I am searching in ($B$2:$B$9).

The second argument is the value that I am searching for (A2).

When the formula evaluates it will count how many times the value you are searching for shows up in the range you are searching in.

So in this example, everything that evaluates to a 1 is a duplicate that exists in both columns. Everything that evaluates to a zero is unique to each column.

Closing Thoughts

There are many useful methods for comparing data from different columns in Google Sheets.

To become efficient at making these kinds of comparisons, you will have to learn how to use the various functions that I have gone over in this post. Once you gain comfort with these functions you can begin making your own kinds of comparisons by combining these functions and sorting/filtering to pull out the data you need.

This makes it very efficient to compare and sort through large data sets with ease.

More Google Sheets Tutorials:
Does Not Equal
How to Use Index and Match Together
How to Get the Sheet Name
XLOOKUP Alternatives