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.
26 Answers
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
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.
Using the column function is a more elegent.
=VLOOKUP(input!$B$3,data!$A4:$AJ52,COLUMN(B4),0)You can copy this across and down and it should always work.
1You can also click on Column(B:B). This automatically chooses column 2.
Note if you wish to start on column 5 Just Click on (E:E) etc.
No need to then add anything. This should be done on the worksheet where you are starting your vlookup.
It would look like this =vlookup(yourcell,yourrange,column(B:B),0)This will drag automatically and start with column 2 then 3 etc.
You can easily do this by creating a row of numbers with the indexing that you want, then looking up those column numbers in your formula. For example:
=VLOOKUP($A3,Sheet2!$A:$L,B$2,FALSE)Row 1 has my regular headers in there.
Row 2 is numbered with what you would normally have as the column number in your VLOOKUP formula ("grab the value from the third column").
I am putting the formulas into rows starting in row 3.
Best option is to use Column(Start)-Column(End)+1, this way if you add or delete columns your data won't be a mess
1
