Disclosure: This post may contain affiliate links, meaning when you click the links and make a purchase, we receive a commission.

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


Download to Practice Workbook


11 Possible Ways to Compare Addresses in Excel

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.

Dataset


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")

IF how to compare addresses in excel (2BU)

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.

AutoFill

Read More: Return YES If 2 Cells Match in Excel (10 Methods)


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")

COUNTIF how to compare addresses in excel

Formula Breakdown

  • COUNTIF($C$6:$C$12,F6)>0This 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.

compare addresses in excel

Read More: Excel Compare Two Strings for Similarity (3 Easy Ways)


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))

ISNUMBER and MATCH

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.

ISNUMBER and MATCH

Read More: Compare Two Cells in Excel and Return TRUE or FALSE (5 Quick Ways)


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)))

how to compare addresses in excel

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.

AutoFill


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.

VLOOKUP ISTEXT

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.

AutoFill


6. Using Conditional Formatting

You also can use Conditional Formatting from the ribbon 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.

Apply Conditional Formatting

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

Apply Conditional Formatting

  • 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.

Color choice

  • After clicking It will highlight the duplicate values of two selected columns.

Output

Read More: How to Compare Text in Excel and Highlight Differences (8 Quick Ways)


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.

how to compare addresses in excel

  • 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.

Color choice

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.

Output how to compare addresses in excel

Read More: How to Compare Two Cells and Change Color in Excel (2 Ways)


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)

LEFT Function how to compare addresses in excel

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 matches 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.

Output


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")

SUBSTITUTE Function

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)

SUBSTITUTE Function


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.

VBA how to compare addresses in excel

  • 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 how to compare addresses in excel

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.

VBA how to compare addresses in excel

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.

VBA how to compare addresses in excel

  • 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")

VBA how to compare addresses in excel

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.

VBA how to compare addresses in excel


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.

Equal Operator

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.

Equal Operator


Conclusion

In this article, you will find 11 ways on how 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.


Further Readings

Shamima

Shamima

Hello! Welcome to my Profile. I am Shamima Sultana Rita. Currently, I am working and doing research on Microsoft Excel and here I will be posting articles related to this. My last educational degree was BSc and my program was Computer Science and Engineering from East West University-Bangladesh. I am a Computer Science graduate with a great interest in research and development. I love to learn new things. Data Analysis is one of my favorite fields as I love to extract patterns based on problems. Here, I'm trying to provide enriched quality content regarding Excel basics to Advanced. Always try to gather knowledge from various sources and try to make innovative solutions.

2 Comments
  1. 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?

    • 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.

      null

      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

Leave a reply

ExcelDemy
Logo