Dragging VLOOKUP

[SamuelT]

Hi all,

I'm using a VLOOKUP formula (in Excel 2000) and need to drag th
command across a range of columns. Weird thing is (and I'm sure i
never used to do this), the column number - i.e. =VLOOKUP(whateve
,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,

Samuel

 

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

 

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

 

[Guest]

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.

 

[Guest]

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]

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

Samuel