Two Dimensional VLOOKUP

Associated Files Download Links

The VLOOKUP and HLOOKUP functions are well known for looking up data in one dimension:

And then:

However what happens if we have a TWO dimensional array

And we want to find the value for Boats in Mar-08. So we could add two drop downs to specify the mode of transport and the month that we need:

Excel provides a function called INDEX that allows us to return values from a 2d array: INDEX(Array_Range, Row Number, Col Number)

Where • Array_Range is the range in Excel of the two dimensional array – in this case $B$4:$H$7 • Row Number is the position in the list where we find the text “Boat” – in this case is 1 • Col Number is the position in the list where we find the month “Mar-08”

Of course the only thing left to do is to determine the Row and Column Number. This is done by using the MATCH function – which returns the position of a string within a range of values: MATCH(“String”, Range,0) – will return the position of “String” in the array “Range” and the 0 states that we want an exact match. So we are looking for the position of Boats in the range {Boats, Cars, Planes , Lorries} – which is 1. This will give the row number:

MATCH(B11,$B$4:$B$7,0) And similarly for the column number MATCH(C11,$C$2:$H2,0) And then we combine all these into one function: =INDEX($C$4:$H$7,MATCH(B11,$B$4:$B$7,0),MATCH(C11,$C2:$H$2,0)) To give the value of 79 for boats in March 2008: