Lookup in Excel Using Multiple Criteria

​By Iroshan Navaratne, Assistant Director Institutional Effectiveness, Claflin University

Have you ever run into a situation in which VLOOKUP and HLOOKUP just did not cut it? For example: The worksheet below has GPAs by term and year for every student. You are tasked with compiling a list of students for the entire academic year and, amidst all other data relevant to each particular student, you are to list their GPAs for each semester in separate columns.

multiple1.jpg
 
Using VLOOKUP in cells D2:D18 on Sheet2 results in the following:

multiple2.jpg

Note how Excel populates GPAs for each student regardless of term. Even though VLOOKUP is quite a powerful function, not being able to parse in a second criterion makes achieving this task seem nearly impossible. Luckily, the index and match methods can be used to simulate the effects of VLOOKUP.

The index method returns the VALUE of a cell within an array or reference. The syntax is as follows: =INDEX(Array, Row_num, [Column_num] ). The match method returns the POSITION of a cell within an array. The syntax is as follows: =MATCH(Lookup_value, Lookup_array, [Match_type]). 

The formula “=INDEX(Sheet1!A1:D18,4,4)” returns “3.19” as it looks through the array “A1:D18” and grabs the value of the cell in the fourth row and column; and likewise “=MATCH(1001, Sheet1!A1:A18,0)” returns “5 finding “1001” within the array on the fifth row.
 
Additional criteria can be parsed into the MATCH function as well. Instead of providing the function with a “Lookup_array,” we ask that it make one. In the example below, the function looks for a cell that is the intersection of an id (1001) and respective term (SU). If such a cell exists fulfilling both criteria, a 1 (1 for TRUE, or 0 for FALSE) is stored in the array. The MATCH function goes on to return the row of the matching cell (6). 
 
{=MATCH(1,(Sheet1!A1:A18=1001)*(Sheet1!C1:C18=”SU”),0)}
 
If you’ve tried entering the Match formula and keep getting a #VALUE! error, it’s likely that you haven’t entered it in as an array formula. Note the curly brackets surrounding the formula above; this indicates that any formula encapsulated within is an array formula. To enter any formula as an array formula, simply hold down your control and shift keys while hitting enter.
 
Utilizing both the INDEX and MATCH functions in conjunction, we arrive at the following formula:
 
{=INDEX(Sheet1!$A$1:$D$18,MATCH(1,(Sheet1!$A$1:$A$18=$A2)*(Sheet1!$C$1:$C$18=B$1),0),4)}
 
The INDEX function looks through the array for a row that matches both criteria and returns the value in the fourth column as shown below.
 
multiple3.jpg

To clean it up a bit and eliminate all errors, we can utilize the IfError method to modify the formula to:
 
{=IFERROR(INDEX(Sheet1!$A$1:$D$18,MATCH(1,(Sheet1!$A$1:$A$18=$A2)*(Sheet1!$C$1:$C$18=B$1),0),4),””)}
 
This process results in the following:

multiple4.jpg

 

 

 Comments

 
To add a comment, Sign In
Total Comments: 11
 
Lisa posted on 10/16/2014 11:40 AM
This is a great tip! I have tried this and have already found a use for it. Thanks so much for sharing.
Nic posted on 10/16/2014 11:45 AM
This is a really useful tip. We've had this problem in the past in Access and this is a nice solution.
Norma posted on 10/16/2014 11:45 AM
A very useful tip! The coding takes some getting use to, but I can easily think if ways to utilize it.
Keith posted on 10/16/2014 1:22 PM
Great examples and formulas. I think this will be a powerful way for restructuring data to include in our data visualizations and pivot charts. Thank you!
Lee posted on 10/16/2014 3:24 PM
Thanks Iroshan! I use Excel extensively but this is a new trick - looks like it could be very useful. I've already given it a try and it works great.
Terry posted on 10/17/2014 8:10 AM
Super! This Tech Tip really illustrates the power of Excel. No wonder it is the goto application for most of us.
Dale posted on 10/17/2014 8:55 AM
Very useful. It's a nice way to broaden one's skill set beyond the VLOOKUP.
Mike posted on 10/17/2014 10:47 AM
Thanks for the tip, Iroshan. The example illustrates the power of array formulas.
Ishuan posted on 10/17/2014 4:17 PM
Wonderful; the instruction is very clear and it worked well.
Shawn posted on 10/22/2014 4:23 PM
Very useful way of "de-cluttering" information in Excel without needing to jump into another software application. Thank you for sharing!
Mike posted on 12/20/2014 5:56 AM
These commands are powerful I'm sure, but for this specific example, why not simply put the data into a pivot table? It reshapes the data in the same way, taking the various rows of FA, SP, and SU data and putting them into columns for FA, SP, and SU.