Home |
Search |
Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Dragging VLOOKUP | New Users to Excel | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
vlookup data hidden within worksheet | Excel Worksheet Functions | |||
VLOOKUP dragging down | Excel Worksheet Functions | |||
VLOOKUP dragging down | Excel Worksheet Functions |