How To Lookup Multiple Criteria Using VLOOKUP In Excel

In this tutorial we are going to look at how you can have multiple criteria as your lookup value with VLOOKUP. We’ll use the CHOOSE function with an array constant, and enter it as an array formula.

You can download the file here and follow along. If you get a preview, look for the download arrow in the upper right hand corner.

Here I have a list of 6 students, 4 classes, and their grade for each class:

11517-1

I have an area with drop down lists for each student and class, and I want the formula in cell G2 to provide the grade for any combination:

11517-2

The formula we are going to use is:

{=VLOOKUP(E2&F2,CHOOSE({1,2},A2:A25&B2:B25,C2:C25),2,0)}

This formula is an array formula – notice the curly brackets around the entire formula – and must be entered using Ctrl + Shift + Enter. It utilizes both VLOOKUP and CHOOSE functions, and also incorporates an array constant in the CHOOSE portion of the formula.

The syntax for VLOOKUP is: =VLOOKUP(lookup_value,table_array,col_index_num,[range_lookup]), and is broken down as follows:

lookup_value – E2&F2 – notice we have concatenated the student and class with an ampersand – this will result in the lookup value being something like TomEnglish or BettyMath

table_array – CHOOSE({1,2},A2:A25&B2:B25,C2:C25) – the CHOOSE function provides the table array – more on that in a bit

col_index_num – 2 – we’ll be retrieving the answer from column 2 of the table array

[range_lookup] – 0 – this is an exact match

If you are not very familiar with VLOOKUP, please stop here and take a minute to go to my website and do a search on that function so you can understand it better.

The key to this working is the way we used the CHOOSE function with an array constant to create the table array.

The syntax for the CHOOSE function is: =CHOOSE(index_num, value1, value2, value3, …), and is broken down as follows:

index_num – {1,2} – this is an array constant – more about this in a minute

value1 – A2:A25&B2:B25 – this will be the first column of our table – and will concatenate the names and classes for each student

value2 – C2:C25 – this will be column two of the table array and will be the grades for each students’ class

With the CHOOSE function, the index number is the choice you make of the various values that you list. For example, in this formula – =CHOOSE(3,”book”,”bell”,”gong”,”drum”,”hat”) the answer would be “gong” because it is the third value in the list. If the index number was 5, the answer would be “hat”.

But with an array constant like we have in our formula – {1,2}, we are telling the CHOOSE function to choose not one or the other, but BOTH values – the list of concatenated names and classes, along with the grades. This then creates the table. If I put the formula into edit mode, selected the table_array for the VLOOKUP formula and hit F9, it would produce this:

=VLOOKUP(E2&F2,{“TomEnglish”,”A”;”TomMath”,”A”;”TomBiology”,”B”;”TomHistory”,”B”;”EdEnglish”,”C”;”EdMath”,”C”;
“EdBiology”,”D”;”EdHistory”,”D”;”AnnEnglish”,”A”;”AnnMath”,”B”;”AnnBiology”,”C”;”AnnHistory”,”D”;
“BettyEnglish”,”A”;”BettyMath”,”C”;”BettyBiology”,”D”;”BettyHistory”,”A”;”LarryEnglish”,”A”;
“LarryMath”,”B”;”LarryBiology”,”C”;”LarryHistory”,”B”;”SueEnglish”,”B”;”SueMath”,”A”;
“SueBiology”,”D”;”SueHistory”,”A”}
,2,0)

A comma indicates the separation between columns, and a semi-colon means a new row. So the CHOOSE function actually created a table similar to this:

11517-3

And now the VLOOKUP formula can find the matching lookup value in column one and return the appropriate grade from column 2.

What can you do next?
Share this post with others that can benefit from it!
Leave a comment or reply below – let me know what you think!
Subscribe to this blog for more great tips in the future!
Check out my YouTube channel – click on the YouTube icon below!

Happy Excelling!

Related Post

Free Download!

Subscribe to Download Your FREE Copy of
"My 70+ Favorite Excel Keyboard Shortcuts" Today!

, , , , , ,

Trackbacks/Pingbacks

  1. How To Lookup Multiple Criteria Using VLOOKUP In Excel – Latest Trendy Videos - May 26, 2017

    […] How To Lookup Multiple Criteria Using VLOOKUP In Excel […]

Leave a Reply