Dragging VLOOKUP

[SamuelT]

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

[Bob Phillips]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[Daniel CHEN]

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" <[email protected]> wrote in
message news:[email protected]...
>
> 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
>

[T Kirtley]

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
>
>

[SamuelT]

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

SamuelT