How to Compare Addresses in Excel (11 Easy 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.

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.

Sample Dataset to Compare Addresses

Download to Practice

11 Ways to Compare Addresses in Excel

1. Using IF to Compare Addresses

You can use the IF function to compare addresses.

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

=IF(C5=F5,"Match","No match")

Using IF to Compare Addresses

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.

Finally, press ENTER.
Now, it will show the result of compared addresses of C5 and F5 cells.

Using IF to Compare Addresses

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

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.

Here, I will compare the Email_ID of two banks whether emails match with each other or not.

To use these both functions, select any cell to place your resultant value
➤I selected the cell H5
Next, type the following formula in the selected cell or in the Formula Bar.

=IF(COUNTIF($C$5:$C$11,F5)>0, "Match Found","No Match")

Using IF & COUNTIF to Compare Addresses

Here, in the COUNTIF function selected the F5 cell as criteria now this function will count how many times the value of F5 cell occurred in the selected range C5:C11. If the value of the selected cell is greater than 0 then the IF function returns Match Found as a result otherwise No Match.

Press ENTER. Now, it will show the result of compared addresses of F5 in the selected cell range.

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

Using IF & COUNTIF to Compare Addresses

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

3. Using ISNUMBER & MATCH 

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

Firstly, select any cell to place your resultant value
➤I selected the cell H5
Next, type the following formula in the selected cell or in the Formula Bar.

=ISNUMBER(MATCH(F5, $C$5:$C$11,0))

Using ISNUMBER & MATCH to Compare Addresses

Here, in the MATCH function selected the F5 cell as lookup_value and selected the cell range C5:C11 as lookup_array also given the match_type 0. Now, this function will look for the value of the F5 cell in the cell range of C5:C11. If the value of the selected cell matches with the values of the selected cell range, then it will return TRUE as a result otherwise FALSE. The ISNUMBER function will ignore the error values.

In the end, press ENTER. Then, it will show the result of compared addresses of F5 in the selected cell range.

Using ISNUMBER & MATCH to Compare Addresses

Here the F5 lookup value matches with the selected range C5:C11 so that it returns TRUE as a result.

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

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

4. Using ISNUMBER & XMATCH to Compare Addresses

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

Now, select any cell to place your resultant value
➤I selected the cell H5
Next, type the following formula in the selected cell or in the Formula Bar.

=(ISNUMBER(XMATCH(F5,C5:C11)))

Using ISNUMBER & XMATCH to Compare Addresses

Present, in the XMATCH function, selected the F5 cell as lookup_value and selected the cell range C5:C11 as lookup_array. Now, this function will look for the value of the F5 cell in the cell range of C5:C11. If the value of the selected cell matches with the values of the selected cell range, then it will return TRUE as a result otherwise FALSE. The ISNUMBER function will ignore the error values.

Finally, press ENTER. Then, it will show the result of compared addresses of F5 in the selected cell range.

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

Using ISNUMBER & XMATCH to Compare Addresses

5. Using ISTEXT & VLOOKUP

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

To use these functions first, select any cell to place your resultant value

➤I selected the cell H5
Second, type the following formula in the selected cell or in the Formula Bar.

=ISTEXT(VLOOKUP(F5,$B$5:$B$11,1,FALSE))

Using ISTEXT & VLOOKUP to Compress Addresses

Here, in the VLOOKUP function selected the F5 cell as lookup_value and selected the cell range C5:C11 as table_array, selected the col_index_num 1 then, selected range_lookup FALSE.

Now, this function will look for the value of the F5 cell in the cell range of C5:C11. If the value of the selected cell matches with the values of the selected cell range, then it will return TRUE as a result otherwise FALSE. The ISTEXT function will ignore the error values.

In the end, press ENTER. Then, it will show the result of compared addresses of F5 in the selected cell range.

Using ISTEXT & VLOOKUP to Compress Addresses

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

6. Using Conditional Formatting

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

➤ 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

Using Conditional Formatting to Compare Addresses

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

Using Conditional Formatting to Compare Addresses

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

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

7. Conditional Formatting with Formula

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

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

Conditional Formatting with Formula to Compare Addresses

➤ 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(F5, $C$5:$C$11,0))

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

Conditional Formatting with Formula to Compare Addresses

Here, I selected the F5 cell as lookup_value and selected cell range C5:C11 as the lookup_array also given the match_type 0. Now, this function will look for the value of the F5 cell in the cell range of C5:C11.

If the value of the selected cell matches with the values of the selected cell range, then it will Fill color to that cell. The ISNUMBER function will ignore the error values.

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

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

8. Using LEFT to Compare Addresses

You can use the LEFT function to compare addresses.

Firstly, select any cell to place your resultant value
➤I selected the cell H5
Secondly, type the following formula in the selected cell or in the Formula Bar.

=LEFT(C5,5)=LEFT(F5,5)

Using LEFT to Compare Addresses

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

Press the ENTER key. Now, it will show the result of compared addresses of C5 and F5 cells.

Using LEFT to Compare Addresses

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

Using LEFT to Compare Addresses

9. Using SUBSTITUTE to Compare Addresses

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

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

=(SUBSTITUTE(SUBSTITUTE(LOWER(C7)," 2101"," 2")," Waverly"," Wa"))=SUBSTITUTE(SUBSTITUTE(LOWER(F7)," 2101"," 2")," Waverly"," Wa")

Using SUBSTITUTE to Compare addresses

Here, the LOWER function will convert the selected text string to all lowercase letters. Then two SUBSTITUTE functions will check whether the character of the selected cell is equal or not.

Provided the text string along with old_text and new_text. If both the values of the cell match after the substitution, then it will return TRUE as a result otherwise FALSE.

Press the ENTER key. Now, it will show the result of compared addresses of C5 and F5 cells.

Using SUBSTITUTE to Compare addresses

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

10. Using VBA to Compare Addresses

You also can use the VBA to compare addresses.

First, open the Developer tab >> then select Visual Basic

Using VBA to Compare Addresses

It will open a new window of Microsoft Visual Basic for Applications.

Here, from Insert select Module.

A new Module will be opened.

Using VBA to Compare Addresses

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

Save the code and go back to the worksheet.

First, select any cell to place your resultant value
➤I selected the cell H5
Then, type the following formula in the selected cell or in the Formula Bar by using the function just created.

=AddressCompare(C5,F5,2)

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

Finally, press ENTER. As the characters of two selected cells match 100% so it returns 1.

Using VBA to Compare Addresses

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

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 I5
Now, type the following formula in the selected cell or in the Formula Bar

=IF(AddressCompare(C5, F5, 2)>0.5, "Full Match", "No Match")

Using VBA to Compare Addresses

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.

In the end, press ENTER.
Now, it will show the result of compared addresses as a text value, not as numbers.

Using VBA to Compare Addresses

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

Using VBA to Compare Addresses

11. Using Equal (=) Operator

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

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

=C5=F5

Using Equal (=) Operator to Compare Addresses

Here, the equal operator will check both selected values are equal or not.

Press the ENTER key. Now, it will show the result of compared addresses of C5 and F5 cells.

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

Using Equal (=) Operator to Compare Addresses

Practice Section

I’ve provided two extra practice sheets in the worksheet so that you can practice these explained methods to compare addresses.

Practice Sheet to Compare Addresses

Conclusion

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