VLOOKUP Help 1

[ajduk1]

Hi
I have a VLOOKUP that I need to use to enter info vertically
=VLOOKUP($A1,cust,1,FALSE)(CUST IS A DEFINED NAME)
If I fill down all is ok but I want to fill across keeping the absolute reference (A1) but allowing the lookup column to change from 1 to 2 to 3 etc.
eg.
=VLOOKUP($A1,cust,1,FALSE) =VLOOKUP($A1,cust,2,FALSE) =VLOOKUP($A1,cust,3,FALSE) ETC

When I drag across using the fill handle it keeps the column reference and so adds the same info along all columns.
Is there a way od doing this ?

AJD

AJD

 

[xlbo]

Only if your offset reference is going to be linear

If your 1st formula is in column A and the offset is 1 then you may use:

=vlookup($A1,cust,column(),FALSE)

You can add or subtract numbers from the column() reference depending on exactly how your offset works so if the 1st formula is in col D then you would use:

=vlookup($A1,cust,column()-3,FALSE)

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question

 

[ajduk1]

Thanks Geoff
I used your formula to an extent. I used :
=vlookup($A1,CUST,column()*1,FALSE) this gave me all I needed when entered in B1 and dragged across.

Thanks again Geoff

AJD

 

[xlbo]

why multiply the column() by 1? It returns a number so there should be no need to multiply

Rgds, Geoff

We could learn a lot from crayons. Some are sharp, some are pretty and some are dull. Some have weird names and all are different colours but they all live in the same box.

Please read FAQ222-2244 before you ask a question