dynamic index match to replace vlookup

Question

I can make a vlookup "dynamic" by changing the column index... and easily get entire column from the "database"... i can choose any keys in any order.

this is the data table + and the lookup:

and these are the formulas behind:

my question if the dynamic vlookup formula e. g vlookup($g5, $A:$E, J$4,false)... can be converted into index-match? e.g.index(E:E, match(A8, A:A, 0))

how can i get the A and E column without loosing the performence benefit of index-match?

Answer 1

Use the OFFSET function.

OFFSET takes a range as input, then returns a range that is a specified number of rows down, columns to the right, and a certain width and height. In this case, and assuming the data in your screenshot is backwards, we'd use something like this:

=INDEX(OFFSET($A:$A,0, J$4-1),MATCH($G5,$A:$A,0))

The OFFSET takes three arguments (it allows for five, but we're only using three here). The first is the start point - in this case, A:A. The second is the number of rows to move down - in this case, 0. The third is the number of columns to move to the right. This is where we're doing the work, by pulling the reference number from row 4 and moving across by that number of columns (-1, to account for the fact that column 1 = 0 columns of movement for our reference).