The Ultimate Guide to Comparing Two Columns in Google Sheets in 2024

Are you working with large amounts of data in Google Sheets and struggling to spot small differences or similarities between data elements? Don’t worry, Google Sheets has got you covered! In this ultimate guide, I’ll show you how to compare two columns in Google Sheets using conditional formatting and the COUNTIF function.

Comparing Columns to Find Matching Data

When dealing with large data sets, it can be challenging for the human eye to identify small differences or similarities between data elements. Thankfully, data processing software like Google Sheets can spot details that even the most observant human eye might miss.

Tasks such as comparing columns, finding differences, and highlighting similarities can be easily accomplished without errors. In the following tutorial, I will show you how to use Google Sheets to compare two columns.

In the upcoming tutorials, I will be using the sample data below, consisting of two columns: cake ingredients and cookie ingredients.

How to Compare Two Columns for Exact Line-by-Line Matches

The simplest way to compare two columns is to find exact line-by-line matches, which requires a line-by-line comparison.

As you may know, Google Sheets needs to know which rows have the same values and which rows have different values. It can then display the result in an empty third 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 display as TRUE. Otherwise, if a pair of items in the same row do not match, I will use FALSE.

Here is the formula I will use for the comparisons:

=A2=B2

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

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

1. In column C (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 remaining cells in column C.

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

How to Compare Two Columns and Display Meaningful Text

Based on my personal experience, the above formula works well if you are the only one manipulating the data. However, I have often encountered problems when sharing the spreadsheet and data with colleagues. This is because they didn’t understand what I meant by 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 use the word “Matching” for row items that match and “Not Matching” for row items that do not match. I have found that this helps dispel misunderstandings and ensure better communication within a team.

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

For the example above, I 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 displays the text “Matching”. Otherwise, it displays the text “Not Matching”.

Here is the formula and the steps I 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 remaining cells in column C.

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

How to Compare Two Columns and Display Meaningful Text from Numeric Values

Just like in the previous example, if you want to compare two columns containing numeric values and display meaningful text, you can use an IF statement on the numeric data. You can use any operator or formula you want to display results as a logical expression.

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

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

Here, you can see that I used this formula to compare if Supermarket 1 is cheaper than Supermarket 2.

How to Compare Two Columns and Highlight Matching Rows

Of course, there will be times when instead of displaying the results in a separate column, you prefer to highlight the rows with matching data. In such cases, I would use conditional formatting as it is an excellent technique for formatting cells based on a specific condition.

Follow the example below to see how I used conditional formatting to highlight matching rows in Google Sheets:

1. Click on the “Format” menu in the menu bar.
2. Select the “Conditional formatting” option.
3. This will open the “Conditional formatting rules” sidebar on the right side of the window.
4. In the input box below the “Apply to range” area, enter the range of cells you want to compare. In my example, I entered: A2:B12.
5. Next, under the “Format rules” section, under “Format cells if”, click on the dropdown arrow.
6. In the dropdown menu that appears, select “Custom formula is”.
7. You will see an input box below the dropdown list.
   • Type your custom formula, for example: =$A2=$B2.
8. Under “Formatting style”, click on the “Fill color” button.
9. Select the color you want to use for highlighting the matching cells/rows. My preferred color is blue, so I selected “pale blue 1”.
10. Finally, click the “Done” button to let “Conditional formatting” do its job.

If you follow these steps, you will see that all matching cells/rows will be highlighted in the color you chose, as shown in my example below.

Note: If you want to do the opposite, i.e., highlight cells/rows that do not match, modify the formula from step 7 as follows:

=$A2<>$B2

How to Compare Two Columns without Sorting

So far, I have shown you techniques for knowing if cells in the same row match. But what if I want to compare two columns and find out which values from column A are also repeated in column B, regardless of the row they are in? This would make more sense for my sample data, wouldn’t it? For example, I might want to know which cake ingredients are also used when baking cookies.

Video of Comparing Unsorted Columns

Here is a quick video that shows the process I use when comparing two columns in Google Sheets:

Link to the video

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

So far, I have shown you techniques for knowing if cells in the same row match. But what if I want to compare two columns and find out which values from column A are also repeated in column B, regardless of the row they are in? This would make more sense for my sample data, wouldn’t it? For example, I might want to know which cake ingredients are also used when baking cookies.

Here is how I would use conditional formatting to highlight duplicate data:

1. Click on the “Format” menu in the menu bar.
2. Select the “Conditional formatting” option.
3. This will open the “Conditional formatting rules” sidebar.
4. In the input box below “Apply to range”, enter the range of cells you want to apply the formatting to.
   • In my example, I want to see the highlighting of column B only, so I entered: B2:B12.
5. Next, under the “Format rules” section, under “Format cells if”, click on the dropdown arrow.
6. In the dropdown menu that appears, select “Custom formula is”.
7. In this menu, you will see an input box below the dropdown list.
   • Type your custom formula, for example: =COUNTIF($A$2:$A$9,B2)>0.
8. Under “Formatting style”, click on the “Fill color” button.
9. Select the color you want to use for highlighting matching cells/rows. My preferred color is blue, so I selected “pale blue 1”.
10. Finally, click the “Done” button to let “Conditional formatting” do its job.

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

Explanation of the Formula

In the above example, I wanted to know which ingredients from column A are used when baking cookies (column B).

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

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

The COUNTIF function counts how many times the value in B2 appears in the range of cells A2:A9.

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

When the function returns a TRUE value, the conditional formatting works and highlights cell B2.

This is repeated for all cells from B2 to B12.

Finding the Difference Between Two Columns in Google Sheets

Now, what if I want to do the opposite? For example, what if I want to find out which ingredients for cookies are not used in cakes? In that case, I would make a slight modification to the formula used in Step 7 of the previous method.

The “custom formula” used in the “Conditional formatting rules” sidebar should now be:

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

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

This will give me the following result:

These are some of the ways you can use Google Sheets to compare two columns. While I used a smaller sample to help you grasp the concepts, there are other methods discussed in this guide that can be just as powerful when used with larger data sets, especially for analytical processing.

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

Using the VLOOKUP Function to Compare Two Columns in Google Sheets

Another method that I commonly use to compare columns in Google Sheets is the VLOOKUP function. It works with the following syntax:

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

To use this formula, I specify which cell to compare, such as the “search_key”. The column range does the search for matches, while the index column specifies the range within columns A, B, and C. Additionally, if I want to use data from column B to display the results, I would enter the number two (2) since it is the second column.

Then I can define whether I want an exact or approximate match using the TRUE or FALSE operators. In the example below, a “FALSE” result means 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 “range_column_index”
• 1 as the “index”
• FALSE for exact matches

Using the MATCH function in Google Sheets to Compare Two Columns

The MATCH function works similarly to VLOOKUP. The difference is that the results will display the position of the match in the search “range.” The syntax for the MATCH function is as follows:

=MATCH(lookup_value, range, [match_type])

• “lookup_value” is the value you want to compare
• “range” is the array to search for matches in
• “match_type” indicates whether to use exact matches. Enter 0 for exact matches or 1 for approximate matches.

In the example below, I will compare two children’s favorite animals using the following formula:

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

As you can see, instead of showing TRUE for matches, it gives a numeric value based on its position in the range. For example, C1 indicates 5 because the value Elephants is the fifth value in the range of column A.

FAQs about Comparing Two Columns in Google Sheets

How to 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 to Compare Two Columns in Google Sheets and Highlight Duplicates?

You can highlight duplicates in columns in Google Sheets using conditional formatting. To do this:

1. Select the columns you want to compare.
2. Go to “Format” > “Conditional formatting”.
3. Set the “Format cells if” option to “Custom formula is”.
4. Use absolute references for column names and use the equal sign (=) between the first two rows of the columns you want to compare. For example, if you want to compare columns A and B starting from row 2, 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 corresponding data in two columns where it is applied.

How to 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 as follows:

=MATCH(lookup_value, range, [match_type])

If you want to add the value or cell reference you want to match as the “lookup_value”, use absolute references to set the “range” (where to search). Enter 0 to search for exact matches or 1 to search 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)

Conclusion

As you can see, there are many different ways to use Google Sheets to compare data between columns. If you found this guide on comparing two columns in Google Sheets helpful, there is still much more to learn. I hope this guide has been useful to you.

For more information, you can check out some of our other guides:

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