How to Compare Addresses in Excel (11 Possible Methods)

To perform any data analysis task comparing data in Excel is common. It is most required for banks to check the background of a customer depending on the provided addresses. It helps to make a decision whether to do any business or not with a particular customer. In this article, I’ve explained how to compare addresses in Excel. You will be able to find out if the same customer addresses belong to multiple banks or organizations or not.

How to Compare Addresses in Excel: 11 Methods

To make these methods more understandable and visible I’ve used a sample dataset of two banks having Full Name, Email_ID, and Address. Here the same person may have two accounts in two different banks.

1. Using IF to Compare Addresses

You can use the IF function to compare addresses.

Steps:

First, select any cell to place your resultant value. I selected cell H5.
Then, type the following formula in the selected cell or in the Formula Bar.
After that, press ENTER to get the output.

=IF(C6=F6,"Match","No Match")

Formula Explanation

Here, the IF function checks whether the value of the selected cell is equal or not equal.
If the value of selected cells is equal, then it will return Match as a result otherwise Not Match.

Later, you can use the Fill Handle to AutoFill the formulas for the rest of the cells.

2. Using IF & COUNTIF to Compare Addresses

You can use both the IF function and the COUNTIF function to compare addresses in Excel.

Steps:

Here, I will compare the Email_ID of two banks whether emails match each other or not.
To use these functions, select any cell to place your resultant value.
I selected the cell H6.
Next, type the following formula in the selected cell or in the Formula Bar.
Then, press ENTER to get the output.

=IF(COUNTIF($C$6:$C$12,F6)>0, "Match Found","No Match")

Formula Breakdown

COUNTIF($C$6:$C$12,F6)>0 → This is the logical statement. It will be TRUE if F6 occurs multiple times in the C6:C12
- Output: TRUE
IF(COUNTIF($C$6:$C$12,F6)>0, “Match Found”,”No Match”) → This becomes,
IF(TRUE, “Match Found”,”No Match”)
- Output: Match Found

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

3. Combine ISNUMBER & MATCH Functions

You can use both the ISNUMBER function and the MATCH function to compare addresses in Excel.

Steps:

Firstly, select any cell to place your resultant value.
I selected the cell H6.
Next, type the following formula in the selected cell or in the Formula Bar.
Then, press ENTER to get the output.

=ISNUMBER(MATCH(F6,$C$6:$C$12,0))

Formula Breakdown

MATCH(F6, $C$6:$C$12,0) → F6 cell as lookup_value and selected the cell range C6:C12 as lookup_array also given the match_type 0
- Output: 1
ISNUMBER(MATCH(F6, $C$6:$C$12,0)) → This becomes,
ISNUMBER(1)
- Output: TRUE

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

4. Merge ISNUMBER & XMATCH to Compare Addresses

You can use both the ISNUMBER function and the XMATCH function to compare addresses in Microsoft 365.

Steps:

Select any cell to place your resultant value.
I selected cell H6.
Next, type the following formula in the selected cell or in the Formula Bar.
Then, press ENTER to get the output.

=(ISNUMBER(XMATCH(F6,C6:C12)))

Formula Breakdown

XMATCH(F6, C6:C12,0) → F6 cell as lookup_value and selected the cell range C6:C12 as lookup_array also given the match_type 0.
- Output: 1
ISNUMBER(XMATCH(F6, C6:C12,0)) → This becomes,
ISNUMBER(1)
- Output: TRUE

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

5. Combine ISTEXT & VLOOKUP Functions

You can use both the ISTEXT function and the VLOOKUP function to compare addresses in Excel.

Steps:

To use these functions first, select any cell to place your resultant value.
I select the cell H6.
Second, type the following formula in the selected cell or in the Formula Bar.

=ISTEXT(VLOOKUP(F6,$B$6:$B$12,1,FALSE))

Then, press ENTER to get the output.

Formula Breakdown

VLOOKUP(F6,$B$6:$B$12,1,FALSE) → F6 cell as lookup_value and selected the cell range C6:C12 as table_array ,selected the col_index_num 1 then, selected range_lookup FALSE.
- Output: “2101, Waverley “
ISTEXT(VLOOKUP(F6,$B$6:$B$12,1,FALSE)) → This becomes,
ISTEXT(“2101, Waverley “)
- Output: TRUE

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

6. Using Conditional Formatting

You also can use Conditional Formatting to compare addresses by highlighting the matched values.

Steps:

Select the cell range. I selected two columns. For that, first, select any column before selecting another column, hold the CTRL key then select the column.

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

Now, a dialog box will pop up. From there select Duplicate then select the colors to highlight duplicate values.
I selected the Green Fill with Dark Green Text.

After clicking OK, it will highlight the duplicate values of two selected columns.

7. Apply Conditional Formatting with Formula

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

Steps:

Select the cell range. I selected the F.
Open the Home tab >> go to Conditional Formatting >> select New Rule.

A dialog box will pop up. From the Select a Rule Type select Use a formula to determine which cells to format.
After that, in the Edit the Rule Description type the following formula.

=ISNUMBER(MATCH(F6, $C$6:$C$12,0))

Now, from the Format choose the color of your choice to fill color in a cell.

Formula Breakdown

MATCH(F6, $C$6:$C$12,0) → F6 cell as lookup_value and selected the cell range C6:C12 as lookup_array also given the match_type 0
- Output: 1
ISNUMBER(MATCH(F6, $C$6:$C$12,0)) → This becomes,
ISNUMBER(1)
- Output: TRUE

Finally, click OK. As a result, it will show the cell filled with the selected format where the values are matched.

8. Using LEFT Functions to Compare Addresses

You can use the LEFT function to compare addresses.

Steps:

Firstly, select any cell to place your resultant value.
I selected cell H6.
Secondly, type the formula in the selected cell or the Formula Bar.
Press the ENTER Now, it will show the result of compared addresses of C6 and F6 cells.

=LEFT(C6,5)=LEFT(F6,5)

Formula Explanation

Here, the LEFT function checks whether the character of the selected cell is equal or not.
I selected the C6 cell as text and gave num_chars 5.
If the 5 characters from the left of selected cell C6 match with the selected cell F6 then it will return TRUE as a result otherwise it will show FALSE

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

9. Applying SUBSTITUTE Functions to Compare Addresses

You can use the SUBSTITUTE function and the LOWER function to compare addresses.

Steps:

To use these functions, select any cell to place your resultant value.
I selected cell H6.
Now, type the following formula in the selected cell or in the Formula Bar.
Press the ENTER Now, it will show the result of compared addresses of C6 and F6 cells.

=(SUBSTITUTE(SUBSTITUTE(LOWER(C6)," 2101"," 2")," Waverly"," Wa"))=SUBSTITUTE(SUBSTITUTE(LOWER(F6)," 2101"," 2")," Waverly"," Wa")

Formula Breakdown

LOWER(C6) → This is the text for the SUBSTITUTE function.
- Output: “2101, waverley “
SUBSTITUTE(LOWER(C6),” 2101″,” 2″) → This becomes,
SUBSTITUTE(“2101, waverley”,” 2101″,” 2″)
(SUBSTITUTE(SUBSTITUTE(LOWER(C6),” 2101″,” 2″),” Waverly”,” Wa”))=SUBSTITUTE(SUBSTITUTE(LOWER(F6),” 2101″,” 2″),” Waverly”,” Wa”) → This will compare the two addresses in C6 and F6.
- Output: TRUE

Use Fill Handle to AutoFill the formulas for the rest of the cells. (In most cases you may need to adjust the formula based on cell value)

10. Using VBA Code to Compare Addresses

You also can use the VBA to compare addresses.

Steps:

Press ALT + F11 to open the VBA window.
Then go to Insert >> select Module.

A new Module will be opened.
Then, write the 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

VBA Code Breakdown

I have created a new function named “AddressCompare”. The arguments are first_string, Second_string, and Comparing_Letters.
Then, I defined some variables named int_character, Comparing_LettersMatch, n_Gram_List1, n_Gram_List2, n_letter_Gram, and n_Gram_array1
After that, I used For_Loop.

Save the code and go back to the worksheet.
First, select any cell to place your resultant value.
I selected the cell H6.
Then, type the following formula in the selected cell or in the Formula Bar by using the function just created.

=AddressCompare(C6,F6,2)

Finally, press ENTER to get the output.

The AddressCompare function will compare how many characters of a text string match then it will return the result as a numeric value here,1 will represent a 100% match.

Then AutoFill up to H12.

Now you can use the IF function to represent these numeric values as Text.
Again, select any cell to place your resultant value.
I selected cell I6.
Now, type the following formula in the selected cell or in the Formula Bar.
In the end, press ENTER.
Now, it will show the result of compared addresses as a text value, not as numbers.

=IF(AddressCompare(C6, F6, 2)>0.5, "Full Match", "No Match")

Formula Explanation

Here, in the IF function, used a condition to check the value of matched values the condition is greater than 0.5
If the value of selected cells is greater than 0.5 then it will return Full Match as a result otherwise No Match.

Then AutoFill up to I12.

11. Using Equal (=) Operator

You can use the simple equal operator (=) when addresses remain side by side in a row to compare addresses in Excel.

Steps:

First, select any cell to place your resultant value.
I selected the cell H6.
Then, type the following formula in the selected cell or in the Formula Bar.

=C6=F6

Then, press ENTER to get the output.

Here, 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

Compare Address.xlsm

Conclusion

In this article, you will find 11 ways to compare addresses in Excel. These methods will be useful for you whenever you want to compare addresses. In case you have any confusion or question regarding these methods you may comment down below.

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

Get FREE Advanced Excel Exercises with Solutions!

2 Comments

Abby Shuler May 17, 2022 at 2:06 AM

Thanks for this VBA function; I added it in as a module, doublechecked my syntax, and still receive a #Name error. I modified my address lists to more closely match the examples and refreshed (running this in xlsm with macros enabled); still receive #NAME error. What am I doing wrong? Also, for a list with more than two variables (more like “5400 Menaul NE, Albuquerque, NM” vs “5400 Menaul Blvd NE, Albuquerque, NM”) would the last variable be 3 instead of 2?
- Reply
  Afia Aug 22, 2022 at 11:48 AM
  
  Dear ABBY SHULER
  Thank you for your comment. Here, for your first problem, to get rid of the #NAME error, you can copy the following VBA code in a new Excel Workbook. The uploaded Excel file sometimes show problem while running on a different computer, therefore, when you will copy the code to a new workbook, the #NAME error will be solved.
  
  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
  
  After that, save the code and go back to your worksheet. I hope the #NAME error will be gone now.
  
  For your second query, you can use 3 instead of 2. Using 3 will compare 3 letters at a time, and therefore, the result will decrease the match percentage between two addresses.
  When you will use 2, it will compare 2 letters at a time, and therefore, the match percentage between two addresses will be higher.
  Let me show you that elaborately.
  When we use =AddressCompare(C5, F5, 2) in cell E3, the result becomes 1, which indicates the Exact Match.
  However, the two addresses are not the same, therefore, using 2 does not give an accurate result.
  
  On the other hand, when we use the formula =AddressCompare(C5, F5, 3) in cell E3, the result becomes 0.970588235, which does not indicate the Exact Match. Rather, it suggests that there is some dissimilarity between the two addresses.
  Therefore, using 3 is wise in your case.
  
  Here, another thing must be noted, for your address match, you have to set your own creation while using the IF function.
  Let me elaborate on this.
  Here, in cell F3, we type the following formula.
  IF(AddressCompare(C5, F5, 3)>0.5, "Full Match", "No Match")
  Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.5, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.5, the IF function returns “No Match“.
  Therefore, in cell F3 the result is “Full Match“.
  However, there is some dissimilarity between the two addresses. Hence, the result in cell F3 is not accurate.
  
  To get an accurate result, we will type the following formula in cell F3.
  
  IF(AddressCompare(C5, F5, 3)>0.99, "Full Match", "No Match")
  Here, in this formula, we give the criteria, that when AddressCompare(C5, F5, 2) is greater than 0.99, the IF function returns “Full Match“. However, when AddressCompare(C5, F5, 2) is not greater than 0.99, the IF function returns “No Match“.
  Therefore, in cell F3 the result is “No Match“.
  This is the correct result.
  
  I really hope that you get your answer, and that you can solve your problems.
  If you face any problem, you can always let us know.
  
  Regards,
  Afia Aziz Kona

How to Compare Addresses in Excel (11 Possible Methods)

How to Compare Addresses in Excel: 11 Methods

1. Using IF to Compare Addresses

2. Using IF & COUNTIF to Compare Addresses

3. Combine ISNUMBER & MATCH Functions

4. Merge ISNUMBER & XMATCH to Compare Addresses

5. Combine ISTEXT & VLOOKUP Functions

6. Using Conditional Formatting

7. Apply Conditional Formatting with Formula

8. Using LEFT Functions to Compare Addresses

9. Applying SUBSTITUTE Functions to Compare Addresses

10. Using VBA Code to Compare Addresses

11. Using Equal (=) Operator

Conclusion

Leave a reply Cancel reply