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.

**Table of Contents**hide

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

**Here the same person may have two accounts in two different banks.**

*Address.***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.

**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
of two banks whether emails match each other or not.*Email_ID* - 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.**

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

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

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

__Formula Breakdown__

**X****MATCH(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** 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.

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

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

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

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

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

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#NAMEerror, you can copy the followingVBA codein anew 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#NAMEerror 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 codeand go back to yourworksheet. I hope the #NAME error will be gone now.For your

second query, you can use3instead of2. Using3will compare3 lettersat a time, and therefore, the result willdecrease the match percentagebetween two addresses.When you will use

2, it will compare2 lettersat a time, and therefore, thematch percentagebetween two addresses will behigher.Let me show you that elaborately.

When we use

in cell`=AddressCompare(C5, F5, 2)`

E3, the result becomes1, which indicates theExact Match.However, the

two addresses are not the same, therefore, using2 does not give an accurate result.On the other hand, when we use the formula

in cell`=AddressCompare(C5, F5, 3)`

E3, the result becomes0.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 than0.5,the IF functionreturns “Full Match“. However, whenAddressCompare(C5, F5, 2)is not greater than0.5,the IF functionreturns “No Match“.Therefore, in cell

F3the result is “Full Match“.However, there is some dissimilarity between the two addresses. Hence, the result in cell

F3isnot 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 than0.99,the IF functionreturns “Full Match“. However, whenAddressCompare(C5, F5, 2)is not greater than0.99,the IF functionreturns “No Match“.Therefore, in cell

F3the 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