Tip: A Better Way For Dynamic Look-ups

Looking up a value in a range is probably the most common task in Excel after SUM and the basic operations. We usually want the look-up function we write to be dynamic. Look at the table on the picture below

The table is named SampleRange for easier access
If the task is to extract the value for C in 2015 usually we type a formula like this one:
=VLOOKUP("C",SampleRange,5,FALSE)

Year 2012 in column 5. If we want to make it dynamic we use match function and the formula goes like this
=VLOOKUP("C",SampleRange,MATCH(2015,A1:F1,0),FALSE)

It is OK with small and fast to access tables. Bigger calculation sheets however this is not very convenient as we have to go to the range to find the address for the header row. This could be annoying and time consuming as we have been clever enough to name the data range for easier access. A simple solution for this is to use INDEX formula. Little know and even less used is the fact that we could use this formula for accessing rows or columns. You can do this by omitting the number of the column to reference a row or row to refer to a column. For example, INDEX(SampleRange, 2, ) refers to row 2 in the range and INDEX(SampleRange, ,1) refers to first column in it.

Our simple formula then becomes:
=VLOOKUP("C",SampleRange,MATCH(2015,INDEX(SampleRange,1,),0),FALSE)

Same goes for HLOOKUP but INDEX should be INDEX(SampleRange, ,1) to refer to the first column in the range.

This approach reduces not only the time for writing the formula but also the errors coming from referencing the wrong row.