In most cases, people will hardcode the column index into VLOOKUP to retrive values from a certain column. But if you want to create a dynamic, two-way lookup, and if column headers in the table are unique, you can use the MATCH function to figure out which column number to give VLOOKUP. Unlike a normal VLOOKUP formula, where the column index is hardcoded, and will break if columns are deleted or added to the table, VLOOKUP + MATCH will continue to work correctly.
In the example, we are using this formula to dynamically lookup both rows and columns with VLOOKUP:
H2 supplies the lookup value for the row, and H3 supplies the lookup value for the column.
How this formula works
This is a standard VLOOKUP exact match formula with one exception: the column index is supplied by the MATCH function.
Note that the lookup array given to MATCH (B2:E2) representing column headers deliberately includes the empty cell B2. This is done so that the number returned by MATCH is in sync with the table used by VLOOKUP. In other words, you need to give MATCH a range that spans the same number of columns VLOOKUP is using in the table. In the example (for Feb) MATCH returns 3, so after MATCH runs, the VLOOKUP formula looks like this:
=VLOOKUP(H2,B3:E11,3,0)
Which returns sales for Jon (row 7) in Nov (column 3), which is $17,804.
