11 Approaches to Compare Addresses in Excel

In this lesson, I’ll demonstrate 11 effective approaches to compare addresses in Excel. Moreover, these techniques will enable you to solve the problem that led you here. However, you will master several crucial Excel features and functions during this article, which will come in extremely handy for any assignment using Excel.

📁 Download Excel File

The sample worksheet that was used during the discussion is available to download for free right here.

Learn to Compare Addresses in Excel with These 11 Useful Techniques

Here, in this article, we will learn how to compare addresses in Excel using different approaches. To be exact, I’ve provided a total of 11 methods down below.

We are going to compare Addresses using the built-in feature of Excel here which is Conditional Formatting. In this approach, we format cells as a product of the result.

Let’s say we have a dataset that has the list of customers and their addresses for Two Banks. Now we will determine which addresses are common in both lists.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select the columns having addresses you want to compare by selecting pressing Ctrl.

• After that go to Home Tab. On the right side of the ribbon, you’ll find an option named Conditional Formatting. Now choose Highlight Cells Rules and then Duplicate Values like the picture below.

• A new window will pop up. Now select Yellow Fill with Dark Yellow Text in the values with the option and then click OK.

• After that, you will see the following results.

In this section, we will be using the conditional formatting tool with a formula to compare E- mail addresses in Excel.

⬇️⬇️ STEPS ⬇️⬇️

• First, Select the whole column you want to check for similarities.

• After that go to Home Tab. On the right side of the ribbon, you’ll find an option named Conditional Formatting. Then select New Rule like the picture below.

• Now a new window will appear copy the following formula for comparing addresses. Change the Preview Format as per your choice.

• Once you have done you’ll see the following results.

📕 Read More: 6 Easy Methods to Compare Two Strings in Excel for Similarity

Our goal here is to learn how to compare addresses in Excel. Now in this section, we are going to learn how to use different functions to make effective formulas to compare addresses. We will see the use of some basic functions such as  IF, COUNTIF, ISNUMBER, MATCH, XMATCH, ISTEXT, VLOOKUP SUBSTITUTE, LOWER, and LEFT Functions.

Here, we will be comparing the addresses of the customers from two different Banks using the Equal Operator. It is a very straightforward process we are just comparing cells that are of the same row for this method.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, copy the following formula to a cell.

• Press Enter and copy down the formula along the column to get the results as TRUE/FALSE.

Now we will be using a formula by the IF Function to find if the customer details for the two Banks’ data are same or not. It’s a straightforward method that just compares two cells in a row.

⬇️⬇️ STEPS ⬇️⬇️

• First, select a cell and copy the following formula I’ve made here for you.

• Press Enter and copy down the formula along the column for all the results.

Now we are going to use the COUNTIF Function to compare addresses in excel. This function counts the number of cells that meets certain criteria. We are going to use the IF function to show results based on our criteria.

⬇️⬇️ STEPS ⬇️⬇️

• Select and copy the following formula here I’ve prepared for you.

• Press Enter and copy down the formula along the column for all the results.

📕 Read More: How to Compare Two Cells in Different Sheets with Excel Formula

This time, we will demonstrate an approach using ISNUMBER and MATCH functions. Here MATCH function searches for a specific value and the ISNUMBER function tells whether it’s a number or not. Thus the answers will be TRUE/ FALSE

⬇️⬇️ STEPS ⬇️⬇️

• Firstly select and copy the formula I’ve given below here.

• Press Enter and copy down the formula along the column for all the results.

This method is quite similar to the past method which was using  ISNUMBER and MATCH. Here, we will be using XMATCH instead of the MATCH function.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select and copy the following formula I’ve provided here.

• Press Enter and copy down the formula along the column for all the results.

Here we are going to use ISTEXT and VLOOKUP functions to compare the addresses of the two Banks’ customers. Here VLOOKUP searches for the value we command it and ISTEXT checks whether the value is TEXT or not.

⬇️⬇️ STEPS ⬇️⬇️

• First, select and copy the following formula I’ve provided here.

• Press Enter and copy down the formula along the column for all the results.

Here we are going to use the LEFT function to compare between addresses. This function returns the leftmost value of a cell. If we use a comma then it can return values that have specific numbered characters. For instance, the formula used here is checking if a 5-char word of cell C7 and a 5-char word of cell F7 are equal or not.

⬇️⬇️ STEPS ⬇️⬇️

• First, select and copy the following formula I’ve provided here.

• Press Enter and copy down the formula along the column for all the results.

A different approach to compare addresses using SUBSTITUTE and LOWER functions.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, select and copy the following formula I’ve provided here.

• Press Enter and copy down the formula along the column for all the results.

We can always use VBA codes to solve any kind of problem. VBA is an advanced professional feature of Microsoft Excel. As we have learned all other possible methods except this one.

⬇️⬇️ STEPS ⬇️⬇️

• Firstly, you have to enable the Developer Tab in your Excel to use VBA. Then Go to the Visual Basic option from the Developer Tab of the ribbon.

• After that, a window will pop up, go to Module from the Insert option like the picture below.

• Copy the following VBA Code in the Module.

Function MatchAdd(address_one As String, address_two As String, _
    let_count As Integer) As Double
    Dim int_count As Integer, let_match_num As Integer
    Dim FList As String, SList As String, LettList As Variant
    Dim LettArray As Variant
    For int_count = 1 To Len(address_one) - (let_count - 1)
        If FList <> "" Then FList = FList & ","
        FList = FList & Mid(address_one, int_count, let_count)
    Next int_count
    For int_count = 1 To Len(address_two) - (let_count - 1)
        If SList <> "" Then SList = SList & ","
        SList = SList & Mid(address_two, int_count, let_count)
    Next int_count
    LettArray = Split(FList, ",")
    For Each LettList In LettArray
        If InStr(1, SList, LettList) Then
            let_match_num = let_match_num + 1
        End If
    Next LettList
    MatchAdd = let_match_num / (UBound(LettArray) + 1)
End Function

• Now after saving the code we have successfully created a custom function that we can use to compare addresses as it finds the percentage of strings and characters matched on a scale of 1.

• After copying the custom function along the column. now we will add some conditions to it according to its matching ratio to get the comparison.

• Press Enter and copy down the formula along the column for all the results.

📄 Important Notes

🖊️  Be careful while using the code as there are a lot of terms and if you lose concentration the code may be incorrect.

🖊️  Use shortcuts to reduce time, and use F4 for absolute value.

🖊️  While writing formulas carefully see the suggestions and don’t forget the commas and parentheses.

🖊️  Be careful while defining criteria to be accurate as the dataset.

🖊️  If you download a macro-enabled Excel file don’t forget to unblock the file from the properties menu.

📝 Takeaways from This Article

📌  Use of  IF, COUNTIF, ISNUMBER, MATCH, XMATCH, ISTEXT, VLOOKUP SUBSTITUTE, LOWER and LEFT functions.

📌  How to Compare both E-mail Addresses and Living Addresses

📌  Creating formulas using multiple functions.

📌  Using VBA codes to customize functions.

📌  How to define a new function.

📌  Conditional formatting and its usage to sort things out.

📌  Conditional formatting defines any formula.

Conclusion

Firstly, I hope you were able to use the techniques I demonstrated in this how-to compare addresses in the Excel lesson. As you can see, there are a lot of options on how to do this. However, decide deliberately on the approach that best addresses your circumstance. Most importantly I advise repeating the steps if you become confused in any of the steps if you get stuck. Practice on your own after looking at the Excel file in the practice workbook, I’ve provided above because practice makes a man perfect. Above all, I earnestly hope you can use it. Please share your experience with us in the comments about how your experience was throughout the article and what we need to improve or add to make things better for you folks. However, if you have any problem regarding Excel spit it in the comment box. The Excelden crew is always available to answer your questions. In conclusion, keep educating yourself by reading. For more Excel-related articles please visit our website Excelden.com.

Related Article

• 5 Steps to Do Statistical Comparison of Two Data Sets in Excel