How to look up a value after multiple criteria

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:

Table of Contents

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.