Google Sheets difference between two columns

Comparison is inherent in our daily lives. We compare the prices at which the same good is sold by different sellers to choose the cheaper one. We compare the performances of different individuals to determine who performed better. Any decision taken at the management level is done so after benchmarking it against the data available. 

This article demonstrates how users can compare two columns in Google Sheets and make informed decisions or infer a conclusion based on the output.

Sample Google Sheets template with formula .

Table of Contents

Case #1: Using IF to compare two columns in Google Sheets for row matches

Let’s assume we have the following data of two shops and wish to know which products in Shop A are available in Shop B as well.

Sample Dataset

Step 1: In a blank cell where you want to paste the result, enter the following formula to check for matches:

=If(Cell1=Cell2,” Available”,” Not Available”)

Replace ‘Cell1’ and ‘Cell2’ with the location of the cells that contain the data which needs to be matched.

Once you input the formula, you will see the following result:

Checking if items in the first row match 

To know more about how to use the IF function in Google Sheets, click here.

Step 2: Copy and paste the formula in the cells below to cover the entire data and you will find those items that are available in both shops.

Result after using the IF function to look for matches

Thus, we find that only Eraser and Pens are available in both shops. However, there’s one limitation to this solution. The solution only produces appropriate results if the same items are entered into adjacent cells. We will resolve this problem in the next case we consider.

Case #2: Using the VLOOKUP function to compare two columns in Google Sheets for row matches

For instance, if we tweak the position of the data a little bit, we are left with inaccurate results as shown below:

When items are not placed in adjacent cells

Although pens are available in both shops, we see that the result is “Not Available”. It is because the items are not inputted in adjacent cells.

To solve this problem we use the VLOOKUP formula. The VLOOKUP formula is an effective way to compare two columns in Google Sheets. To learn about how to use the VLOOKUP formula, you may click here.

Thus, instead of the formula in step 1, we use the following formula:

=VLOOKUP(A3,$B$3:$B$9,1,0)

What this formula does is that it takes the search key (A3), and looks for it in the range ($B$3:$B$9), on finding a match it returns the data corresponding to the search key from the first column, indicated by the number 1. The argument, ‘0’ at the end implies that the data is not sorted.

On dragging it down to include the complete data, we find the following result:

Using the VLOOKUP function to compare two columns in Google Sheets

Case #3: Using Conditional Formatting to compare two columns in Google Sheets

Let’s assume we have the same data and we have to look for matches in the data. To find the cells we follow the steps given below.

Step 1: Select the second column. Navigate to Format→ Conditional Formatting.

Navigating to the conditional formatting option

Step 2: Under the “Format rules” option, select “Custom Formula is” as shown below.

Selecting the option that allows us to insert the rule

Step 3: Insert the rule as shown below:

=COUNTIF($A$3:$A$7, B3)>0

The COUNTIF function is used to count the frequency of the data in B3 in the given range. If the data is present in the range, i.e., has a frequency of 1 or more, the rule above yields “TRUE” and subsequently colours the concerned cell.

Applying the rule and finalizing the color

Step 4: Click done. You’ll notice that the items in Shop B which are also available in Shop A will have their cells coloured as shown below:

Data after conditional formatting has been applied

The highlighted cells indicate the items which are available in both the shops.

Case #4: Using the MATCH function to compare two columns in Google Sheets

The MATCH function works in the same way as the VLOOKUP function. The only difference between the two is that while the latter produces output according to the user’s preference, the former displays the row number of the item if found in the lookup range. 

Let’s understand how to use the MATCH function to compare two columns in Google Sheets using the same data as in the previous examples. 

Step 1: In a blank cell, enter the following formula:

=MATCH(A3,$B$3:$B$9,0)

The MATCH function takes the search key (A3) and looks for its matches in the lookup range ($B$3:$B$9). The third argument, 0, indicates that the function should look for exact matche. On finding the match, the function returns the relative row number in which it has found a match and displays it in the cell corresponding to the search key.

Using the Match function to find the relative position of an item

Step 2: Copy and paste the formula to the cells below to include the complete data. Once the complete data is covered, you will be left with the following result:

Applying the MATCH function to the whole data

An interesting thing to note is that the MATCH function will return the first row in which it has found a match with the item that is to be looked up. 

For example, if we had another occurrence of “Pens” in the last row, the MATCH function would still show that the item “Pens” is located in the first row only as shown below.

Using MATCH function with duplicates

We notice that although the item “Pens” has been inputted twice in row 3 and row 10, the MATCH function only considers it once when producing the output.

Conclusion

Although the cases considered above are not exhaustive, they are enough to complete most of the tasks that will require you to compare two columns in Google Sheets. To know more such tips and tricks that make your tasks using Google Sheets easier, head to our blog.

How to calculate difference between two columns in Google Sheets?

How to compare two columns in Google Sheets for matches and differences?

How do you calculate difference in Google Sheets?

How do you find the difference between two columns?