using VLOOKUP command (2000)

Topic

New Reply

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.

Reply | Quote

Replies

Why not just change the $C$5:$N$45 to $C$5:$N$500 or whatever the max size will ever be?

Reply | Quote

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.

Reply | Quote

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 looked-up list.
This is annoying and dangerous.
Is excel getting ‘tired’, are there limits to the size of a looked-up list, what’s the workaround?
Thanks!

Reply | Quote

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

Reply | Quote

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…

Reply | Quote

[indent]

---

but the spreadsheet is infinite (or isn’t it?)

---

[/indent]

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!

Reply | Quote

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.

Reply | Quote

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

Reply | Quote