Get instant live expert help with Excel or Google Sheets
“My Excelchat expert helped me in less than 20 minutes, saving me what would have been 5 hours of work!”

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.

How to Compare Two Columns in Google Sheets and Excel

Excel provides us with different methods to compare two columns and find unique or duplicate values with the use of the VLOOKUP, MATCH, INDEX, IF, COUNTIF or Conditional Formatting functions.  This step by step tutorial will assist all levels of Excel users in comparing two columns in Excel or Google sheets.  

Figure 1. Final result: Compare two columns in Excel

Data to compare two columns in Excel

Our data consists of three columns: List 1 (column B), List 2 (column C) and Comparison (column D).  We want to compare the product codes in the two columns B and C.  The results of comparison will be recorded in column D.  

Figure 2. Sample data to compare two columns in Excel

Compare two columns in Excel using VLOOKUP

VLOOKUP is used when we want to look up a value in one column and determine if it exists in another column.  There are two match types: approximate match or exact match. When comparing lists, the exact match is most commonly used.  

Syntax of the VLOOKUP function

=VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])

  • lookup_value – the value that we want to find in the table_array
  • table_array – the range of cells containing the data we want to find or retrieve
  • col_index_num the column number in the table_array corresponding to the information we want to retrieve, relative to the lookup_value
  • [range_lookup] – optional; value can either be TRUE or FALSE
    • if TRUE or omitted, VLOOKUP returns either an exact or approximate match
    • if FALSE, VLOOKUP will only find an exact match

In order to determine if a value in the first column (List 1) exists in another column (List 2) and return the value itself, we follow these steps:  

Step 1.  Enter the formula in cell D3: =VLOOKUP(B3,$C$3:$C$9,1,FALSE)

Step 2.  Press ENTER

Step 3:  Copy the formula in cell D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down

Our lookup_value is the value in column B (B3), while our table_array is the set of values in column C, $C$3:$C$9.  The col_index_num is 1, which returns the value in the first column relative to the table_array if the value in column B is found in column C.  Otherwise, the function will return the value “#N/A”.  The range_lookup is FALSE, which means that the function will look for an exact match.  

As a result, the value in cell D3 is “19001”, because the value in column B “19001” is found in column C.  

Figure 3. Excel VLOOKUP returning matched value in two columns

For the next value in column B, “19002”, the VLOOKUP function returns “#N/A” in cell D4 because “19002” is not found in column C.  

Figure 4. VLOOKUP returning #N/A for unmatched value

Below table shows the final results after comparing the two lists using VLOOKUP.  The values 19001, 19003, 19005 and 19006 are present in the two lists.  

Figure 5. Using VLOOKUP to compare two columns in Excel

Compare two columns in Google sheets using VLOOKUP

In the same way, we can compare two columns in Google sheets using VLOOKUP by following the same procedure above. We insert the following formula into D3 and drag it down to D9.

=VLOOKUP(B3,$C$3:$C$9,1,FALSE)

The results of comparison in Excel and Google sheets is the same, as shown in the table below:

Figure 6. Using VLOOKUP to compare two columns in Google sheets

Compare two columns using Excel COUNTIF

While working with Excel or Google sheets and we want to count the number of times a value in one column exists in another column, we can use the function COUNTIF.     

Syntax of COUNTIF Function

=COUNTIF(range,criteria)

  • range – the data range that will be evaluated using the criteria
  • criteria – the criteria or condition that determines which cells will be counted

In order to determine the number of times that a value in column B exists in column C, we follow these steps:  

Step 1.  Enter the formula in cell D3: =COUNTIF($C$3:$C$9,B3)

Step 2.  Press ENTER

Step 3:  Copy the formula in cell D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down

Our range is the set of values in column C, $C$3:$C$9.  The criteria is B3, or the value in column B. The function returns the frequency of B3 in the range in column C. As a result, the value in cell D3 is “1”, because “19001” is found once in column C.  

Figure 7. Excel COUNTIF returning the count for a matched value in two columns

As for B4, or “19002”, the COUNTIF function returns “0”, because “19002” is not found in column C.  

Figure 8. COUNTIF returning “0” for unmatched value

The value in D5 is “2” because “19003” is found twice in column C, specifically in C3 and C9.  

Figure 9. Using COUNTIF to compare two columns in Excel

Customize the result using IF and COUNTIF

We can customize the results when we compare two columns in Excel by combining the IF and COUNTIF functions.  We follow the same procedure as in the previous example but we will be using this formula:  

=IF(COUNTIF($C$3:$C$9,B3)>0,"Match found","Not found")

Instead of returning the count of matched values in the two lists, the formula returns “Match found” when the value in column B exists in column C, and returns “Not found” if otherwise.  

Figure 10. Using IF and COUNTIF to customize results

Compare two columns using MATCH

When we compare two columns in Excel, we might want to return the position of a value in a specified column, instead of the value itself.  The MATCH function is the perfect solution for this, as it returns the position of a value in a specified range.  Note, however, that it only considers the first match.

Syntax of the MATCH function

=MATCH(lookup_value, lookup_array, [match_type])

  • lookup_value – a value which we want to find in the lookup_array
  • lookup_array – the range of cells containing the value we want to match
  • [match_type] optional; the type of match; if omitted, the default value is 1; We use 0 to find an exact match

In order to determine if a value exists in two columns B and C and return its position instead of the value itself, we follow these steps:  

Step 1.  Enter the formula in cell D3: =MATCH(B3,$C$3:$C$9,0)

Step 2.  Press ENTER

Step 3:  Copy the formula in cell D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down

Our lookup_value is the value in column B, while our lookup_array is the set of values in column C, $C$3:$C$9. The match_type is “0” in order to find the exact match.  

Figure 11. Using Excel MATCH to return positions of matched values in two columns

As a result, the value in cell D3 is “4”, because the value in column B “19001” is found in the fourth position (C6)  in the lookup_array C3:C9. On the other hand, when a value is not found, the function returns “#N/A”, which is the case for D4, D6 and D9.  

For “19003”, there are two matches in column C, specifically C3 and C9.  However, MATCH function only considers the first match and disregards any other matches.  In D5, the result is “1”, because the first match for “19003” is in the 1st position in the range C3:C9.  

Compare two columns using INDEX and MATCH

A combination of INDEX and MATCH functions is used when we want to match two columns in our Google sheets or in Microsoft Excel.  The MATCH function, as discussed above, returns the position of a value in a specified column while INDEX function returns the value of a cell in a range based on a column and row number.  

Syntax of the INDEX function

=INDEX(array, row_num, column_num)

  • array – a range of cells where we want to retrieve some data
  • row_num – the row in the array from which we want to retrieve data
  • column_num – the column in the array from which we want to retrieve data; if the array has only one column, column_num can be omitted

In order to compare the two columns B and C and return the matched value by using INDEX and MATCH, we follow these steps:  

Step 1.  Enter the formula in cell D3: =INDEX($C$3:$C$9,MATCH(B3,$C$3:$C$9,0))

Step 2.  Press ENTER

Step 3:  Copy the formula in cell D3 to cells D4:D9 by clicking the “+” icon at the bottom-right corner of cell D3 and dragging it down

Our array is the set of values in column C, $C$3:$C$9, and the row number is provided by the MATCH function.

Figure 12. Using Excel MATCH and INDEX to return matched values in two columns

In the first example, our MATCH function looks up B3 or “19001” in the range C3:C9, and returns “4” which is the position of “19001” in the range C3:C9.  The INDEX function then returns the fourth value in the array C3:C9, which is C6 or “19001”. The column number is ignored because we are considering only one column in our array, column C.  

Finally, the value in cell D3 is “19001”.  The combination of INDEX and MATCH when used to compare two columns yields the same results as when we are using VLOOKUP.  

Compare two columns using Conditional Formatting

Conditional formatting is another built-in function in Excel or Google sheets that helps us compare two columns based on a set of rules.  

Highlight duplicate values in two columns

Suppose we want to compare two columns in Excel and highlight the matches or duplicate values in B and C. We follow these steps:

Step 1. Select the cells we want to highlight, B3:C9.   

Figure 13. Selecting the cells to highlight matches in two columns

Step 2. Click the Home tab, Conditional Formatting Menu and select “Highlight  Cells Rules”, then “Duplicate Values”. The Duplicate Values dialog box will pop up.  

Figure 14. Creation of a new rule in Conditional Formatting

Step 3. In the Duplicate Values dialog box, select “Duplicate” and “Light Red Fill with Dark Red Text” in the drop-down lists

Figure 15.  Formatting duplicate values in two columns

Step 4. Press OK to apply the formatting rule.  

As a result, the duplicate values 19001, 19003, 19005 and 19006 in the two columns B and C are highlighted.

Figure 16.  Output: Formatting rule reflected in duplicate values

Highlight unique values in two columns

We can also compare two columns in Excel and highlight the differences  and use conditional formatting to highlight only the unique values.  We follow the same procedure but this time, we choose “Unique” from the drop-down list and choose any format we want. In this case, we choose the formatting style “Yellow Fill with Dark Yellow Text”.

Figure 17.  Output: Formatting rule reflected in unique values

As a result, the unique values 19002, 19004 and 19007 in the two columns B and C are highlighted.

Most of the time, the problem you will need to solve will be more complex than a simple application of a formula or function. If you want to save hours of research and frustration, try our live Excelchat service! Our Excel Experts are available 24/7 to answer any Excel question you may have. We guarantee a connection within 30 seconds and a customized solution within 20 minutes.

Are you still looking for help with the IF function? View our comprehensive round-up of IF function tutorials here.

Did this post not answer your question? Get a solution from connecting with the expert.

Another blog reader asked this question today on Excelchat:
Solution examples
I filtered data in column A (it is labeled ID #) of a data set. On my next worksheet, column A is also the ID #, but it is a different data set. I want to filter the ID #'s the same for the two sheets, but how do I do that being that I am working with two different sets of data?
Solved by V. H. in 22 mins
VLOOKUP help. Trying to input data from database into new table
Solved by S. Q. in 40 mins
Could you help me by explaining the IF function?
Solved by G. L. in 24 mins
I need a formula to compare the data in two columns and then export the mismatched data in the 3rd column
Solved by S. Q. in 20 mins
I have a question about Conditional Formatting. =IF('Sheet1'!A1="X",IF('Sheet1'!B1="Y", TRUE, FALSE),FALSE) the set the cell background to red. I would like to make it so that if Sheet1'!B1="Z" I set the back to blue. How can I achieve this.
Solved by B. F. in 39 mins

Leave a Comment

avatar

Subscribe to Excelchat.co

Get updates on helpful Excel topics

Subscribe to Excelchat.co

Did this post not answer your question?
Get a solution from connecting with the expert

Another blog reader asked this question today on Excelchat:

Post your problem and you’ll get expert help in seconds.

Your message must be at least 40 characters
Our professional experts are available now. Your privacy is guaranteed.
Trusted by people who work at
Amazon.com, Inc
Facebook, Inc
Accenture PLC
Siemens AG
Macy's
The Allstate Corporation
United Parcel Service
Dell Inc