How to VLOOKUP with Multiple Criteria in Excel


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.

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.

Conclusion

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.

Must Read Next

About the Author

Puneet Gogia

Puneet is using Excel since his college days. He helped thousands of people to understand the power of the spreadsheets and learn Microsoft Excel. You can find him online, tweeting about Excel, on a running track, or sometimes hiking up a mountain.