Vlookup Causing Slow Calculations

  • Hi,
    I am trying to lookup data series via a key(ISIN or Valor). I have built a macro that works but it is very slow when I use it for a larger file. I have attached a small example.
    (normal file is about 15000 line)

    Maybe there is a way to return a complete data row after identifying a match.

    Your help is very much appreciated.
    Kind regards

    This is the code:
    (also in attachment with data sample)

  • Re: Speed Up A Lookup Macro

    Initially it was a vlookup but the file became too large and very slow.
    The vlookup caused very long re-calculations.

    Isn't there a way to lookup a value in column a and then return the complete series from another table via a macro?

    Thanks for your Help!!

  • Re: Speed Up A Lookup Macro

    I really think I have to include it in teh existing macros in this file.
    I am not sure if I fully understand your sugegstion.

    This file is linked to a db via a pivot. So first you have to find a group of "lookup items(isins/valors)" via the pivot. Step 2 is what I showed in the macro; you have to retrieve the performance-series over time(120 periods) for this "group of isins/valors"


  • Re: Speed Up A Lookup Macro

    The database just has the names (isin/valors) and classifications of the lookup items(investment products). The performance data comes from another source. So basically I filter the database in order to get a peergroup with the same classifications and then I lookup the corresponding performance rows in the performance datafiles("series" in my example)


  • Re: Speed Up A Lookup Macro

    Your code below seems to be looking at 20,000 cells and comparing to a set value.

    Do Until lRow = 20000 Or Sheets(sWK2).Cells(lRow, lCol) = Cells(jRow, jCol) ' first  search for valor
    lRow = lRow + 1 

    First of all, it seems like 20,000 is an arbitrary number, and you are probably searching thru a lot of blank cells. Instead of using 20,000, you should probably stop at the last used cell.

    Secondly, it seems like it would be a lot quicker to use Find, e.g.

    Dim rng As Range
    With Sheets(sWK2)
    Set rng = Range(.Cells(lRow, lCol), .Cells(.Cells.Rows.Count, lCol).End(xlUp))
    lRow = rng.Find(What:=Cells(jRow, jCol), After:=.Cells(lRow, lCol), _
          LookIn:=xlValues, LookAt:=xlWhole, SearchOrder:=xlByRows, SearchDirection:=xlNext).Row
    End With
  • Re: Speed Up A Lookup Macro

    Native Excel Functions are ALWAYS much faster than a replacement Custom Function.

    The single best way to speed up VLOOKUP is to sort the left most column and use True for Range_lookup, or omit it.

    The minute recalculations become longer than about 4-6 secs, you have a Spreadsheet Design issue that should be fixed and NEVER catered to.

  • Re: Vlookup Causing Slow Calculations

    The workbook is probably wrongly designed (i.e. changing pivot that causes 20000 rows and 120 column to refresh). The vlookups take much longer than 6 secs. Unfortunately I can't change the design, there are too many dependencies.

    Thanks for the Find tip turtle44. This works perfectly and much faster.

  • Re: Vlookup Causing Slow Calculations

    I would suggest you read up on the FIND METHOD if you are going to use that code. Some options MUST be set each time the Find Method is used.

    You will also see that COUNTIF can be used to restrict the loop.

  • Re: Vlookup Causing Slow Calculations



    Native Excel Functions are ALWAYS much faster than a replacement Custom Function

    I don't agree with you; certainly they are more practical because they save you plenty of typing, but please take a look at this example:

    Custom vs Built-in: 14:1 Speed gain.


Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!