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:
- 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.”
What are we looking up? “Yes.” So tell Excel exactly that.
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).
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.
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.
Look at how the results have now changed…
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)…
And pulling down as far as you can.
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.
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!