A Simple Google Sheets Compare Two Columns Guide for 2023

To err is human, and we all know that. When working with large amounts of data, the human eye is not very capable of identifying small differences or similarities between data items.

Thankfully, data processing software such as Google Sheets can pick up details that even the keenest human eye might miss.

As such, tasks like comparing columns, finding differences, and pointing out similarities get done easily, at lightning speeds, and without errors.

Read this Google Sheets compare two columns tutorial to learn everything you need to know.

For each of the above topics, we will be using the following data sample, consisting of two columns – ingredients for Cake, and ingredients for Cookies:

Dataset to compare two columns in Google sheets

Also, note that in this case, we are comparing columns that are in the same worksheet. But you can as well use the same techniques to compare two columns that are in different Google Sheets documents. To do this you will have to use the IMPORTRANGE function to be able to refer to the columns in another sheet.

How to Compare Two Columns for Exact Row Matches

The simplest way to compare two columns is to find exact row matches. All this calls for is just a row-by-row comparison.

Google sheets just need to see which rows have the same values and which ones have different values and display the result in a third, blank column.

In our example, we are simply going to create a new column for the results, and display a TRUE if the pair of items in the same row match and a FALSE if they don’t.

The formula you would use for a comparison like this is:

=A2=B2

The above formula compares data in row 2 of both columns to see if they match. If they do, then you get a TRUE result. If not, you get a FALSE result.

Here are the steps to follow:

  1. In the second row of column C (in cell C2), insert the formula: =A2=B2
  2. Press the return key.
  3. Double click the fill handle to copy the same formula to the rest of the cells in column C.

You should now see the results of each comparison in column C.

Row by Row comparison of two columns

Compare Two Columns and Show Meaningful Text

The above method works great if you’re the only one who will be working with the data.

But, a third-person going through your worksheet might not understand what exactly you’re implying by the words “TRUE” and “FALSE”.

So it makes sense to give a more descriptive result in column C. For example, you could use the word “Matching” for row items that match and “Not matching” for row items that don’t match.

If you want to get a more descriptive result, you can use a simple IF formula to return the text “Match” when the values are the same and “Mismatch” when the values are different.

For this, assuming you were working in cell C2 of our example, you would use the following formula:

=IF(A2=B2,"Matching","Not Matching")

The above formula uses an IF function to compare values in A2 and B2. If the condition “A2=B2” is TRUE, it returns the text “Matching”. If not, it returns the text FALSE.

Here are the steps to follow:

  1. In the second row of column C (in cell C2), insert the formula: =IF(A2=B2,”Matching”,”Not Matching”)
  2. Press the return key.
  3. Double click the fill handle to copy the same formula to the rest of the cells in column C.

You should now see the results of each comparison as “Matching” or “Not Matching” in column C.

Getting more descriptive text when comparing columns

Compare Two Columns and Show Meaningful Text From Numerical Values

Just like in the above example, you could use an IF statement on numerical data. You could use any operator or formula you’d like to show results for as a logical expression. For example, you could use a formula like:

=IF(C2<B2,"Yes","No")

To compare if the data in C2 is less than the numbers in B2.

In the below example, we used this formula to compare whether a new supermarket is cheaper than an older one.

Using If statements to compare numerical data in columns

Compare Two Columns and Highlight Matching Rows

If instead of displaying results in a separate column, you would rather have rows with matching data highlighted, then you can perform Conditional Formatting.

Conditional formatting is a great technique that lets you format cells based on a condition.

Here’s how you can use Conditional formatting to highlight matching rows in Google Sheets:

  1. Click the Format menu from the menu bar.
  2. Select the ‘Conditional Formatting’ option.Click on Conditional formatting
  3. This will open the ‘Conditional format rules’ sidebar on the right of the window.Conditional formatting rules pane
  4. In the input box under “Apply to range”, type in the range of cells you want to compare. In our example, we can type A2:B12.Input the range for conditional formatting
  5. Next, in the Format rules section, under “Format cells if”, click on the dropdown arrow.Click on Format cells if option
  6. From the dropdown menu that appears, select “Custom formula is”.Select Custom Formula is
  7. You will see an input box below the dropdown list. Type your custom formula there: “=$A2=$B2”.Conditional formatting formula by row by row comparison
  8. Under “Formatting style”, click on the Fill Color button.Click the fill color button
  9. Select the color you want to use, in order to highlight matching cells/rows. We selected “light cornflower blue 1”.Select the color for cell formatting
  10. Finally click the Done button, to let Conditional formatting do its work.Click on done

You should now see all matching cells/rows highlighted in your selected color.

Matching rows being highlighted

Note: In case you want to do the opposite, i.e. to highlight only the cells/rows that don’t match, change the formula in step 7 to: =$A2<>$B2

Find differences in two columns formula

Using Google Sheets Conditional Formatting to Compare Two Columns and Find Matching Data

So far, the techniques we discussed mainly found out if cells in the same row were matching.

But what if you want to compare two columns and find out which values of column A are repeating in column B, irrespective of which row it’s in?

That would make more sense for our sample data too wouldn’t it? You might want to know which ingredients of a cake are also used in baking cookies.

Here’s how you can use Conditional formatting to highlight duplicate data:

  1. Click the Format menu from the menu bar.
  2. Select the ‘Conditional Formatting’ option.
  3. This will open the ‘Conditional format rules’ sidebar on the right of the window.
  4. In the input box under “Apply to range”, type in the range of cells you want to apply the formatting on. In our example, we want to see the highlights of column B only. So we type: B2:B12.Specify the range
  5. Next, in the Format rules section, under “Format cells if”, click on the dropdown arrow.
  6. From the dropdown menu that appears, select “Custom formula is”.
  7. You will see an input box below the dropdown list. Type your custom formula there: =COUNTIF($A$2:$A$9,B2)>0Formula to find duplicate data
  8. Under “Formatting style”, click on the Fill Color button ( ).
  9. Select the color you want to use, in order to highlight matching cells/rows. We selected “light cornflower blue 1”.
  10. Finally click the Done button, to let Conditional formatting do its work.

You should now see all cells (ingredients) of column B (cookies) that are also present in column A (cake) highlighted in your selected color.

dataset where matching data is highlighted

Explanation of the Formula

In this problem, we wanted to know which ingredients of column B also had an occurrence in column A.

In other words, we wanted to highlight only those items of column B that have 1 or more occurrences in cells A2 to A9.

In the formula:

=COUNTIF($A$2:$A$9,B2)>0

The COUNTIF function counts the number of times the value in B2 occurs in the cell range A2:A9.

If this count is more than 0, it means a duplicate of the B2 value exists in column A. It could exist once, twice or more times. In either case, as long as the count is more than 0, the function returns a TRUE.

When the function returns a TRUE value, our conditional formatting gets to work and highlights cell B2.

This is repeated for all cells starting from B2 to B12.

Finding the Google Sheets Difference Between Two Columns

Now, what happens when you want to do the opposite? What if you want to find the ingredients of Cookies that are not present in the Cake? In this case, you would only have to make a slight change to the formula used in step 7 of the previous method.

The Custom formula used in the Conditional format rule sidebar should now be:

=COUNTIF($A$2:$A$9,B2)=0

Formula to find missing data

This is because you now want to see if the element of column B has 0 occurrences in column A.

This will give you the result as shown below:

Dataset where difference in data is highlighted

These were some of the ways in which you can use Google Sheets to compare two columns. We used a smaller sample to help you understand the concepts.

But the methods discussed in this tutorial can be really powerful when used with larger datasets, especially if you’re using it for analytical processing.

We encourage you to apply and experiment with these methods and hope you found the explanations helpful.

Compare Columns Using a VLOOKUP Formula

You can also compare columns in Google Sheets using the VLOOKUP function which operates with the following syntax:

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

To use this formula, you’d need to specify a cell to compare as the search_key, the column range you want to search for matches in, and the index column number in the range ie f your range includes columns A, B, and C and you want the data from B to be the used as the results you’d type 2 as it’s the second column. Then you can define whether you want an exact or approximate comparison by using TRUE or FALSE operators. FALSE means that the data must be an exact match

In the example below we used:

  • B2 as the search_key
  • $C$2:$C$5 as the comparison_column_range
  • 1 as the index
  • And FALSE so we only return exact matches

Using VLOOKUP to compare columns in Google Sheets

Notice that the values that don’t match return an #N/A error as there are no available matches in the specified range.

How to Compare Two Columns In Google Sheets Using MATCH

The MATCH function works similarly to VLOOKUP. The difference is that the results will show the row position of the match in the array_column. The syntax for the MATCH function is:

=MATCH(lookup_value, lookup_array, [match_type])
  • lookup_value is the value you want to compare
  • lookup_array is the array to search for matches
  • match_type indicates whether or not to use exact matches. Type 0 for exact matches or 1 for partial matches.

In the below example we are pretending to compare two children’s favorite animals with the following formula:

=MATCH(A1,$B$1:$B$5,0)

Comparing columns with the MATCH function in Googel Sheets

As you can see instead of showing TRUE for matches, it gives a numerical value depending on where it is in the range. For example, C1 says 5 as the value as Elephants is the fifth value in the range of column A.

Google Sheets Compare Two Columns FAQ

How Do I Compare Two Columns In Google Sheets?

There are quite a few functions that can compare columns in Google Sheets. The most sensible ways include:

  • VLOOKUP
  • COUNTIF
  • IF
  • MATCH
  • Conditional Formatting

How Do I Compare Two Columns in Google Sheets and Highlight Duplicates? / Can Google Sheets Compare Two Columns With Conditional Formatting?

You can highlight duplicates in columns on Google Sheets by using conditional formatting. To do so:

  1. Highlight the columns you want to compare
  2. Navigate to Formatting > Conditional formatting
  3. Set the Format cells menu item to Custom formula is
  4. Use absolute references for the column names and use the =sign between the first two rows of the columns you want to compare. For example, to compare columns A and B from row 2 down, you could type =$A2=$B2
  5. Click Done

How Do I Use the MATCH Function in Google Sheets?

Google Sheets can match two columns to find values in columns that have the same or similar values. The syntax is:

=MATCH(lookup_value, lookup_array, [match_type])

You add the value or cell reference you’re looking to match as the lookup_value, use absolute references to define the lookup_array (where to search), and type 0 to search for exact matches or 1 to look for partial matches. For example, to search if the value in C1 is in column D between rows 5 and 10, you could use the following formula:

=MATCH(C1,$D$5:$D$10, 0)

Wrapping Up Comparing Columns In Google Sheets

As you can see, there are a ton of different ways that you can compare your data across columns in Google Sheets. If you found this Google Sheets compare two columns guide useful, there’s plenty more to learn.

Check out some of our related guides here:

Most Popular Posts

Sumit

Sumit

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.

9 thoughts on “A Simple Google Sheets Compare Two Columns Guide for 2023”

  1. Looking for a formula that would highlight duplicates. Specifically, I would need to compare the cells of an entire column (N) against all cells in the previous columns (A-M) and only highlight the duplicates from column N and the sister cells (if possible). I do not want the formula to compare columns A-M against each other for duplicates. I was using this formula, but found that each cell is being compared to all other cells and highlighting all dupes. Taking too much time to process. Any help would be awesome! Thanks!

    =COUNTIF($A$2:N6000,Indirect(Address(Row(),Column(),)))>1

    Reply
  2. This shows how to format the cells but how do we get the number of rows matching and how do we then determine how closely matched the two data sets are?

    Reply
  3. Hi! I’m trying to create a color coded google sheet in order to track student grades.
    If I had to describe the google sheet basic set up, students names are in the first column, next column has score (0-50), and then the second column has the following week’s scores, again (0-50).

    I used your formula for comparing 2 cells on the same row, but different columns, and modified it to color a cell green if the value in it is greater than the value in the cell before it on the same row (so from =$A2=$B2 to =$A2>$B2). I then added 2 other conditional format formulas, to show a different color that cell is lower than the adjacent cell before it (=$A2$B2 on row 5 (=$A2>$B2 instead of =$A5>$B5).

    Is there any extra step, or change I can make to the formula so that it populates correctly for each row, without me having to write 3 different formulas for every single cell?

    Thanks for your help!

    Reply
  4. Hi, what if I want to use Google Sheets Conditional Formatting to compare columns in two different sheet, what will the =countif formula be? Thank you.

    Reply

Leave a Comment

Related Posts

Disclosure: Spreadsheet Point is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.