How to do an Excel lookup with multiple criteria in columns, with the INDEX and MATCH functions. Get the sample file, then watch the step-by-step video, or follow the written instructions.
This video shows how the INDEX and MATCH functions work together, with one criterion. Then, the formula is changed, to work with multiple criteria.
To follow along with the video, download the sample file. There are written instructions too, in the next section.
VLOOKUP won't work here, so we'll use the INDEX and MATCH functions together, to get the results that we need.
To do this complex lookup with multiple criteria, we'll use the INDEX and MATCH functions.
When INDEX and MATCH are used together, they create a flexible and powerful lookup formula. ▲TOP
Before using INDEX and MATCH with multiple criteria, let's see how they work together in a simpler formuls.
In this lookup formula, we need to find “Sweater” in a column B of a price list, and get its price from column C.
Here’s how that simple INDEX / MATCH formula finds the sweater price:
So, by combining INDEX and MATCH, you can find the row with “Sweater” and return the price from that row. ▲TOP
In the previous example, the match was based on one criterion -- the Item name. For the next lookup, there are 2 criteria -- Item name and product Code.
In the screen shot below, each item is listed 3 times in the pricing lookup table. To get the right price, you’ll need to specify both the item name and the size. We want to find the price for a large jacket.
In the lookup formula, we need the MATCH function to check both the Item and Size columns.
To show how that will work, I'll add temporary columns on the worksheet, to check the item and size columns -- is the item a Jacket, and is the Size a Large?
Enter this formula in F2, and copy down to F10: =C2=$C$13
Enter this formula in G2, and copy down to G10: =D2=$D$13
We need the price from the row where both results are TRUE. We'll use a formula to calculate that for us:
Enter this formula in H2, and copy down to H10: =F2*G2
In Excel, TRUE is equal to 1, and FALSE is equal to zero. When you multiply the values,
Only the 8th row in our list of items has a 1, because both values are TRUE in that row.
We could use a MATCH formula to find the position of a 1 in column G, in the screen shot above. The 8th row of data (worksheet row 9), has the 1, and that row will give us the correct price for a large jacket.
But, instead of adding extra columns to the worksheet, we will use an array-entered INDEX and MATCH formula to do all the work.
Here is the array-entered* formula that we’ll use in cell E13, to get the correct price:
*Press Ctrl + Shift + Enter, instead of just pressing the Enter key. That will automatically add curly brackets around the formula.
Here's how this INDEX MATCH multiple criteria formula works. In this INDEX and MATCH example,
The formula checks for the selected item name in $C$2:$C$10, and the selected size in $D$2:$D$10. The TRUE and FALSE results are multiplied, to return zeros and ones.
To see how that works, select this part of the formula in the formula bar
Then, press the F9 key, to see the calculated results for the selected potion of the formula. In the screen shot below there are 9 results, and all are zero, except the 8th result, which is
Next, select the MATCH function portion of the formula in the formula bar
Press F9, to see the result. The MATCH function looks for the 1 in the array of results, so in our formula, it returns 8.
Then, press the ESC key, to exit the function without saving the calculations.
As the final step in the formula, the INDEX function returns the price from the 8th data row in column E (cell E9). ▲TOP
Last updated: May 10, 2018 1:12 PM