A Simple Google Sheets Compare Two Columns Guide for 2024

To compare two columns in Google Sheets, use conditional formatting and the COUNTIF function. This guide shows how to compare exact row matches and full columns that contain unsorted data.

Comparing Columns for Matching Data

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 can be easily done and without errors. In the tutorial below, I will share how to use Google Sheets to compare two columns.

In the following tutorials, I will be using the data sample below, consisting of two columns — ingredients for Cake and ingredients for Cookies:

How To Compare Two Columns for Exact Row Matches

The simplest way to compare two columns is to find exact row matches, which calls for a row-by-row comparison.

As you know, Google Sheets needs to see which rows have the same values and which ones have different values. Then, it can display the result in a third, blank column.

In my example, I will create a new column for the results. If a pair of items in the same row match, it will be displayed as TRUE. Alternatively, if a pair of items within the same row do not match, then I will use FALSE.

Below is the formula I will use for comparisons:

=A2=B2

The formula compares data in Row 2 of both columns (A & B) to see if they match. If they do, column C will display a TRUE result. If not, you get a FALSE result.

To understand how this works, follow the same steps that I have used:

1. In column C (in the cell C2), enter 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.

How To Compare Two Columns and Show Meaningful Text

From my personal experience, the formula above works great if you’re the only one handling the data. However, I often encountered problems when sharing the spreadsheet and data with colleagues. That is because they did not understand what I was implying with the words “TRUE” and “FALSE.”

Therefore, in hindsight, it makes more sense to use a descriptive result in column C. For example, instead of using “True” or “False,” I can set the word to “Matching” for the row items that match and “Not matching” for row items that don’t match. This, I found, helped to clear up any misunderstandings and ensured better communication within a team.

If, like me, you prefer to have a descriptive result, you can use a simple IF formula. This means the text will display “Match” when the values are the same and “Mismatch” when the values are different.

For the example above, I have used the following formula:

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

This formula uses an IF function to compare the values in A2 and B2. If the condition “A2=B2” is TRUE, it will display the text as “Matching.” If not, it will display the text as “FALSE.”

Here is the formula and steps that I have used for the example:

1. In cell C2, enter 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 see the results of each comparison as “Matching” or “Not Matching” in column C.

How To Compare Two Columns and Show Meaningful Text From Numerical Values

As in the above example, if you want to compare two columns with numerical values and show meaningful text, you can use an IF statement on the numerical data. You could use any operator or formula you’d like to show results for as a logical expression.

For example, I can use the formula below to compare the data in C2 and B2 to determine which number is less:

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

Here, you can see that I have used this formula to compare whether supermarket one is cheaper than supermarket two.

How To Compare Two Columns and Highlight Matching Rows

Of course, there will be times when, instead of displaying results in a separate column, you would rather have rows with matching data highlighted. In such cases, I would use Conditional Formatting because it’s a great technique for formatting cells based on a specific condition.

Follow the example below to see how I have used Conditional Formatting to highlight matching rows in Google Sheets:

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 the “Apply to range” box, type in the range of cells you want to compare. In my example, I typed: A2:B12.
5. Next, in the “Format rules“ section, under “Format cells if,” click on the drop-down arrow.
6. From the drop-down menu, select “Custom formula is.”
7. You will see an input box below the drop-down list.
   • Type your custom formula, such as: “=$A2=$B2”.
8. Under “Formatting style,” click the “Fill Color“ button.
9. Select the color you want to highlight the matching cells/rows. My favorite color is blue, so I selected “light cornflower blue 1.”
10. Finally, click the “Done” button to let “Conditional formatting” do its work.

If you follow these steps, you will see all matching cells/rows are highlighted in your selected color, like my example below.

Note: If you want to do the opposite, i.e., highlight the cells/rows that don’t match, change the formula in step 7 to:

=$A2<>$B2

How to Compare Two Columns without Sorting

So far, I have shown you the techniques to find out if cells in the same row are a match. But what would I do if I wanted 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 my sample data, too, wouldn’t it? For instance, I might want to know which cake ingredients are also used when baking cookies.

Unsorted Column Comparison Video

Here’s a video that quickly shows the process I use when comparing two columns in Google Sheets:

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

So far, I have shown you the techniques to find out if cells in the same row are a match. But what would I do if I wanted 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 my sample data, too, wouldn’t it? For instance, I might want to know which cake ingredients are also used when baking cookies.

Here’s how I would 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 window.
4. In the input box under “Apply to range,” type in the range of cells you want to apply the formatting on.
   • In my example, I want to see the highlights of column B only. Therefore, I typed: B2:B12.
5. Next, in the “Format rules“ section, under “Format cells if,” click on the drop-down arrow.
6. From the drop-down menu that appears, select “Custom formula is.”
7. From here, you will see an input box below the drop-down list.
   • Type your custom formula there, such as:

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

8. Under “Formatting style,” click the “Fill Color” button.
9. Select the color you want to highlight the matching cells/rows. I selected “light cornflower blue 1.”
10. Finally, click the “Done” button, and Conditional formatting will be applied.

If you followed my steps exactly, then you will see all the cells (ingredients) of column B (cookies) that are also used in column A (cake) highlighted in your chosen color.

Explanation of the Formula

In the example above, I wanted to know which same ingredients are used to make cookies (column B) and cakes (column A).

In other words, I wanted to highlight the items from cells A2 to A9 and the same ones from column B. Therefore, I used the COUNTIF formula below:

=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, if the count is more than 0, the function returns a TRUE.

When the function returns a TRUE value, conditional formatting works 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 I want to do the opposite? For example, what if I want to find the ingredients of Cookies that are not also used in Cakes? In this case, I would 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

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

This will give me the following result below:

These are some of the ways you can use Google Sheets to compare two columns. While I have used a smaller sample to help you understand the concepts, there are other methods discussed in this tutorial that can be equally powerful when used with larger datasets, especially if you’re using them for analytical processing.

Therefore, I strongly encourage you to apply and experiment with these methods, and I hope you have found my explanations helpful.

How To Compare Columns Using a VLOOKUP Formula

Another method that I commonly use to compare columns in Google Sheets is the VLOOKUP function. This operates using the following syntax:

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

To use this formula, I would need to specify which cell to compare, such as the search_key. The column range searches for matches, while the index column specifies the range in columns A, B, and C. Additionally, if I want to use the data from column B to display the results, I would type the number two (2) as it’s the second column.

Then, I can define whether I want an exact or approximate comparison using the TRUE or FALSE operators. In the example below, a “FALSE” result means that the data is not an exact match.

In the example below, I used the following references:

• B2 as the search_key
• $C$2:$C$5 as the comparison_column_range
• 1 as the index
• FALSE for exact matches

Note: Any values that don’t match display 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 example below, I will compare two children’s favorite animals with the following formula:

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

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 of 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 several functions that can compare columns in Google Sheets. The most popular methods include:

• VLOOKUP
• COUNTIF
• IF
• MATCH
• Conditional Formatting

How Do I Compare Two Columns in Google Sheets and Highlight Duplicates? 

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 equals sign (=) between the first two rows of the columns you want to compare. For example, if I want to compare columns A and B from row 2 down, I would type: =$A2=$B2
5. Click “Done”

Can Google Sheets Compare Two Columns With Conditional Formatting?

Yes. I can apply conditional formatting to cells by comparing matching data in two columns, where it is applied.

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

If you want to 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). Type 0 to search for exact matches or 1 to look for partial matches. For example, if I want to search if the value in C1 is the same as in column D between rows 5 and 10, I would use the following formula:

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

Wrapping Up 

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

Check out some of our related guides here:

• How to Split Cell in Google Sheets (into separate columns)
• How to Find Unique Values in Google Sheets (2 Easy Methods)
• How to Use the Not Equal Google Sheets Symbol [Easy]
• How To Remove Duplicates In Google Sheets (3 Easy Ways)
• How to Number Rows in Google Sheets (Add Serial Numbers)
• REGEXMATCH Function in Google Sheets
• How to VLOOKUP Multiple Criteria in Google Sheets
• How to Compare Two Sheets in Google Sheets