Speed of Formulas

  • So I know index with a match is faster than a vlookup. But is an index/match with criteria faster than a vlookup? Is it safe to assume that index/match is faster/much faster than index/match with criteria? Thanks.


    Have a large excel sheet and just trying to weigh pros and cons of what I am doing. Thanks for any input.

  • Re: Speed of Formulas


    First of all, I'm not sure how you "know" that INDEX/MATCH is faster than VLOOKUP. Perhaps you can add a link to a site or two which offers evidence in support of that opinion?


    Secondly, what precisely do you mean "with criteria"? You ask "But is an index/match with criteria faster than a vlookup", but if you have criteria than they should apply equally to VLOOKUP as well as to INDEX/MATCH, so your question should really be "But is an index/match with criteria faster than a vlookup with criteria".


    I think you need to give a few examples to explain what you are trying to do.


    Regards

  • Re: Speed of Formulas




    So some background. I have data set of five columns and about 200k rows. To right of 5 columns are series of other formulas. So the l could a join the criteria as it be looking up a date. Say in column A, I have formula to combine column B and C (=B2&C2). So I could match or look up into that or do criteria. Now mind you the column will need to go down 200k rows. So just trying to figure out what is more taxing on spreadsheet.



    http://www.exceluser.com/formu…s-better-than-vlookup.htm



    Above is link to why index match faster than vlookup. Also decided to index March more likely as it dynamic and I've recently accidentally added columns where I shouldn't and hence changed vlookup column.


    Any idea if any other methods to make more dynamic of spreadsheet between the criteria of lookup versus creating separate column.


    At some point was going to hardcore or create macro to hardcode 198k of the rows of data.


    As I will be adding data to the set should I have formulas down to 250k for added rows of data in future or name validate and use to change that way. Say I put in "column B" as a name and then instead of changing all formulas just change the name "column B" to include more rows. Would this be more dynamic and less taxing on spreadsheet itself. Thanks.

  • Re: Speed of Formulas


    If I understand you correctly, it's much, much more efficient to perform an INDEX/MATCH (or VLOOKUP) on a prior concatenated entry (of the individual criteria) - assuming this is possible - than to incorporate those criteria in-function.


    For example, rather than using:


    =INDEX(E1:E10,MATCH(1,(A1:A10="A")*(B1:B10="B")*(C1:C10="C"),0))


    which, additionally, requires entering as an array formula, we would first use an additional column within the worksheet in which to perform a suitable concatenation. For example, if we put, in D1:


    =A1&"|"&B1&"|"&C1


    (it's wise - if not always strictly necessary - to use some form of separator, e.g. |, between entries), and copy down to D10, then we can use the far, far more efficient:


    =INDEX(E1:E10,MATCH("A|B|C",D1:D10,0))


    Regards


    P.S. I can point to many reputable sources which disagree with the opinion that INDEX/MATCH is faster than VLOOKUP, e.g.:


    http://analystcave.com/excel-v…match-vs-sql-performance/
    http://www.decisionmodels.com/optspeede.htm


    More flexible, certainly. But faster? Not so sure.

  • Re: Speed of Formulas


    Thanks for feedback. Do you have any of those sources off hand that say vlookup faster than match?


    Speed I don't think is my main issue as lot of formulas be hard coded to reduce size. Perhaps issue is size of file but I'd assume size and speed are well correlated. Thanks