Watch Video – How to Use VLOOKUP Function with Multiple Criteria
Excel VLOOKUP function, in its basic form, can look for one lookup value and return the corresponding value from the specified row.
But often there is a need to use the Excel VLOOKUP with multiple criteria.
How to Use VLOOKUP with Multiple Criteria
Suppose you have a data with students name, exam type, and the Math score (as shown below):
Using the VLOOKUP function to get the Math score for each student for respective exam levels could be a challenge.
One can argue that a better option would be to restructure the data set or use a Pivot Table. If that works for you, nothing like that. But in many cases, you are stuck with the data that you have and pivot table may not be an option.
In such cases, this tutorial is for you.
Now there are two ways you can get the lookup value using VLOOKUP with multiple criteria.
- Using a Helper Column.
- Using the CHOOSE function.
VLOOKUP with Multiple Criteria – Using a Helper Column
I am a fan of helper columns in Excel.
I find two significant advantages of using helper columns over array formulas:
- It makes it easy to understand what’s going on in the worksheet.
- It makes it faster as compared with the array functions (noticeable in large data sets).
Now, don’t get me wrong. I am not against array formulas. I love the amazing things can be done with array formulas. It’s just that I save them for special occasions when all other options are of no help.
Coming back to the question in point, the helper column is needed to create a unique qualifier. This unique qualifier can then be used to lookup the correct value. For example, there are three Matt in the data, but there is only one combination of Matt and Unit Test or Matt and Mid-Term.
Here are the steps:
- Insert a Helper Column between column B and C.
- Use the following formula in the helper column:=A2&”|”&B2
- This would create unique qualifiers for each instance as shown below.
- Use the following formula in G3 =VLOOKUP($F3&”|”&G$2,$C$2:$D$19,2,0)
- Copy for all the cells.
How does this work?
We create unique qualifiers for each instance of a name and the exam. In the VLOOKUP function used here, the lookup value was modified to $F3&”|”&G$2 so that both the lookup criteria are combined and are used as a single lookup value. For example, the lookup value for the VLOOKUP function in G2 is Matt|Unit Test. Now this lookup value is used to get the score from C2:D19.
Clarifications:
There are a couple of questions that are likely to come to your mind, so I thought I will try and answer it here:
- Why have I used | symbol while joining the two criteria? – In some exceptionally rare (but possible) conditions, you may have two criteria that are different but ends up giving the same result when combined. Here is a very simple example (forgive me for my lack of creativity here):
Note that while A2 and A3 are different and B2 and B3 are different, the combinations end up being the same. But if you use a separator, then even the combination would be different (D2 and D3).
- Why did I insert the helper column between column B and C and not in the extreme left? – There is no harm in inserting the helper column to the extreme left. In fact, if you don’t want to temper with the original data, that should be the way to go. I did it as it makes me use less number of cells in the VLOOKUP function. Instead of having 4 columns in the table array, I could manage with only 2 columns. But that’s just me.
Now there is no one size that fits all. Some people may prefer to not use any helper column while using VLOOKUP with multiple criteria.
So here is the non-helper column method for you.
VLOOKUP with Multiple Criteria – Using the CHOOSE Function
Using array formulas instead of helper columns saves you worksheet real estate, and the performance can be equally good if used less number of times in a workbook.
Considering the same data set as used above, here is the formula that will give you the result:
=VLOOKUP($E3&”|”&F$2,CHOOSE({1,2},$A$2:$A$19&”|”&$B$2:$B$19,$C$2:$C$19),2,0)
Since this is an array formula, use it with Control + Shift + Enter, instead of just Enter.
How does this work?
The formula also uses the concept of a helper column. The difference is that instead of putting the helper column in the worksheet, consider it as virtual helper data that is a part of the formula.
Let me show you what I mean by virtual helper data.
In the above illustration, as I select the CHOOSE part of the formula and press F9, it shows the result that the CHOOSE formula would give.
The result is {“Matt|Unit Test”,91;”Bob|Unit Test”, 52;……}
It’s an array where a comma represents next cell in the same row and semicolon represents that the following data is in the next column. Hence, this formula creates 2 columns of data – One column has the unique identifier and one has the score.
Now, when you use the VLOOKUP function, it simply looks for the value in the first column (of this virtual 2 column data) and returns the corresponding score.
You can also use other formulas to do a lookup with multiple criteria (such as INDEX/MATCH or SUMPRODUCT).
Is there any other way you know to do this? If yes, do share with me in the comments section.
You May Also Like the Following LOOKUP Tutorials:
15 thoughts on “How to Use VLOOKUP with Multiple Criteria”
Thank you. It is informative, useful and it works once I understood the structure.
Thanks for your guidance
I AM STILL A LITTLE CONFUSED ABOUT THIS FORMULA.. this vlookup formula is complex. can you break it down to be simpler
Suppose in a same cell let say A1 I have entered multiple values using Alt+enter. Can I get a corresponding vlookup value in other cell let say B1 for each entry in A1 cell?
Awesome tip. THANKS!!!!
Awesome tips , Sumit , thanks alot .
Hi Sumit
It is found that your website is providing very good information on MS Excel.
I am looking for information on selection of multiple values into a dropdown list based on the selection from another dropdown list.
1 1.65
1 2.77
1 2.9
1 3.38
1 4.55
1 6.35
1 9.09
2 1.65
2 2.11
2 2.77
2 3.18
2 3.58
2 3.91
2 4.37
2 4.78
2 5.54
2 6.35
2 7.14
2 8.74
2 11.07
If 1 is selected from a dropdown list, the values against 1 to be populated in to another dropdown list. The first column may contain fractions in text format, decimals etc.,
Can you please help on this?
Thanking you
Krishna
Use define range and indirect formula
thank you so much for this …..exactly what I was trying to figure out 🙂
Hello, many thanks for your very useful tips, however I didn’t found any answer related to the following issue. The formula on column I should return the exchange rate for the currency on column N related to the date on column C (another sheet). If there isn’t an exchange rate to that day, it should return the immediately previous date’s exchange rate. My issue is how can I write the formula to return the exchange rate to the currency to the immediately previous date? Thanks a lot for your help.
Hi Sumit! Thank you for the tip. Is there a way to use this if the info you need is to the left of the info you have? I had ran a macro to insert a column and concatenate the info to the column A. Your tip here looks easier. Will it go left too?
Hey Jill, The limitation of VLOOKUP is that it can not look for the data on the left. However, you can do this using the combination of INDEX/MATCH. I cover details on the difference in these two functions here: https://trumpexcel.com/vlookup-vs-index-match-debate-ends/
You can learn more about INDEX function here: https://trumpexcel.com/excel-index-function/
is there any shortcut for inserting rows after specific lines,
i want to insert new row after every ten lines
Hi Sumit, Thanks a lot for your tips and I have started excel in excel, I have a small issue while copying a set of numbers from one other excel column. I have pasted it and tried to sort assenting order. But they have a “space in the front” and not responding to my command. How to overcome this issue.
With regards
JACIN
For your data table setup and the resulting layout, Pivot Table should be the ideal solution. 🙂
Thanks for commenting Wong.. I mentioned the same in the article too. Pivot Table works really well and are easy to use, but need to the data to be structured in a required format. And if you are looking to fetch only a couple of values (let’s say in a dashboard), then VLOOKUP (or Index/Match, Sumproduct) could be the way to go.