Take the 2-minute tour ×
Super User is a question and answer site for computer enthusiasts and power users. It's 100% free, no registration required.

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.

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
add comment

3 Answers

up vote 3 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.

=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

share|improve this answer
add comment

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.

share|improve this answer
add comment

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
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.