Excel – Autoincrement the VLOOKUP column index in Excel

microsoft excelvlookup

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.

Best Answer

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

Related Question