*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.

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

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.

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: