Simple VLookup Problem (I hope!)

[Nibbles]

Hi,

I have a simple array of data consisting of several columns. Each column holds data for a specific date.

On a seperate worksheet I wish to display this data for each date and I am using lookup functions to get it. I am using the following formula:

VLOOKUP($A1,'Sheet2'!$A1:$AE273,3,0)

All I want to do is drag this formula across my cells so that the column reference number (3 in the example above) will increase by 1 for each column. No matter what I try the column ref will always remain fixed.

Can anyone help? It is driving me insane!

Many thanks,

Nibbles

 

[Aladin Akyurek]

On 2002-04-16 02:53, Nibbles wrote:
Hi,

I have a simple array of data consisting of several columns. Each column holds data for a specific date.

On a seperate worksheet I wish to display this data for each date and I am using lookup functions to get it. I am using the following formula:

VLOOKUP($A1,'Sheet2'!$A1:$AE273,3,0)

All I want to do is drag this formula across my cells so that the column reference number (3 in the example above) will increase by 1 for each column. No matter what I try the column ref will always remain fixed.

Can anyone help? It is driving me insane!

Many thanks,

Nibbles

Which cell did you put this formula?

 

[Nibbles]

Hi Aladdin,

That formula was just an example to illustrate the principal that I need. Say I have a column of data from A2:A273 on Sheet 1, and i wanted to use this to lookup data from A2:AE273 on sheet 2. I would want to enter the following formula in B2:

Vlookup($A2,'Sheet2'!$A2:$AE273,2,0)

I would then want to drag this formula across the range B2:AE273 on sheet 1. However when I do this the column ref will remain fixed at 2, whereas I want it to be 3 for column C, 4 for column D etc.

Hope this explains my problem better,

Nibbles

 

[Aladin Akyurek]

On 2002-04-16 03:02, Nibbles wrote:
Hi Aladdin,

That formula was just an example to illustrate the principal that I need. Say I have a column of data from A2:A273 on Sheet 1, and i wanted to use this to lookup data from A2:AE273 on sheet 2. I would want to enter the following formula in B2:

Vlookup($A2,'Sheet2'!$A2:$AE273,2,0)

I would then want to drag this formula across the range B2:AE273 on sheet 1. However when I do this the column ref will remain fixed at 2, whereas I want it to be 3 for column C, 4 for column D etc.

Hope this explains my problem better,

Nibbles

In B2 enter and copy across:

VLOOKUP($A2,'Sheet2'!$A2:$AE273,COLUMN(),0)

 

[Nibbles]

That's fantastic Aladdin, thankyou very much, I knew it would be annoyingly simple!

All the best,

Nibbles

 

[First Wob]

Thanks Aladin. I was stumped also and you solved the problem.