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.

Table of Contents

**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*:

**Note: I will be comparing two columns within the same worksheet**. However, you can use the same techniques to compare two columns in different Google Sheets documents. To do this, you can use the IMPORTRANGE function 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, 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:

- In column C (in the cell C2), enter the formula:
**=A2=B2** - Press the return key.
- 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:

- In cell C2, enter the formula:
**=IF(A2=B2,”Matching”,”Not Matching”)** - Press the return key.
- 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:

- Click the “
**Format***”*menu from the menu bar. - Select the “
**Conditional Formatting***“*option. - This will open the “
sidebar on the right of the window.**Conditional format rules**“ - 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**. - Next, in the “
**Format rules***“*section, under “**Format cells if**,” click on the drop-down arrow. - From the drop-down menu, select “
**Custom formula is**.” - You will see an input box below the drop-down list.
- Type your custom formula, such as: “=$A2=$B2”.

- Under
*“***Formatting style**,” click the “**Fill Color***“*button. - Select the color you want to highlight the matching cells/rows. My favorite color is blue, so I selected
*“***light cornflower blue 1***.*” - 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:

- Click the “
**Format***”*menu from the menu bar. - Select the “
**Conditional Formatting***“*option. - This will open the “
**Conditional format rules***“*sidebar window. - In the input box under
*“*,” type in the range of cells you want to apply the formatting on.**Apply to range**- In my example, I want to see the highlights of column B only. Therefore, I typed: B2:B12.

- Next, in the “
**Format rules***“*section, under “**Format cells if**,” click on the drop-down arrow. - From the drop-down menu that appears, select “
**Custom formula is**.” - 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**

- Type your custom formula there, such as:
- Under “
**Formatting style**,” click the “**Fill Color**” button. - Select the color you want to highlight the matching cells/rows. I selected “l
**ight cornflower blue 1.**” - 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:

- Highlight the columns you want to compare
- Navigate to “
**Formatting” > “Conditional formatting”** - Set the “F
**ormat cells**” menu item to “**Custom formula is”** - 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** - 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

## 10 thoughts on “A Simple Google Sheets Compare Two Columns Guide for 2024”

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

Try using this guide instead https://spreadsheetpoint.com/highlight-duplicates-google-sheets/

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?

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!

Thank you so much.

Very easy to understand and helped me a lot.

Thanks so much for this!

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.

This is very helpful and comprehensive.

Fantastic!

Hello! wonderful guide, Quick Question what if i have multiple entries in the a single cell ( e.g Location a customer has used for our stores) and want to compare the updated list for same data, But wanted to compare to see if any new store has shwon up in the list in the cell

Columns A Column B

Houston, Sugarland, Katy Houston, Katy, Sugarland, the Woodlands

So want to compare Cell B2 with A2 and In Cell C spit out the Woodlands as a new location added

Is that possible?

Thank you