Wednesday, May 18, 2011

Matching Two Databases

Many of us are faced with matching data that is contained in two or more databases. It may be matching customers with product choices, insurance policyholders with claims, and any great number of things. What is often the case is that there will be matches only for some of the data, as is the case in our example today of matching Policyholder Database with a Claims Database.

In our example, we will use the MATCH function, along with an error-handling function, IFERROR.

Here is what you do:

1) In the Claims database, Name your range of address in the database "ClaimsAddresses” (or whatever suits your fancy).
2) Create a "Match" range of addresses on the Policies database
3) Put the following formula in in the first cell of the new "Match" range:
=IFERROR(MATCH($M2, ClaimsAddress, FALSE), "")
4) Fill the formula to the bottom of the Match range with a double-click
5) Sort the Policy database according to the Match range

Voila! The result is a list of policies that have a claim that are indexed to the Claims database.

Try this out! Seriously, this can save you a lot of work when you have multiple databases.  All the best…

No comments: