Using VLOOKUP and MATCH for a two-way look up across the column header

Excel

Most VLOOKUP formulas specify an exact column to pull the data from, but if you use MATCH it’s easy to pull the data from any column. Another advantage is that the formula automatically adjusts if you move columns in the table to new positions.

In the following figure the table is in F10:R13. The user specifies the data to retrieve by selecting from Data Validation lists in C6 and D6. These Data Validation lists use the regions, C10:C13, and Months List, D10:D21.

Using VLOOKUP and MATCH to do a two-way lookup on column headings.

Using VLOOKUP and MATCH to do a two-way lookup on column headings.

Download the Excel samples files for VLOOKUP

The VLOOKUP in cell G6 retrieves data by looking down the leftmost column of the table, F10:F13, searching through the regions. The formula in G6 is,

=VLOOKUP($F$6,$F$10:$R$13,MATCH($D$6,$F$9:$R$9,0),FALSE)

$F$6 is the value, East, being searched for

$F$10:$R$13 is the table containing data

MATCH($D$6,$F$9:$R$9,0) specifies the column in the table from which to retrieve data

FALSE specifies the search must find an exact match

Everything looks like a normal VLOOKUP except for the column from which data is retrieved. Instead of a static number specifying a column, the MATCH function is used. The MATCH function looks through all the month headers, $F$9:$R$9 to find an exact match to the month in $D$6. The number MATCH returns is the column from which data will be retrieved.

By using MATCH to calculate the column from which data is retrieved we can look for any column header. You can use a Data Validation list, a drop-down list, a manually entered value, or a calculated value to specify which column the data comes from. And, you never have to rebuild formulas because a new column was inserted in the table.

Download the Excel samples files for VLOOKUP

Share the power...