Super User is a question and answer site for computer enthusiasts and power users. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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?


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

share|improve this question
Did you intentionally leave out the last argument? Leaving that out defaults to TRUE. Normally you would only use that option if the "lookup range" (1st column of table_array) is sorted ascending and you want a "closest match". Most likely appropriate for numeric data rather than text. – barry houdini Jan 31 '12 at 12:51
@barry yes, the input!$b$3 is a number and I am trying to match it to min-max value in the first column. – Jake Feb 1 '12 at 3:46
up vote 4 down vote accepted

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.


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

share|improve this answer

Depending upon where you start your formula entry could use


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

share|improve this answer

You 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.

share|improve this answer

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .