Dragging VLOOKUP
 

Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag the
command across a range of columns. Weird thing is (and I'm sure it
never used to do this), the column number - i.e. =VLOOKUP(whatever
,whatever, THIS NUMBER, false) doesn't change when I do the dragging.
One would think that it would go up incrementally..

Have I disabled some option, or is there a means of doing this?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449

Dragging VLOOKUP
 

No it won't, if you want it to you need to refer to something that will also
increment, such as COLUMN(A1)

--

HTH

Bob Phillips

(replace xxxx in the email address with gmail if mailing direct)

"SamuelT" wrote in
message ...

Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag the
command across a range of columns. Weird thing is (and I'm sure it
never used to do this), the column number - i.e. =VLOOKUP(whatever
,whatever, THIS NUMBER, false) doesn't change when I do the dragging.
One would think that it would go up incrementally..

Have I disabled some option, or is there a means of doing this?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449

Dragging VLOOKUP
 

THIS NUMBER will not increase automatically because it is in the formula,
not a direct input number.

You can you function COLUMN to get the increment you want, like
Assume you want this number to be 1, 2, 3,....
The first column you have the vlookup formula is D (column 4)

then use the following formula to replace this number:
column()-3
When you drag the formula, the column D, it is 1 (4-3) , column E it is 2
(5-3), ....


--
Best regards,
---
Yongjun CHEN
==================================
- - - - www.XLDataSoft.com - - - -
Free Excel/VBA Tool & Training Material
==================================


"SamuelT" wrote in
message ...

Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag the
command across a range of columns. Weird thing is (and I'm sure it
never used to do this), the column number - i.e. =VLOOKUP(whatever
,whatever, THIS NUMBER, false) doesn't change when I do the dragging.
One would think that it would go up incrementally..

Have I disabled some option, or is there a means of doing this?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449

Dragging VLOOKUP
 

Hi

The column return number does not increment - because it's part of a formula
and does not relate to a cell, I guess. If you are dragging it across
columns you could use the COLUMN() function to increment the number for you,
eg.
=VLOOKUP(A2,tabls,COLUMN()-2, FALSE)
You will need to alter the -2 to the correct figure but it will increment
when you drag it.

Hope this helps.
Andy.

"SamuelT" wrote in
message ...

Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag the
command across a range of columns. Weird thing is (and I'm sure it
never used to do this), the column number - i.e. =VLOOKUP(whatever
,whatever, THIS NUMBER, false) doesn't change when I do the dragging.
One would think that it would go up incrementally..

Have I disabled some option, or is there a means of doing this?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile:
http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449

Dragging VLOOKUP
 

No, to my knowledge VLOOKUP() has never incremented the column number
reference automatically. If you want to build a formula that can use relative
references when copied, try a combination of the INDEX() and MATCH()
functions.

Syntax is like: INDEX([range of data to return], MATCH([lookup value],
[lookup range],0))

HTH,

TK

"SamuelT" wrote:


Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag the
command across a range of columns. Weird thing is (and I'm sure it
never used to do this), the column number - i.e. =VLOOKUP(whatever
,whatever, THIS NUMBER, false) doesn't change when I do the dragging.
One would think that it would go up incrementally..

Have I disabled some option, or is there a means of doing this?

TIA,

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449

Dragging VLOOKUP
 

Thanks all. Your suggestions have given me a good solution!

SamuelT


--
SamuelT
------------------------------------------------------------------------
SamuelT's Profile: http://www.excelforum.com/member.php...o&userid=27501
View this thread: http://www.excelforum.com/showthread...hreadid=551449