How to compare two columns in Excel – Matches and Differences

Sometimes we are faced with this question: How to compare two columns in Excel? It can be a challenge to use the most effective way to reach the result. The chosen solution depends on user requirements and the structure of the initial data.

For example, we can apply simple conditional formatting to highlight all the matching data points in two columns. Furthermore, we’ll use complex VBA functions to check the similarity between records.

In this tutorial, we will discover several techniques to compare two columns in Excel and highlight matches and differences between them.

Compare Two Columns for Exact Row Match

Let us begin to compare two columns in Excel with some detailed tutorials.

In this section, we’ll show you how to compare and identify which rows contain the same value and which ones are different.

Example: Compare cells in the same row – Use the Equal Sign

Here is the initial data set. We will compare the names row by row without using built-in Excel functions. Check the first name in column A. Now we find it is the same in the second column or it’s different.

To quickly get the result, use a simple equal sign.

=E2=F2
compare two columns using equal sign

If cell E3 is equal to F2, Excel will write a TRUE string into the column G. If the two value is different, we’ll get the result as FALSE.

Example: Apply IF formula to Compare Cells in the same row

How to get an easier understandable result? Chose “equal” as a second parameter, and “Different” as the third parameter of the IF formula. Now the result will speak for itself.

The IF formula will return “Equal” when the names are the same. The result is “Not equal” when the names are different.

=IF(A2=B2,"Equal","Not Equal")
how to compare two columns using if formula

Example: Case Sensitive cell comparison using the EXACT function

Good to know: If we want to get case sensitive result we have to combine the IF and the EXACT functions.

Tip: This expression is case sensitive! “BERMUDA” and “Bermuda” will be identified as the same and will be marked as DIFFERENT items.

=IF(EXACT(A2,B2),"equal","different")
exact match for comparing two columns

Example: Highlight Matching Data using conditional formatting

How to highlight the same rows in place? The best space-saving solution to do that using conditional formatting. The given cells will be highlighted instead of creating an additional column.

1. Select the range which contains the data set.

2. Click the Home tab on the ribbon.

3. Choose the Styles group. Click on the ‘Conditional Formatting’ icon.

home tab conditional formatting

4. Click on the “new rule” from the drop-down list.

5. Locate the ‘New Formatting Rule’ dialog box and click on the ‘Use a formula to determine which cells to format.’

apply a new rule

6. Enter the formula: =$E2=$F2 into the formula field.

enter formula for conditional formatting rule box

7. To select the format we want to use to the matching cells, click the Format button.

8. Click the OK button.

compare two columns using conditional formatting

Finally, all the cells where names are equal in each row will be highlighted.

Compare Two Columns and Highlight Matches

In some situation, we need to compare two columns and highlight matching data. In this example, we’ll show you how to find duplicates using conditional formatting.

Tip: This method is not a row by row comparison!

Example: Compare Different Ranges and Highlight Matching Data

If we have different ranges and matching records, but those located in a different position, we can apply conditional formatting to highlight all matching items.

On this image below, you can see that the ‘E2:A10’ range is not equal size to ‘F2:F11’ range. At first look we have matching names but not in the same position.

compare two columns not equal ranges

Follow these steps below to compare two different sizes columns.

1. Select the range which contains names.

2. Go to the Home tab and choose the Styles group. Click on the conditional formatting icon.

apply conditional formatting

3. Select the Highlight cell Rules option then click on the Duplicate values.

duplicate values dialog box

4. The Duplicate Values dialog box will appear. Choose the Duplicate option on the left side of the window.

5. Apply your favorite style using the drop-down list.

6. Click OK.

highlight unique matches

Good to know that this rule is not case sensitive! ‘Florida’ and ‘FLORIDA’ will be identified as the same and will be marked as duplicated items.

Example: Compare Two Columns and Highlight Unique Items

Actually, we would like to apply an inverse selection to find and highlight unique items.

Select the initial range.

Click the Home tab on the ribbon.

Navigate to Styles group, click on the ‘Conditional Formatting’ icon.

Select the Highlight Cell Rules option. Now click on Duplicate Values.

Compare Two Columns and Highlight Unique Items

In the dialog box, select the ‘Unique’ option.

Set up the styles for cell formatting.

find duplicate values

Click OK.

Check the result on the picture above. As a result of inverse selection, all cells will be highlighted that have a unique name, and that does not exist on the second list.

Compare Two Columns and Extract Differences

In this section, we’ll show you some unusual method to compare two columns and extract the result into a new list. We’ll introduce the usage of array formula too. We recommend this method for advanced Excel users.

Example: Apply array formula for comparing unsorted lists

In this example, we’ll demonstrate an array formula in cell D3. The array formula will extract values than only exist in the List 1 and not in List 2. The formula will be used in cell D7 too, but we’ll use different cell references.

Enter the formula below in cell D3:

=INDEX($A$3:$A$7,SMALL(IF(COUNTIF($B$3:$B$8,$A$3:$A$7)=0, MATCH(ROW($A$3:$A$7),ROW($A$3:$A$7)),""),ROWS($A$1:A1)))
Apply array formula for comparing unsorted lists

Explanation: To apply an array formula hold Ctrl + Shift at the same time then press Enter. Finally, release all keys to validate the formula. If you see the formula between brackets, you are done! Please don’t edit the formula manually on the formula bar.

How to comparison formula works?

The expression below count values in List 1 based on values in List 1.

COUNTIF($B$3:$B$8,$A$3:$A$7)=0

To replace TRUE with corresponding row number use:

(IF(COUNTIF($B$3:$B$8,$A$3:$A$7)=0, MATCH(ROW($A$3:$A$7),ROW($A$3:$A$7))

If we want to find the n-th smallest row number apply this formula below:

SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1))

As a result of

INDEX($D$3:$D$8, SMALL(IF(COUNTIF($B$3:$B$7, $D$3:$D$8)=0, MATCH(ROW($D$3:$D$8), ROW($D$3:$D$8)),""), ROWS($A$1:A1)))

we’ll get the “E” value in cell D3.

Example: Compare two columns using sorted elements

In this example, we want to find missing data points using sorted lists. To compare two columns in the ordered list, use this formula below. We can get the same result as per our wish.

Apply the formula below:

Compare Two Columns and Fetch the Matching Data
=INDEX($A$3:$A$7,MATCH(SMALL(IF((COUNTIF($D$2:D2,$A$3:$A$7)+COUNTIF($B$3:$B$8,$A$3:$A$7))=0,COUNTIF($A$3:$A$7,"<"&$A$3:$A$7)),1),COUNTIF($A$3:$A$7,"<"&$A$3:$A$7),0))

Compare Two Columns and Fetch the Matching Data

We’ll use lookup formulas to compare two lists and pull the matching data points.

Example: Exact Data Matching using VLOOKUP, INDEX, and MATCH

For example, we want to pull the sales data for column 2 based on column 1. To do this, we’ll use a simple lookup formula in Column 1.

compare two columns using vlookups

The VLOOKUP function to check whether a record in A is present in column B or not.

If we found a match, the formula will return that the corresponding value from column Sales. If the result is different, we’ll get a #N/A error.

=VLOOKUP(I2,$F$2:$G$10,2)

As an alternative, we can use a nested formula which contains INDEX and MATCH. We will get the same result as the previously described solution with the VLOOUKUP formula.

compare two columns using index and match
=INDEX($F$2:$G$10,MATCH(I2,$F$2:$F$10,0),2)

Example: How do you do a partial match in Excel?

In this tutorial, we’ll show you the steps of finding partial matches using wildcards to compare two columns. To find a partial match we’ll the VLOOKUP function and apply wildcards (asterisk) “*” in Excel.

compare two colimns partial match using asterisk wildcards
=VLOOKUP("*"&I2&"*",$F$2:$G$10,2,0)

Calculating similarity score (%) between two strings

In this example, we’ll show you how to calculate the similarity between two strings. We want to consider using a function that will tell us, in exact terms, how “close” two string is. If you want to clean data in Excel, we strongly recommend to use it!

Example: Using similarity to compare two columns

Let’s see the next question:

Is there a way to compare two columns and get the similarity percentage between text in two cells that are in the same row?

The syntax and the result look like below:

=LEVENSHTEIN("reorder","recorder") = 88

Just insert the code into a new module or download the template.

The below-mentioned algorithm tries to find the common and non-common parts of the strings and factor them to generate the similarity score using percentage.

Function Levenshtein(ByVal str1 As String, ByVal str2 As String) As Long

Dim i As Long, j As Long, str1_length As Long, str2_length As Long
Dim gap(0 To 60, 0 To 50) As Long, sm1(1 To 60) As Long, sm2(1 To 50) As Long
Dim m1 As Long, m2 As Long, m3 As Long, mm As Long, MaxL As Long

str1_length = Len(str1):  str2_length = Len(str2)

gap(0, 0) = 0
For i = 1 To str1_length:    gap(i, 0) = i: sm1(i) = Asc(LCase(Mid$(str1, i, 1))): Next
For j = 1 To str2_length:    gap(0, j) = j: sm2(j) = Asc(LCase(Mid$(str2, j, 1))): Next
For i = 1 To str1_length
    For j = 1 To str2_length
        If sm1(i) = sm2(j) Then
            gap(i, j) = gap(i - 1, j - 1)
        Else
            m1 = gap(i - 1, j) + 1
            m2 = gap(i, j - 1) + 1
            m3 = gap(i - 1, j - 1) + 1
            If m2 < m1 Then
                If m2 < m3 Then mm = m2 Else mm = m3
            Else
                If m1 < m3 Then mm = m1 Else mm = m3
            End If
            gap(i, j) = mm
        End If
    Next
Next

MaxL = str1_length: If str2_length > MaxL Then MaxL = str2_length
Levenshtein = 100 - CLng((gap(str1_length, str2_length) * 100) / MaxL)

End Function

Download sample Workbooks

We have prepared examples for all comparing methods; you can download the tutorials.