Two Dimensional VLOOKUP

Written by

Editorial Team

Reviewed by

Steve Rynearson

Last updated on February 8, 2018

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

vlookup example

And then:

vlookup multiple conditions

However what happens if we have a TWO dimensional array

how to use a vlookup in excel

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:

vlookup

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:

vlookup syntax

AI Formula Generator

Try for Free

Excel Practice Worksheet

practice excel worksheet

Practice Excel functions and formulas with our 100% free practice worksheets!

  • Automatically Graded Exercises
  • Learn Excel, Inside Excel!

Free Download

Return to Excel Formulas List