Excel functions to return first row or first column from a range? (for a better 2 way lookup in table of data)

Question

So I'm trying to do the classic 2 way lookup from a table of data, ie:

     Jan-00   Feb-00   Mar-00
Joe
Bill
Steve

So, I want to look up the value for "Joe" for "Mar-00".

This type of lookups are typically done using a combination of the INDEX and MATCH functions. The drawback of this approach is that MATCH can only act upon a 1 dimensional range, so in a large spreadsheet with lots of tables, you end up having to define 3 ranges per table instead of one (one for data, one for header row, one for left column).

I'd like to be able to do something like this:

index(data1,match("Mar-00",getrow(mydata,1)),match("Joe",getcolumn(mydata,1)))

Is there anything like this in excel (or, an entirely different way of doing this, perhaps some new feature in excel)?

Answer 1

This is the classic use for VLOOKUP (or the related HLOOKUP):

=VLOOKUP("Joe", mydata, 4, FALSE)

You can also use INDEX to get a whole row or column out of a range (or 2-D array) by passing zero as one of the parameters:

=VLOOKUP("Joe", mydata, MATCH("Mar-00", INDEX(mydata, 1, 0), 0), FALSE)

This is assuming your "mydata" is the whole table, including the row and column headers.

(Of course you also can also just do what you did above, replacing getrow and getcolumn with the calls to INDEX.)