Learn how to compare two columns in Excel to highlight matches and differences using functions and conditional formatting! 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
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")
Example: Case Sensitive cell comparison using the EXACT function
Good to know: If we want to get case sensitive results, 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")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.
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.’
6. Enter the formula: =$E2=$F2 into the formula field.
7. To select the format we want to use to the matching cells, click the Format button.
8. Click the OK button.
Finally, all the cells where names are equal in each row will be highlighted.
Compare Two Columns and Highlight Matches
In some situations, 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
In 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.
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.
3. Select the Highlight cell Rules option then click on the Duplicate values.
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.
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 for an inverse selection to find and highlight unique items.
1. Select the initial range.
2. Click the Home tab on the ribbon.
3. Navigate to Styles group, click on the ‘Conditional Formatting’ icon.
4. Select the Highlight Cell Rules option. Now click on Duplicate Values.
5. In the dialog box, select the ‘Unique’ option.
6. Set up the styles for cell formatting.
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 methods 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 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)))
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)=0To 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:
=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.
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 that contains INDEX and MATCH. We will get the same result as the previously described solution with the VLOOUKUP formula.
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.
=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") = 88Just 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 FunctionFree Excel Add-in to compare two columns quickly
If you are in a hurry try our free excel prodctivity add-in. Read more about this method!
Download sample Workbooks
We have prepared examples for all comparing methods; you can download the tutorials.
Additional resorces: