Error Checking in Excel
Thanks Thanks:  0
Likes Likes:  0
Results 1 to 6 of 6

Thread: Does Vlookup Have Max # of Rows?

  1. #1
    Board Regular
    Join Date
    Mar 2002
    Location
    Irvine, CA
    Posts
    97
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Does anyone know where I can go to find out if there is a row capacity limit when doing Vlookup? I was using a table w/50,000 rows and having some challenges but did not know if it was because of the size of my table?

  2. #2
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    No. Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so.
    ~Anne Troy

  3. #3
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    On 2002-03-10 21:04, jevremovj wrote:
    Does anyone know where I can go to find out if there is a row capacity limit when doing Vlookup? I was using a table w/50,000 rows and having some challenges but did not know if it was because of the size of my table?
    I don't think VLOOKUP itself imposes any limits on the hight (or width) of a table.

  4. #4
    MrExcel MVP Aladin Akyurek's Avatar
    Join Date
    Feb 2002
    Location
    The Hague, NL
    Posts
    83,653
    Post Thanks / Like
    Mentioned
    33 Post(s)
    Tagged
    6 Thread(s)

    Default

    > Actually, I always suggest that people name their lookup range, and select the entire column(s) when they do so.

    Good practice, but not always desirable, especially when the lookup range must dynamically computed. Just curious: Why do you think naming would help here?

    Aladin

  5. #5
    MrExcel MVP Anne Troy's Avatar
    Join Date
    Feb 2002
    Location
    Westwood NJ
    Posts
    2,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Not applicable here as in correcting an issue, Aladin. More like a *while I'm at it*. I see many people not using named ranges in their VLOOKUPs and I think it makes them sooo much easier.


    ~Anne Troy

  6. #6
    Rest in Peace
    Join Date
    Feb 2002
    Posts
    1,582
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default

    Hi jevremovj

    The only limitation to the VLOOKUP is the total number of rows on an Excel Worksheet, ie 65536. You might find your problems were not having the optional range_lookup argument set to False. While I certainly agree with tuggy (Dreamboat) on naming ranges I must disagree on using an entire column, unless there is only a couple of formulae referencing the range.

    I constantly use dynamic ranges throughout my spreadsheets: http://www.ozgrid.com/Excel/DynamicRanges.htm
    as this helps restrict the cells needing to be referenced and thus calculated. For example if you have a data table that has 20,000 rows by 5 Columns 5 wide and you name the Columns A:E you are referencing an extra 227680 cells more than needed. use this in only 10 formulas and you adding a lot of overhead to Excels re-calculations (10*227680). You are better off using a dynamic ranges this way you are only referencing the extra 227680 cells once and not 10 times.

    BTW sorting your data can also help a lot.



    _________________
    Kind Regards
    Dave Hawley
    OzGrid Business Applications
    Microsoft Excel/VBA Training
    If it's Excel, then it's us!

    [ This Message was edited by: Dave Hawley on 2002-03-11 00:45 ]

Some videos you may like

User Tag List

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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