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. Show
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 matchesLet’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 DatasetStep 1: In a blank cell where you want to paste the result, enter the following formula to check for matches:
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 matchTo 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 matchesThus, 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 matchesFor 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 cellsAlthough 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:
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 SheetsCase #3: Using Conditional Formatting to compare two columns in Google SheetsLet’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 optionStep 2: Under the “Format rules” option, select “Custom Formula is” as shown below. Selecting the option that allows us to insert the ruleStep 3: Insert the rule as shown below:
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 colorStep 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 appliedThe highlighted cells indicate the items which are available in both the shops. Case #4: Using the MATCH function to compare two columns in Google SheetsThe 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:
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 itemStep 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 dataAn 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 duplicatesWe 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?Another way to find the difference in Google Sheets is to manually subtract the two values using the minus sign ''. To do that, insert this formula =A2B2.
How to compare two columns in Google Sheets for matches and differences?Conditional formatting to compare data in two Google Sheets. Select the range with records to color (A2:A10 for me).. Go to Format > Conditional formatting in the spreadsheet menu.. Enter a simple formula to the rule: =A2=C2.. Pick the color to highlight cells.. How do you calculate difference in Google Sheets?How to Subtract Cells in Google Sheets. Select the cell where you want to show the calculation results. That'll be cell B3 in this example.. Go to the formula bar and enter the formula below: =B1B2. Press Enter.. How do you find the difference between two columns?On the Home tab, go to Editing group, and click Find & Select > Go To Special… Then select Row differences and click the OK button. The cells whose values are different from the comparison cell in each row are colored.
