Results 1 to 9 of 9
Thread: using VLOOKUP command (2000)

20030319, 12:41 #1
 Join Date
 Mar 2003
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
using VLOOKUP command (2000)
In excel I am using the VLOOKUP command to return a row of data from another workbook pertaining to the name (in the cell of the current workbook) being looked up. However, the data spreadsheet in the other workbook which the VLOOKUP command looks in is a growing spreadsheet. How can I get the range in the VLOOKUP command to automatically cover the whole spreadsheet of data? Here is the command that I am using: =VLOOKUP($E11,[PERSONAL.XLS]Sheet1!$C$5:$N$45,3,FALSE). If the range is previously $C$5:$N$45, then I add two more lines of data in the PERSONAL workbook, how can I get the range to automatically change to $C$5:$N$47? Thanks and look forward to the responce.

20030319, 14:29 #2
 Join Date
 Jan 2001
 Location
 South Carolina, USA
 Posts
 7,295
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
Why not just change the $C$5:$N$45 to $C$5:$N$500 or whatever the max size will ever be?
Legare Coleman

20030319, 14:38 #3
 Join Date
 Dec 2001
 Location
 Br. Columbia, Canada
 Posts
 28
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
Providing you insert the new data between rows 5 and 45, the lookup range will adjust itself even though you are using absolute references. One way of doing this is to leave the last defined row empty and then selecting that row and inserting the new rows. Alternatively use a range name and set up a simple macro to redefine the range name after additions are made to the end of the range.

20030319, 14:45 #4
 Join Date
 Mar 2003
 Posts
 65
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
I thought about that, but I realized that this spreadsheet will be huge  continually expanding in columns and rows. I would just select the whole spreadsheet as a range to feel a little better  but the spreadsheet is infinite (or isn't it?). Anyway, I'll do just that (select a massive area in the spreadsheet), and I'll see how it turns out. I'm sure it'll be find. Thanks.

20030319, 15:19 #5
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: using VLOOKUP command (2000)
Assuming there are no blank cells in C5 to C "end" you can create a dynamic range name with OFFSET:
Define a named range (insert  name define)
CountC
that refers to (no quotes):
"=COUNTA(Sheet1!$C:$C)COUNTA(Sheet1!$C$1:$C$4)"
This will count the entries in Col C
Then create another named range:
LookupArray [or whatever you want to name it]
that refers to (no quotes)
"=OFFSET(Sheet1!$C$5,0,0,CountC,12)"
This range will be [C5:Nx] where x will grow as new items are added to col C. As the items in C increases, the range will automatically expand.
Steve

20030319, 15:20 #6
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
Glad this thread has appeared, as I've recently come across a problem with this function (or me, or excel, both 97 and 2000)
I have a biggish (~3MB) file containing formulae like this
=IF($B$6="","",VLOOKUP($B$6,excumas!$E$25:$K$524,2 )), where it looks up data from a range on a sheet called excumas.
The second column  the one I want  I want contains folks' names.
It all works swimmingly for a value of B6 up to 333, but beyond that returns the value zero  that is, number zero.
Similarly other fields corresponding to values of B6 over 333 (which happen to be numbers, anyway.)
There's nothing wrong or odd about the data in the lookedup list.
This is annoying and dangerous.
Is excel getting 'tired', are there limits to the size of a lookedup list, what's the workaround?
Thanks!

20030319, 16:19 #7
 Join Date
 Jul 2002
 Location
 Pittsburgh, Pennsylvania, USA
 Posts
 11,225
 Thanks
 14
 Thanked 342 Times in 335 Posts
Re: using VLOOKUP command (2000)
Is the range
excumas!$E$25:$K$524
sorted in ascending order?
If it is NOT, you might not get the answer you want, since the search does NOT look at all entries. This VLOOKUP you have also does NOT just give EXACT matches, it will find "approximate" matches.
If you want an exact match, and the data in any order, then use:
=IF($B$6="","",VLOOKUP($B$6,excumas!$E$25:$K$524,2 ,false))
Steve

20030320, 16:15 #8
 Join Date
 Jan 2001
 Location
 Chelsea, Gtr London, United Kingdom
 Posts
 587
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
<hr>but the spreadsheet is infinite (or isn't it?)<hr>
Taken from Excel 2000 Help
"Worksheet size 65,536 rows by 256 columns" i.e. 16,777,216 cells per sheet.
Not infinite but quite a lot! <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>Steve H
IT Lecturer/Access Developer
O2K SR3/O2010; Win7Pro

20030324, 10:08 #9
 Join Date
 Sep 2002
 Location
 Birmingham, England
 Posts
 123
 Thanks
 0
 Thanked 0 Times in 0 Posts
Re: using VLOOKUP command (2000)
Thanks very much, Steve.
The column concerned is in ascending order (they're member numbers).
I've added the 'false' parameter (by zero, not false) to the formulas concerned, and hey, presto, they now work (on xl 2000) for all values of member number.
Can't understand this, but some things don't need to be understood if they work.
Will do same trick on xl 97 box at home and have every confidence...