Extracting Records from a Database: Part One

When you’re working with a large database with many variables, it can be tricky if you ever need just “certain” records from the database. The following are some examples of how you can make Excel work for you if you are trying to extract data from a large database.

Part One: The Basics

For the sake of these examples, let’s say that we work for a health-insurance company, and our job is to deal with people renewing their coverage every year. Our database may look something like this:

1 excel index match example

  • Customer (Column A) = first and last name of customer
  • Coverage (Column B) = what kind of coverage the customer has
    • Can be personal or company coverage
    • Can be basic or premium coverage
  • Plan Expiration (Column C) = date that their current coverage will expire
  • Renewal Reminder (Column D) = whether or not the customer wants a reminder to renew their plan
    • Can be yes, no, or “auto”, which means the plan is already set to automatically renew

Let’s start simple. Pretend that you have been asked to generate a list of customers who have asked for a reminder to renew their plan (i.e., Column D = “Yes”). Here’s how you could easily do that.

1. Define your search criteria.

You know that you’re looking for customers with a “yes” in Column D. I know that you’re looking for a “yes” in Column D. The key to making Excel do the work is telling excel that you are looking for a “Yes.”

2 excel index match example

Give yourself an area to generate your list of customers. In the same Excel sheet, to the side, create something that looks like this.

What are we looking up? “Yes.” So tell Excel exactly that.

3 excel index match example

You have now “defined” your search criteria.

2. Enter that big, scary function.

Yeah, I’m not going to lie. This function is scary. So for now, I’m just going to put it out there. If you want an explanation of how the function works and why we are using it, go to the very end of this post.

First, you have to enter this function in the cell where you want to start your list of results (in this case, G2).

=IF(ISERROR(INDEX($A$1:$D$101,SMALL(IF($D$1:$D$101=$G$1,ROW($D$1:$D$101)),ROW(1:1)),1)),””,INDEX($A$1:$D$101,SMALL(IF($D$1:$D$101=$G$1,ROW($D$1:$D$101)),ROW(1:1)),1))

4 excel index match example

This is what that big scary function looks like in the function bar.

 

However, if you’re paying attention to your data (and hopefully you are), you will notice that something goes awry as soon as you enter this function.

6 excel index match example

You’ll see that your first match (Jaidyn Huff) is the first name in the database and that the customer’s response in column D was not “yes.”

Don’t panic yet! This is totally normal, because you are not done entering the function.

What we are using is called an array command, and anytime you enter an array function, you have to press CTRL+SHIFT+ENTER. And for those of you just skimming, I will reiterate…

 

SUPER INCREDIBLY IMPORTANT STEP!!!
DO NOT SKIP THIS!!!

3. Type the function. Press CTRL+SHIFT+ENTER.

7 excel index match example

Not you’ll see that your function is enclosed with “{ }”, which means you did it right. Excel is recognizing this as an array function.

 

Look at how the results have now changed…

9 excel index match example

The first “match” is Rayna Fields; if you look at your database really quickly, you can see that she is indeed the first customer in the database (first meaning “from top to bottom in the list”) who does has a “yes” response.

Now it’s all pretty straightforward.

 

4. Drag the function down to copy it.

“Drag” the function down by grabbing the corner of the cell (below)…

10 excel index match example

And pulling down as far as you can.

11 excel index match example

When you “release” the mouse, the cells will generate your list of matches.

Note: Keep pulling the function down until it returns blank cells. Blank cells = no more matches in the database.

12 excel index match example

You now have a list of clients in your database who have asked for a renewal reminder….Go you!


 

I created this video to help explain the “big scary formula”. It’s about 15 minutes are super dry…but it does break down the entire function. So if you have the time or interest, here you go! 

 

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s