Looking up a value in a table using one criteria is simple. You use a simple VLOOKUP formula. Things are much different when you want to use more than one criteria- Multiple criteria. This can be attained in various ways. It includes the use of Excel functions such as LOOKUP, VLOOKUP, INDEX, and MATCH. We are going to learn from this article a few of these ways:
Using single criteria
Lets use the following data as our example
We will learn how to use the index and match functions to get the correct price for an item based on the item and the size. First, we'll look at the index and how we can use it to get the price based on one criterion.
So here we have a jacket, and we will find the price for the first Jacket.
Now we have to tell Excel which row to pull this price from. So we want to use the match function here. And it's going to find the first instance of Jacket and tell us which row that's in.
In cell D13, type =index(D2:D10,match(B13,B2:B10,0))
By typing this formula match(B13, tell us that the lookup value we want is Jacket, B2:B10 defines the array in which we are to look up, and we lastly choose the type of match that we want, which is an exact match 0). This finds the value of the first Jacket.
And if we look here, there's the price for the first Jacket. So we haven't considered the size. This is just returning that first prize. So we want to make a variation on this. We still want to look in the same range in the index, but we have to use a different method with a match to find the correct row. So we're going to be looking for a specific size when looking for the Jacket, a large-scale type.
Using Multiple Criteria
In the first step we indexed one column and matched it with another. In the next step we are going to index with multiple criteria.
1. Write the item you want to index on cell B13
2. Write the size you want to match on cell C13
3. Write the following formula on cell D13
4. This is an array formula. So we have to press Control, Shift, and then hit Enter. And there's our prize.