How to Compare Addresses in Excel (11 Possible Methods)

To demonstrate our methods, we’ll use the following dataset of two sets of bank customers, and find out which have accounts at both banks.

Method 1 – Using the IF Function

Steps:

• Select any cell to place your resultant value. Here, cell H5.
• Enter the following formula in the selected cell or in the Formula Bar:

• Press ENTER to return the output.

• Ue the Fill Handle to AutoFill the formulas for the rest of the cells.

Method 2 – Using IF & COUNTIF Functions

Here, we will compare the Email_ID of the two sets of customers.

Steps:

• In cell H6, enter the following formula:

• Press ENTER to return the output.

• Use the Fill Handle to AutoFill the formulas for the rest of the cells in column H.

Method 3 – Combining ISNUMBER & MATCH Functions

Steps:

• In cell H6, enter the following formula:

• Press ENTER to return the output.

• Use the Fill Handle to AutoFill the formulas for the rest of the cells.

Method 4 – Merging ISNUMBER & XMATCH Functions

Both the ISNUMBER function and the XMATCH function are only available in Microsoft 365.

Steps:

• In cell H6, enter the following formula:

• Press ENTER to return the output.
• Use the Fill Handle to AutoFill the formulas for the rest of the cells.

Method 5 – Combining ISTEXT & VLOOKUP Functions

Steps:

• In cell H6, enter the following formula:

• Press ENTER to return the output.

• Use the Fill Handle to AutoFill the formulas for the rest of the cells.

Method 6 – Using Conditional Formatting

In addition to the use of functions, we can use Conditional Formatting to compare addresses by highlighting the matched values.

Steps:

• Select the cell range. Here, we select two columns by selecting the first, holding the CTRL key, then selecting the second.

• Open the Home tab >> go to Conditional Formatting >> select Highlight Cells Rules >> select Duplicate Values.

A Duplicate Values dialog box will pop up.

• Select Duplicate.
• Select the colors to highlight duplicate values. For example, the Green Fill with Dark Green Text.

After clicking OK, the duplicate values in the two selected columns will be highlighted.

Method 7 – Using Conditional Formatting with Formula

We can use Conditional Formatting from the ribbon to compare addresses by using formulas.

Steps:

• Select the cell range, here column F.
• Open the Home tab >> go to Conditional Formatting >> select New Rule.

A New Formatting Rule dialog box will pop up.

• From Select a Rule Type select Use a formula to determine which cells to format.
• In Edit the Rule Description enter the following formula:

• Click Format and choose a color to fill the cells.

• Click OK.

The cells where the values are matched are filled with the selected format.

Method 8 – Using the LEFT Function

Steps:

• In cell H6, enter the following formula:

• Press ENTER.

The result of the address comparison between cells C6 and F6 is shown.

• Use the Fill Handle to AutoFill the formulas for the rest of the cells.

Method 9 – Using the SUBSTITUTE Function

We can use the SUBSTITUTE function combined with the LOWER function to compare addresses.

Steps:

• In cell H6, enter the following formula:

• Press ENTER.
• The result of comparing the addresses in cells C6 and F6 is shown.

• Use the Fill Handle to AutoFill the formulas for the rest of the cells.

In most cases you may need to adjust the formula based on the cell values.

Method 10 – Using VBA Code

Steps:

• Press ALT + F11 to open the VBA window.
• Go to Insert >> select Module.

A new Module will open.

• Enter the following code to compare two addresses:

Function AddressCompare(first_string As String, Second_string As String, _
                                   Comparing_Letters As Integer) As Double
    Dim int_character As Integer, Comparing_LettersMatch As Integer
    Dim n_Gram_List1 As String, n_Gram_List2 As String, n_letter_Gram As Variant
    Dim n_Gram_array1 As Variant
    For int_character = 1 To Len(first_string) - (Comparing_Letters - 1)
        If n_Gram_List1 <> "" Then n_Gram_List1 = n_Gram_List1 & ","
        n_Gram_List1 = n_Gram_List1 & Mid(first_string, int_character, Comparing_Letters)
    Next int_character
    For int_character = 1 To Len(Second_string) - (Comparing_Letters - 1)
        If n_Gram_List2 <> "" Then n_Gram_List2 = n_Gram_List2 & ","
        n_Gram_List2 = n_Gram_List2 & Mid(Second_string, int_character, Comparing_Letters)
    Next int_character
    n_Gram_array1 = Split(n_Gram_List1, ",")
    For Each n_letter_Gram In n_Gram_array1
        If InStr(1, n_Gram_List2, n_letter_Gram) Then
            Comparing_LettersMatch = Comparing_LettersMatch + 1
        End If
    Next n_letter_Gram
    AddressCompare = Comparing_LettersMatch / (UBound(n_Gram_array1) + 1)
End Function
			

• Save the code and go back to the worksheet.
• In cell H6, enter the following formula containing the function just created:

• Press ENTER to return the output.

The AddressCompare function will compare how many characters of a text string match, and return the result as a numeric value where 1 represents a 100% match.

• AutoFill up to cell H12.

Now we can use the IF function to represent these numeric values as Text.

• In cell I6, enter the following formula:

• Press ENTER.

The results of the address comparisons are displayed as text values, not numbers.

• AutoFill up to cell I12.

Method 11 – Using Equal (=) Operator

We can use the simple equal operator (=) to compare addresses when the addresses are side by side in a row.

Steps:

• In cell H6, enter the following formula:

• Press ENTER to return the output.

The equal operator will check whether both selected values are equal or not.

• Use Fill Handle to AutoFill the formulas for the rest of the cells.

Download to Practice Workbook

<< Go Back To Excel Compare Cells | Compare in Excel | Learn Excel