Working with large amounts of data can be overwhelming. Even though the human eye can easily spot discrepancies, identifying minor differences or similarities between data items is still challenging.
With data processing software such as Google Sheets, you are armed with the extra capability of picking up details that even the keenest human eye might miss.
You can efficiently perform basic tasks such as comparing two or more columns and finding differences and matches faster and without errors.
To make things easy, we’ll use different data samples for examples and images depicting each step while carrying out the tasks you want.
The first dataset we’ll be comparing consists of two columns. Both columns A and B include names of tech companies:
In this case, we’ll look at columns in the same worksheet. You can also compare columns from different worksheets provided you have permission and access to the external worksheet you want to draw comparisons with.
Google allows you to compare different worksheets through its IMPORTRANGE Google Sheets function. With the IMPORTRANGE function, you can easily import data and exact ranges quickly and easily, do real-time data transfers and place the data in any cell in your spreadsheet.
How to Compare Two Columns for Exact Row Matches
The easiest way to compare two columns is to make a row-by-row comparison to get exact row matches. To do this with Google sheets, we first need to create a new column for the results we will get when matching columns A and B we made.
Once Google sheets can identify which rows feature the same values and which don’t, we’ll get our results in terms of TRUE and FALSE in the third blank column.
Using our examples, For every row that includes a company that matches, the third row will display a TRUE result, and for rows that feature companies that don’t match, we’ll get a FALSE.
The formula you would use for a comparison like this is =A2=B2
The above formula compares data in row 2 of both columns to see if they match. If they do, then you get a TRUE result. If not, you get a FALSE result.
Use the following steps to get the same results:
- Insert the formula: =A2=B2 in the second row of column C (in cell C2)
- Press the return key
- Using the fill handle, drag down to copy the formula to the rest of the cells.
Using ‘IF’ formula to Compare Two Columns that Show Meaningful Text (e.g. ‘Matching’ & ‘Not Matching’)
Comparing columns using TRUE and FALSE works great if you’ll be the only one working with the data. Otherwise, another person going through your worksheet might find what you are implying with the words “TRUE” and “FALSE” confusing.
Giving a more descriptive result in column C makes much sense. Using the same worksheet above, we could use the word “Matching” for items on the same row that matches and “Not matching” for row items that don’t.
You can use a simple IF formula to get a more descriptive result. That way, it returns the text “Match” when the values are the same and “Mismatch” for different values.
Let’s assume you were working in cell C2 of our example. You would use the following formula:
The formula =IF(A2=B2,”Matching”,”Not Matching”) compares values in A2 and B2 using an IF function. The text “Matching” shows up on the results column if the condition “A2=B2” is TRUE. If not, it returns the text “Not Matching”.
Use the following steps to get the same results:
- Step 1: In the second row of column C (in cell C2), insert the formula: =IF(A2=B2,”Matching”,”Not Matching”)
- Step 2: Press the return key.
- Step 3: Using the fill handle, drag down to copy the formula to the rest of the cells. The results of each comparison should now show up as “Matching” or “Not Matching” in column C.
Compare Two Columns and Show Meaningful Text From Numerical Values
You could use an IF statement on numerical data, just like in the example above. There are operators or formulas that, when you input them, will result in logical expressions for the data you want to compare.
For instance, you could use a formula like =IF(C2<B2,”Yes”,”No”)
To compare if the data in C2 is less than the numbers in B2.
The example below features Items, Supermarket 1 and 2. We used the formula =IF(C2<B2,”Yes”,”No”) to compare whether Supermarket 2 is cheaper than Supermarket 1.
All you have to do is insert the formula =IF(C2<B2,”Yes”,”No”) In the second row of column C (in cell C2), click enter and autofill the rest of the columns and YES or NO shows up on the result column accordingly.
Compare Two Columns and Highlight Matching Rows
Another way to compare columns easily is by highlighting rows that match instead of displaying results in a separate column. To do this, you need to perform Conditional Formatting.
Conditional formatting lets you format cells based on a condition. It is a great technique that uses the value of the cells to effect results. Conditional formatting works based on another cell value in the worksheet after analyzing all the values for each cell.
For example, let’s say you have students’ overall scores in a subject, and you want to get the range of students who have performed exceptionally and those who have performed low. You can group them by highlighting students who have scored less than 35 as poorly performing students and those that scored more than 80 as exceptional students.
We’re essentially applying conditional formatting on the cell with scores of poorly performing students based on the scores of the outstanding students in the other cell.
Use the following steps to use Conditional Formatting to highlight matching rows in Google Sheets:
- Step 1: Click the Format menu from the menu bar.
- Step 2: Select the ‘Conditional Formatting option.
The ‘Conditional format rules’ sidebar will automatically open on the right of our Google sheet window.
Type in the range of cells you want to compare under “Apply to range” input box. In our example, we can type A2:B12.
Next, in the Format rules section, click on the dropdown arrow under “Format cells if”
Select “Custom formula is” from the dropdown menu that appears. In the input box below the dropdown list, put in your custom formula there: “=$A2=$B2”.
Under “Formatting style”, click on the Fill Color button.
Select the color you want to use in highlighting matching cells or rows. We selected “purple”.
Click the Done button to save the Conditional formatting settings you’ve made.
All matching cells per rows will be highlighted in the color you selected.
Note: If you’d rather do the opposite, you have to change the formula in step 7 to: =$A2<>$B2 to highlight only the cells/rows that don’t match.
Using Google Sheets Conditional Formatting to Compare Two Columns and Find Matching Data
The methods to compare columns we’ve discussed are used mainly if cells in the exact row match.
In a situation where you’d like to compare two columns and find out which values of column A get repeated in column B, what do you do?
You could also use Conditional formatting to highlight duplicate data.
Use the following steps to make more sense of your sample data:
- Step 1: Click Format on the menu bar to bring out the ‘Conditional Formatting rules’ sidebar to the right of the window.
- Step 2:Type in the range of cells on which you want to apply the formatting in the input box under “Apply to range”. In our example, we only want to see column B highlighted. So we type B2:B8.
Next, click on the dropdown arrow under “Format cells if” in the Format rules section and select “Custom formula is”.
There’d be another input box after selecting “Custom formula is”, Type your custom formula there: =COUNTIF($A$2:$A$7,B2)>0
- Next is to set your formatting color by clicking on the Fill Color button.
- The color you select will highlight matching cells or rows.
- Click the Done button to let Conditional formatting do its work.
- All column B cells present in column A should now be highlighted in the color you selected.
Explanation of the Formula
Since we wanted to know which companies in column B get repeated in column A, highlighting the companies in column B that showed up one or more times in cells A2 to A9 is what we did.
In the formula: =COUNTIF($A$2:$A$9,B2)>0
The number of times the value in B2 occurs in the cell range A2:A9 is how the COUNTIF function works.
This means that as long as the count of a duplicate of the B2 value existing in column A exceeds 0, the function will return a TRUE. It could exist once, twice, or more times.
Cell B2 gets highlighted based on our conditional formatting rules when the function returns a TRUE value.
This is repeated for all cells starting from B2 to B12.
Finding the Google Sheets Difference Between Two Columns
In a situation where you want to do the opposite, i.e., find the companies in column B that are not present in column A.
Now, you have to change the formula used in step 7 of the previous method.
The Custom formula used in the Conditional format rules should now be:
We’re substituting this “>” for “=” in the new formula because we now want to see the element of column B and if it has 0 occurrences in column A.
The result below is what you’ll get when you input the formula:
All the methods we’ve been through are some of the ways you can use Google Sheets to compare two columns. Using a smaller dataset was so that you could understand the concepts easily.
You can also use the techniques we’ve discussed with larger datasets, especially if you’re using them for analytical processing.
Compare Columns Using a VLOOKUP Formula
VLOOKUP Formula in Google Sheets is a search function you can use with large sets of data. It allows anyone to look up data in a table arranged vertically.
To use the standard VLOOKUP formula, you must follow these criteria:
Set the search_key as the cell containing the data you want to compare.
Set the range to the information you want to compare it with.
To compare data within the same sheet, just use the formula =VLOOKUP(search_key,range,index,is_sorted)
To compare data from a different sheet, use the formula =VLOOKUP(search_key,’Sheet Name’!range,index,is_sorted)
You can also compare columns in Google Sheets using the VLOOKUP function which operates with the following syntax:
=VLOOKUP(search_key, range, index, [is_sorted])
Breaking Down the Formula Down
Using the VLOOKUP formula means there are some things you’d need to specify.
- First is the cell to compare as the search_key
- The column range you want to search for matches in
- The index column number in the range. If A, B, and C are your column range, and the data you want to use as the result is from column B, you’d type 2 as it’s the second column.
You can now define the type of comparison you want, whether exact or approximate, by using TRUE or FALSE operators. FALSE means that the data must be an exact match.
In the example below, we used:
- B2 as the search_key
- $C$2:$C$5 as the comparison_column_range
- 1 as the index
- And FALSE so we only return exact matches.
Take note that you’d get a #N/A error for values that don’t match, as there are no available matches in the specified range.
How to Compare Two Columns In Google Sheets Using MATCH
The difference between the MATCH function and VLOOKUP is that in MATCH, 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, we are pretending to compare two children’s favorite animals with the formula:
As visible in the illustration above, you get a numerical value depending on where it is in the range instead of showing TRUE for matches.
For example, C1 says 5, which Ram indicates. So Ram is the fifth value in column A.
Wrapping Up Comparing Columns in Google Sheets
There are many ways to compare data across columns in Google Sheets, whether small or large datasets.
We encourage you to apply and experiment with these methods. Hopefully, the explanations in this guide will help you get the hang of comparing columns on Google Sheets.