Results 1 to 9 of 9
  1. #1
    Star Lounger
    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.

  2. #2
    Uranium Lounger
    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

  3. #3
    Lounger
    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.

  4. #4
    Star Lounger
    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.

  5. #5
    WS Lounge VIP sdckapr's Avatar
    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

  6. #6
    2 Star Lounger
    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 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!

  7. #7
    WS Lounge VIP sdckapr's Avatar
    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

  8. #8
    4 Star Lounger SteveH's Avatar
    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

  9. #9
    2 Star Lounger
    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...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •