problem with vlookup

[Guest]

In one cell of one sheet I have vlookup formula like this
=vlookup($A1,sheet2!$A1:$BC12,4,FALSE)
This formula is absolutely fine . But i have to do this across whole row may
be till BC16. I there a way where I can enter formula in one cell then column
value changes automatically like ,5,FALSE ,6,FALSE

Any help is greatly appreciated
Thanx

 

[Tom Ogilvy]

You can modify your formula like this

=vlookup($A1,sheet2!$A1:$BC12,Column(A1)+3,FALSE)

then all you have to do is drag fill it across the columns.

 

[Gord Dibben]

Lolly

Example only.....adjust cell refs to suit.

=VLOOKUP(A1,'Sheet2'!A1:E50,{2,3,4,5},0)

Select B1:E1 then enter the above in active cell(B1)

Now Array enter it by hitting SHIFT + CRTL + ENTER

The 2,3,4,5 will increment.

Gord Dibben Excel MVP