How To Vlookup Multiple Values In Excel With One Or Multiple Criteria

Formulas are the key to getting things done in excel.

How to vlookup multiple values in excel with one or multiple criteria. In excel the mixed indext and match function is powerful for us to vlookup values based on one or more criteria to know this formula do as follows. However if you have control over source data you can use a helper column to join multiple fields together and use these fields like multiple criteria inside vlookup. Excel vlookup function in its basic form can look for one lookup value and return the corresponding value from the specified row. Suppose you have a data with students name exam type and the math score as shown below.

When using microsoft excel for data analysis you may often find yourself in situations when you need to get all matching values for a specific id name email address or some other unique identifier. Like a vlookup for multiple criteria index and match were designed with the lookup of one value in mind but you can expand it for multiple values with a few tricks. In the example shown column b is a helper column that concatenates first and last names together and vlookup does the same to build a lookup value. What remains the same in any lookup function is that there needs to be a unique value to look up.

We can do a lookup that retrieves multiple values instead of the first one only. Sometimes while working with data when we match the data to the reference vlookup if finds the value first it displays the result and does not look for the next value but what if the user wants the second result this is another criteria to use vlookup with multiple criteria we need to use other functions with it such as choose function. If you are here it means that you are trying to use vlookup to retrieve multiple values from a data set. Vlookup is a great tool for pulling data from tables but it has a handicap.

But that doesnt mean we cant do it. Today in this post we will explore the method for using multiple criteria with vlookup. The vlookup function does not handle multiple criteria natively. Vlookup value with multiple criteria with indext and match function.

How to use vlookup with multiple criteria. How to use vlookup with multiple criteria. In this accelerated training youll learn how to use formulas to manipulate text work with dates and times lookup values with vlookup and index match count and sum with criteria dynamically rank values and create dynamic ranges. So lets get started and make sure to download this sample file from here to follow along.

But often there is a need to use the excel vlookup with multiple criteria. But lets get it straight vlookup cant return multiple values. If there are multiple rows in your sheet with the same information youll only get the first one. Now according to the above problem i have mentioned we need to use two criteria as a lookup value in vlookup.