Excel vlookup question

[ReyDelSol]

When using a vlookup on an array or table, if you drag the vlookup across, the index number doesn't change.
For example if I have in cell A2 =vlookup(lookup_table,array,3,false) and drag that to cell A3, I will still have the '3' as the index number in the vlookup formula.

Is there a way to have the index number change sequentially when dragged as in other formulas?

The only thing I can think of is kinda clunky. I could number the columns and then set the index value to the column number and $lock it down.

Just curious because when a formula has several vlookups in it, it is a pain to update if you drag it acoss several columns.

 

[jmatt]

You need to get out more...

 

[ReyDelSol]

I saw a reply and I thought

Then I read it and thought

.

 

[pmg]

I think the '3' refers to the column in the table, not the column in the ss. Third column.

 

[ReyDelSol]

I figured it out.instead of =vlookup(lookup_value,array,3,false) I can replace the index number with "COLUMN ()"...

So the formula would then be =vlookup(lookup_value,array,column(C:C),false) in order to get the correct column from the array or range. Then when I drag it across it will autofill the next column sequentially D, E, etc.

Excellent. Much better.

Now, jmatt, I will get out.

:

 

[txtxyeha]

Good answer to your own question. I would have gone with your first solution, but what you came up with is much better. Thanks for posting this.

 

[HornBred]

This is the very reason why I always use SUMIF whenever possible. VLOOKUP is poorly designed.