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 here.
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.
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:
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.
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:
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:
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.
Step 2: Under the “Format rules” option, select “Custom Formula is” as shown below.
Step 3: Insert the rule as shown below:
=COUNTIF($A$3:$A$7, B3)>0The 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.
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:
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.
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:
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.
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.
See Also
Here’s a list of some articles that you may find useful and worthwhile to check out.
How to use XLOOKUP function in Google Sheets