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 which we can’t do with VLOOKUP or they need to put some extra efforts to make it work.
For example, using VLOOKUP with multiple criteria.
By default, VLOOKUP allows you to lookup for a single value. But in a 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 for 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.
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.
1. Create a New Helper Column
Here are the steps to insert a new column by join two criteria.
- First of all, we need to add a new column.
- After that, in the new column enter a formula to join 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 where we have combined two different criteria (name & class).
2. Combine Two Criteria in 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 column 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.
Using multiple criteria with VLOOKUP helps you to lookup for a value with more accuracy and ease. Just like we have done in 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 tip useful and it will help you in your work.
Now tell me one thing. Have you ever tried to add more than one criteria 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.