Sign in or start a free trial to avail of this feature.
7. Lookup Multiple Criteria
Learn how to lookup multiple criteria in this lesson by combining INDEX and MATCH with array formulas.
Performing Multiple Lookups (00:04)
Our aim in this lesson is to find the top 5 customers for each sales person. This involves looking up a revenue amount and a sales person. Excel doesn’t have an inbuilt function for performing multiple lookups like this.
An approach that people sometimes take is to create an extra TRUE/FALSE column in the dataset that performs the two lookups using an AND statement. This approach is problematic because it adds multiple redundant columns to the dataset, hampering readability.
Creating Our Formula (01:37)
A better approach combines INDEX and MATCH with an array function. It can create a long formula so it’s best to make sure your arrays are all named before starting. In Excel, multiplying two TRUE statements returns a value of 1. So to lookup two criteria, we can multiply the criteria together, and search for a value of 1.
In this case, our two criteria relate to revenue and salespeople. We create a match function where the lookup value is 1, and the lookup array is the two criteria multiplied together. The resulting formula is an array formula. We combine it with an INDEX function to find the companies corresponding to the revenue amounts from the last lesson.
When we adjust the sales person in the search panel, the formula updates automatically to show the top 5 companies by revenue for the sales person.
Here are other ways of looking up multiple criteria.
In the previous lesson, we learnt how to rank data records using the functions large and small. In this lesson, we learn how to create a lookup containing multiple criteria. So far in this course, we've only performed a lookup on one value, be it customer name, sales rep name, or state. However, we'll often need to lookup multiple criteria at the same time, and unfortunately, Excel does not make this an easy task. One way that people often use is to create a new column next to the dataset. And in this column they use an AND statement to check the multiple criteria. If the criteria within the AND statement holds true, we will be able to identify the relevant company in question.
This approach has one big problem, you have to change the formula, or create a new column for each lookup, which can be very time consuming if you want to perform a lot of lookups in your analysis. It also adds a number of redundant columns to the dataset, and hampers readability. So let's ignore this approach, and create a single formula that will perform our task. Given that this is likely to be a long formula, let's name some of our arrays before we get started. So I'll select the salesperson array, Ctrl + F3, Alt + N, and I'll call it Salespeople.
I'll also select the revenue column, and name it REVENUES.
And I'll make sure that the company name column is called Company_Names. Now we can return to the search panel, and begin our task. The approach we're going to take will be to combine Index and Match with an array function. Match is going to utilize an interesting quirk of Excel, which gives a value of 1, when a series of true statements are multiplied together. So for our Match function, I'm going to check for both criteria in every row by multiplying the criteria results. On the correct row, the answer should be 1. So I'll begin by writing a Match function, where the lookup value will be 1, and the first part of our array will be Revenues.
And I'll check if the revenue is equal to our first entry.
I'll then multiply this by our second lookup array, which is going to be Salespeople.
And I'll check that this equals to our selected rep.
I'll then close the bracket, and then the match type would be 0, for exact match, and then close the last bracket, and Ctrl, Shift, Enter to perform the array function. And the resulting answer of row 126 tells us where our company name can be found. We'll now use the Index function to pull the company name from this row.
So we'll go up to our output cell and write =index, we'll select the array, which is going to be company names, then we'll select the row number, which is 126, and we'll close the bracket and press Enter. And this gives us Soar Airlines, which is the correct answer. To combine this into one formula, let's go back to our 126, F2 to renter the formula, and then we'll select everything by holding the Shift button.
We'll then copy with Ctrl+C, and Escape.
We'll then return to our output cell, enter the equation with F2, place D18 with paste, and then Ctrl, Shift, Enter to finish.
We can now copy this formula for the remaining four customers.
If we now change the rep, you can see our company's updating accordingly.
While this formula does take a bit of effort to write, you can now perform multiple criteria lookups for any rep name or revenue total. If you want to check more than two criteria, you simply add another array to your match function. Index Match is only one of a few ways of performing a multiple criteria lookup. In the show notes, I'll link to an article that shows you a couple of other ways of doing this. As an exercise, try to find the top customer name for each state in the panel on the right. As always, I'll include my answer in the after file, below the video.