Autoincrement the VLOOKUP column index in Excel

Question

This feels like a common question but I cannot get it to work or find an absolute answer:

How to make the column_index "auto-increment" as I drag horizontally the cell with this VLOOKUP formula?

=VLOOKUP(input!$B$3,data!$A4:$AJ52,2)

My lookup tables has 30 to 50 columns each. If auto-incrementing the column index is possible then it will be a great help.

Answer 1

One solution is to use INDEX and MATCH rather than VLOOKUP. This is more robust and arguably clearer,e.g. use this formula copied across.

=INDEX(data!B4:B52,MATCH(input!$B$3,data!$A4:$A52))

That formula matches in col A and returns a value from col B as does your VLOOKUP, but when you copy across that changes.

Note that I used MATCH with no third argument to be consistent with your VLOOKUP with no fourth argument.

Because the same MATCH function is repeated across the whole row then for more efficiency you could put that function alone in one cell then refer to that same cell across the row

Answer 2

Depending upon where you start your formula entry could use

 =VLOOKUP(input!$B$3,data!$A4:$AJ52,COLUMN()+1)

This would give you 2 if it is entered into column A, and will increment up as you drag it right.