How to VLOOKUP with Multiple Criteria in Excel (Formula)

VLOOKUP is one of the most popular Excel functions. But, it’s one of the most limited functions as well.

Advanced Excel users know that there are so many things that we can’t do with VLOOKUP or they need to put some extra effort to make it work.

For example, using VLOOKUP with multiple criteria. By default, VLOOKUP allows you to lookup for a single value.

But in the real world, there are some situations where we need to use two or more criteria.

Let’s say you have a list of students for an entire school and from that list, you want to look up a student whose name is “John” and who is in class IX.

There is a huge possibility that there could be more than one student whose name is “John”. The best solution here is to combine two different criteria (name and class) to get the right value in return.

Today, in this post, we will explore the method for using multiple criteria with VLOOKUP.

So, let’s get started and make sure to download this sample file from here to follow along.

Now, according to the above problem I have mentioned, we need to use two criteria as a lookup value in VLOOKUP. For this, we need to do two adjustments which are easy and simple.

Create a New Helper Column

Here are the steps to insert a new column by joining two criteria.

• First of all, we need to add a new column.

• After that, in the new column enter a formula to join the name and class, just like below.

• Now, drag the formula up to the last cell of the column.
• Now, we have a single column in which we have combined two different criteria (name & class).

Combine Two Criteria in the Lookup Value

Now, the next thing is to combine two criteria in the lookup_value argument of VLOOKUP.

Here we want to use “John” and “Class-IX” and for this, we need to combine both using an ampersand in lookup_argument.

After that, we need to select columns C and D as a lookup_range, 2 as col_index_number, and 0 as [range_lookup]

In the end, hit enter. And now, we have John’s score who is in Class-IX.

How it Works

The method of using combined criteria in VLOOKUP is simple. When we combine two columns into one it gives us unique values.

Just think this way, we have the name John three times in our data. But when we join names with their class we get a unique value for each name.

For example, JohnClass-IX, JohnClass-VI, and JohnClass-II. Now, there is no value is in duplicates.

In the same way, while specifying lookup_argument in VLOOKUP we have combined both of the criteria: name and class so that it returns the score for the right student i.e. John who is in class IX.

Conclusion

Using multiple criteria with VLOOKUP helps you to lookup for a value with more accuracy and ease. Just like we have done in the above example.

And, the best part is that combining two different criteria is no big deal, it’s simple and easy. I hope you found this formula helpful tip and that it will help you in your work.

Now tell me one thing. Have you ever tried to add more than one criterion in VLOOKUP? Or, do you have any other method for this? Please share with me in the comment section, I would love to hear from you. And please don’t forget to share this tip with your friends.