Shamima Sultana
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.
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.
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 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.
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
- 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 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 #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