Excel: Vlookup Column Index, Fill Formula & Increment Value? 1

[Larft]

Does anyone know if there is a way to increment the Column Index Value for a Vlookup when drag filling formulas? Something like the opposite of the $ absolute reference for cells, or an approch to automating the process without having to type the number in each coulmn?

Any help would be appreciated.

Thanks in advance!

 

[xlhelp]

If you database starts in column A

=COLUMN()-X (where X is any number that suits your needs)

Member- AAAA Association Against Acronym Abusers

 

[AnotherHiggins]

Are you dragging the formulas up/down or left/right?

If dragging left/right, then just use Relative Reference instead of Absolute Reference (take out the "$").

It would be helpful if you included the formula you are using and explained exactly what you want to accomplish.

[tt]_____
[blue]-John[/blue][/tt]
[tab][red]The plural of anecdote is not data[/red]

Help us help you. Please read FAQ181-2886 before posting.

 

[Larft]

Dragging both right and down, the down part is a piece of cake since it fills the existing references, the right part does not fill & increment the column index value though (it's not set to absolute), here is a sample of the code:

=IF(ISNA(VLOOKUP(Entry!$A11,LinkBreakdown!$A$1:$BV$3500,1,FALSE))=TRUE,0,VLOOKUP(Entry!$A11,LinkBreakdown!$A$1:$BV$3500,1,FALSE))

Tried the COLUMN approach but it doesn't seem to like it inside the LOOKUP statement, I'm not familiar with the syntax at the moment.

Does the code sample help?

Thankas!

 

[Larft]

Okay,

That so simple it's no wonder I didn't come up with it, I just had to use the COLUMN statment properly in place of the actual column index number value:

[CODE[=IF(ISNA(VLOOKUP(Entry!$A11,LinkBreakdown!$A$1:$BU$3500,COLUMN(A2),FALSE))=TRUE,0,VLOOKUP(Entry!$A11,LinkBreakdown!$A$1:$BU$3500,COLUMN(A2),FALSE))[/CODE]

Thanks xlhelp, you get a star!