Index/Match vs VLookup?
in [General]
|
Prev: Highlight duplicates
Next: clean up code a little
From: Ken on 7 Apr 2010 10:42 Excel2003 ... My understanding is ... an array "Index/Match" Formula calculates faster than a "VLookup" Formula ... If so, then I would like to use "Index/Match". Also, I often do use "Index/Match" as the Index can be either R or L of target cell. Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to copy all the way down the 2nd Col ... It seems to fail @ about 20,000 records down the 2nd Col (one clarifier ... I am on the same WorkSheet ... adjacent Cols) Above said ... Am I up against an "Index/Match" constraint here ... or ... as is generally the case ... Is this an oversight on my part? Please enlighten me ... Thanks for the guidance ... Kha
From: Bob Phillips on 7 Apr 2010 10:51 I just setup an INDEX(MATCH formula, copied it down to row 28,347 and it worked fine for me. What is your formula? -- HTH Bob "Ken" <Ken(a)discussions.microsoft.com> wrote in message news:B639EBD4-3D41-433B-9A7A-AB62F7971878(a)microsoft.com... > Excel2003 ... > > My understanding is ... an array "Index/Match" Formula calculates faster > than a "VLookup" Formula ... If so, then I would like to use > "Index/Match". > Also, I often do use "Index/Match" as the Index can be either R or L of > target cell. > > Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to > copy all the way down the 2nd Col ... It seems to fail @ about 20,000 > records > down the 2nd Col (one clarifier ... I am on the same WorkSheet ... > adjacent > Cols) > > Above said ... Am I up against an "Index/Match" constraint here ... or ... > as is generally the case ... Is this an oversight on my part? > > Please enlighten me ... Thanks for the guidance ... Kha > >
From: Jim Thomlinson on 7 Apr 2010 10:51 Post your formula... there is no constraint on index match. That being said index match is about 5% slower than VLookup... http://www.decisionmodels.com/optspeede.htm That being said index match is IMO the way to go for many reasons most noteably it is far less prone to error than VLookup. My best guess at your problem would have to with absolute vs relative references. -- HTH... Jim Thomlinson "Ken" wrote: > Excel2003 ... > > My understanding is ... an array "Index/Match" Formula calculates faster > than a "VLookup" Formula ... If so, then I would like to use "Index/Match". > Also, I often do use "Index/Match" as the Index can be either R or L of > target cell. > > Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to > copy all the way down the 2nd Col ... It seems to fail @ about 20,000 records > down the 2nd Col (one clarifier ... I am on the same WorkSheet ... adjacent > Cols) > > Above said ... Am I up against an "Index/Match" constraint here ... or ... > as is generally the case ... Is this an oversight on my part? > > Please enlighten me ... Thanks for the guidance ... Kha > >
From: Ken on 7 Apr 2010 14:48 Perhaps I misunderstood this older post (pasted @ bottom & in particular the response to Question 2) ... but seems I have also seen other post that indicated INDEX/MATCH to be a faster calculation than VLOOKUP ... The ironic thing about the following post is that it indicates INDEX/MATCH to be faster, but @ same time refers to the same website link you have in this post. Above said ... Thank you for responding to my post & for supporting these boards ... As far as INDEX/MATCH vs VLOOKUP calculation speed ... I guess it is up to my particular spreadsheet & my Stop Watch ... :) ... Kha **************************************************** Question 1: Yes, it would be more efficient. If the conditional test of the IF function is TRUE it doesn't continue to evaluate the value_if_false. For example: >=if(a1=0,0,formula) If A1 = 0 it doesn't continue to calculate "formula". Question 2: Yes, that will also help. Using a combination of INDEX/MATCH is more efficient than VLOOKUP. Visit this site for lots of info on efficiency: http://www.decisionmodels.com/ -- Biff Microsoft Excel MVP "EricK" <EricK(a)discussions.microsoft.com> wrote in message news:C9A95B1D-A6C9-4F85-9F6B-0F2F930010CA(a)microsoft.com... > I've "inherited" a very complicated spreadsheet at work, which runs quite > slowly. I'm going to try to speed it up a bit. Here are some things I've > thought of so far, but I'm not sure if they're going to work. > > 1. In some places there are complicated formulae depending on many input > cells. But if one of those cells is zero then the formula will calculate > to > zero as well. Would it speed things up to replace "=formula" with > "=if(a1=0,0,formula). In other words, does the IF function calculate both > parts following the condition (in which case this would just slow it down > further) or does it only calculate the relevant part in which case this > might > save a lot of calculation time. > > 2. Imagine the first 1000 rows column A has various numbers which will > always be integers in the range 1-10 and column B has a complex formula > which > depends on the figure in the nieghboring cell in column A and also on > other > fixed cells in the spreadsheet. Would it speed up the spreadsheet to have > a > small ten row table somewhere in the spreadsheet with the complex forumla > worked out for numbers 1-10 and use and replace the formula in column B > with > a VLOOKUP? In other words, how quick is a simple VLOOKUP compared to a > complex arithmetic calculation (eg "=a1*(1+$V$3)^(($g$7-$g$4)/365)")? > > Are there any other good tips for speeding up spreadsheets? > > Thanks, > > Eric "Jim Thomlinson" wrote: > Post your formula... there is no constraint on index match. That being said > index match is about 5% slower than VLookup... > > http://www.decisionmodels.com/optspeede.htm > > That being said index match is IMO the way to go for many reasons most > noteably it is far less prone to error than VLookup. > > My best guess at your problem would have to with absolute vs relative > references. > -- > HTH... > > Jim Thomlinson > > > "Ken" wrote: > > > Excel2003 ... > > > > My understanding is ... an array "Index/Match" Formula calculates faster > > than a "VLookup" Formula ... If so, then I would like to use "Index/Match". > > Also, I often do use "Index/Match" as the Index can be either R or L of > > target cell. > > > > Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to > > copy all the way down the 2nd Col ... It seems to fail @ about 20,000 records > > down the 2nd Col (one clarifier ... I am on the same WorkSheet ... adjacent > > Cols) > > > > Above said ... Am I up against an "Index/Match" constraint here ... or ... > > as is generally the case ... Is this an oversight on my part? > > > > Please enlighten me ... Thanks for the guidance ... Kha > > > >
From: Ken on 7 Apr 2010 14:55 Bob ... (Hi) Did you copy your INDEX(MATCH formula down the 1st 40,000 rows before you attempted to copy down the 28,347 rows? Above said ... I have had this difficulty with INDEX(MATCH before & have a note in my notes regarding this ... Consequently, in an effort to avoid I use VLOOKUP when I run into this issue ... Otherwise, I use INDEX(MATCH because as I previously stated I thought it to be a faster calculation & it had the advantage of L & R return values??? After today though ... I am uncertain which calculation may be faster??? My "Thanks" for supporting these boards ... There has been many lessons learned here ... Kha "Bob Phillips" wrote: > I just setup an INDEX(MATCH formula, copied it down to row 28,347 and it > worked fine for me. > > What is your formula? > > -- > > HTH > > Bob > > "Ken" <Ken(a)discussions.microsoft.com> wrote in message > news:B639EBD4-3D41-433B-9A7A-AB62F7971878(a)microsoft.com... > > Excel2003 ... > > > > My understanding is ... an array "Index/Match" Formula calculates faster > > than a "VLookup" Formula ... If so, then I would like to use > > "Index/Match". > > Also, I often do use "Index/Match" as the Index can be either R or L of > > target cell. > > > > Issue ... with 2 Cols of 40,000 records ... Index/Match Formula fails to > > copy all the way down the 2nd Col ... It seems to fail @ about 20,000 > > records > > down the 2nd Col (one clarifier ... I am on the same WorkSheet ... > > adjacent > > Cols) > > > > Above said ... Am I up against an "Index/Match" constraint here ... or ... > > as is generally the case ... Is this an oversight on my part? > > > > Please enlighten me ... Thanks for the guidance ... Kha > > > > > > > . >
|
Pages: 1 Prev: Highlight duplicates Next: clean up code a little |