Consulting

Results 1 to 3 of 3

Thread: Speeding Up Vlookups

  1. #1
    VBAX Master
    Joined
    Jun 2006
    Posts
    1,091
    Location

    Speeding Up Vlookups

    I use a lot of Vlookups in code and was wondering if there are any tips to speed them up? Examples is it quicker if the Database list is sorted numerically? Is it faster to use True or False for range lookup? any other ways to speed it up? I already have screen updating off.
    Thank You,
    Daniel Blois
    http://studenthacker.blogspot.com/

  2. #2
    It is MUCH faster if the lookup column is sorted. If using MATCH, set the last argument to something other than zero (depending on sort order, look in Help) and remember to check if it returns an exact match.
    Regards,

    Jan Karel Pieterse
    Excel MVP jkp-ads.com

  3. #3
    Distinguished Lord of VBAX VBAX Grand Master Bob Phillips's Avatar
    Joined
    Apr 2005
    Posts
    25,443
    Location
    Quote Originally Posted by Djblois
    I use a lot of Vlookups in code and was wondering if there are any tips to speed them up? Examples is it quicker if the Database list is sorted numerically? Is it faster to use True or False for range lookup? any other ways to speed it up? I already have screen updating off.
    Using VLOOKUPs in code is not exactly efficient, calling out to Excel is a costly verhead. Have you tried using Find?
    ____________________________________________
    Nihil simul inventum est et perfectum

    Abusus non tollit usum

    Last night I dreamed of a small consolation enjoyed only by the blind: Nobody knows the trouble I've not seen!
    James Thurber

Posting Permissions

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