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.
Lookup multiple criteria
- Unfortunately performing multiple criteria lookups are not easy in Excel
- My chosen method uses INDEX/MATCH with array formulas
- It will also utilise a quirk of Excel which states TRUE*TRUE = 1
- Here are a few more ways of looking up multiple criteria
1 Create a MATCH that looks for the value of 1 from the product of our two criteria
2 Put this MATCH as the row_num input for an INDEX with one column
3 Copy/Paste for remaining cells
CTRL + F3: Open the Name Manager dialog box
ALT + N: Create a new Name
F2: Jump inside formula
ALT + E , S , F: Paste formulas
CTRL + SHIFT + →: Select all cells within data region
SHIFT + →: Select an additional cell
CTRL + →: Move to the last cell in the data region
CTRL + Pg Down: Move to sheet on the right
CTRL + Pg Up: Move to sheet on the left
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 data set, and in this column, they use an AND statement to check the multiple criteria.
If the criteria within the AND statement holds true, then the corresponding company name is returned back to the output cell.
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 rows to the data set 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 sales person array, Ctrl + F3, Alt + N, and I'll call it SALES_PEOPLE.
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 sales people.
And I'll check that this equals to our selected rep.
I'll then close the bracket and then the match type will be 0 for exact match, I'll 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 re-enter the formula, and then we'll select everything by holding the Shift button.
We'll then copy with Ctrl + C, and Esc.
We'll then return to our output cell, enter the equation with F2, replace D18 with paste, and then Ctrl + Shift + Enter to finish.
We can now copy this formula for the remaining 4 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 our MATCH function.
Index-match is only one of the 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.