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
andMATCH
rather thanVLOOKUP
. 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