| Home |
| Search |
| Today's Posts |
|
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
Calculation Speed (Index/Match vs Vlookup)?
Excel2003 ... I have identical WorkBooks where I am testing Index/Match vs
Vlookup before I expand ... In Cols C-D-E (Range 2:2000) I entered Index/Match Formula in 1 WB & Vlookup in 2nd WB ... Both formulas appear to be returning same results ... However, Index/Match appears to be calculating faster ... From those that are intimate with Excel ... Does this make sense? .... Also, is there a way I can simplify the formulas I have without creating a "helper" Col? WB1 ... Col C =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col D =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col E =IF($B2="","",IF(ISNA(INDEX(BTs!$F$1:$F$2000,MATCH ('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",IF(INDEX(BTs!$F $1:$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))=0,"",INDEX(BTs!$F$1 :$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))))) WB2 ... Col C =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,3,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,3,0)) WB2 ... Col D =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)),"",IF( VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)="","",VLOOKUP($B 2,BTs!$C$2:$F$2000,4,0))) WB3 ... Col E =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,6,0)) Thanks for the guidance ... Kha |
|
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
You can simplify the formula a tad as it is the Match that errors
=IF(OR($B2="",ISNA(MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",INDEX(BTs!$H$1: $H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... I have identical WorkBooks where I am testing Index/Match vs Vlookup before I expand ... In Cols C-D-E (Range 2:2000) I entered Index/Match Formula in 1 WB & Vlookup in 2nd WB ... Both formulas appear to be returning same results ... However, Index/Match appears to be calculating faster ... From those that are intimate with Excel ... Does this make sense? ... Also, is there a way I can simplify the formulas I have without creating a "helper" Col? WB1 ... Col C =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col D =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col E =IF($B2="","",IF(ISNA(INDEX(BTs!$F$1:$F$2000,MATCH ('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",IF(INDEX(BTs!$F $1:$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))=0,"",INDEX(BTs!$F$1 :$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))))) WB2 ... Col C =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,3,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,3,0)) WB2 ... Col D =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)),"",IF( VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)="","",VLOOKUP($B 2,BTs!$C$2:$F$2000,4,0))) WB3 ... Col E =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,6,0)) Thanks for the guidance ... Kha |
|
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
What's wrong in having a helper column? As it stands, you have 6 MATCH
functions in each row, but you can have your checks once only in the helper column (C) as: =IF(OR($B2="",ISNA(MATCH('CC Vari'!$B2,BTs!$C$1:$C $2000,0)),"",MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0)) and then your other formulae take the form: =IF($C2="","",INDEX(BTs!$H$1:$H$2000,$C2)) in D2, and so on. This will calculate much more quickly, particularly with several thousand rows. See here for other speed tips: http://www.decisionmodels.com/optspeede.htm Hope this helps. Pete On Oct 4, 12:36 pm, Ken wrote: Excel2003 ... I have identical WorkBooks where I am testing Index/Match vs Vlookup before I expand ... In Cols C-D-E (Range 2:2000) I entered Index/Match Formula in 1 WB & Vlookup in 2nd WB ... Both formulas appear to be returning same results ... However, Index/Match appears to be calculating faster ... From those that are intimate with Excel ... Does this make sense? ... Also, is there a way I can simplify the formulas I have without creating a "helper" Col? WB1 ... Col C =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col D =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col E =IF($B2="","",IF(ISNA(INDEX(BTs!$F$1:$F$2000,MATCH ('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",IF(INDEX(BTs!$F $1:$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))=0,"",INDEX(BTs!$F$1 :$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))))) WB2 ... Col C =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,3,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000*,3,0)) WB2 ... Col D =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)),"",IF( VLOOKUP($B2,BTs!$C$2:$H$2*000,4,0)="","",VLOOKUP($ B2,BTs!$C$2:$F$2000,4,0))) WB3 ... Col E =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000*,6,0)) Thanks for the guidance ... Kha |
|
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
Bob / Pete ... Thanks
Calculation speed (Index/Match vs Vlookup) ... I initially used Vlookup as I thought it was simpler formula ... However, calculation speed seems to favor Index/Match (this application anyway). Bob ... once I get a formula to work, if I then need to disregard "errors" I simply put ... if the Formula produces an error,"",Formula. So in the "tip" you provided I realized you were saying the error was being found by the MATCH portion of my formula so I really did not need to have the INDEX portion when checking for the ISNA error ... So, I edited as you suggested .... :) Pete ... recommendation to use HELPER Col ... I am confident this is correct .... & I am sure this will not make sense to those that are intimate with Excel ... However, bottom line is ... I am just an Excel hack that prefers not to work with HELPER Cols. And, I certainly do realize this is a short-coming on my part (comfort zone). Above said ... for all of you that are intimate with Excel & offer so much support & guidance to these boards on a daily basis ... My many Thanks for helping me better understand Excel & for helping me to expand my "Comfort Zone" ... Kha "Bob Phillips" wrote: You can simplify the formula a tad as it is the Match that errors =IF(OR($B2="",ISNA(MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",INDEX(BTs!$H$1: $H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... I have identical WorkBooks where I am testing Index/Match vs Vlookup before I expand ... In Cols C-D-E (Range 2:2000) I entered Index/Match Formula in 1 WB & Vlookup in 2nd WB ... Both formulas appear to be returning same results ... However, Index/Match appears to be calculating faster ... From those that are intimate with Excel ... Does this make sense? ... Also, is there a way I can simplify the formulas I have without creating a "helper" Col? WB1 ... Col C =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col D =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col E =IF($B2="","",IF(ISNA(INDEX(BTs!$F$1:$F$2000,MATCH ('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",IF(INDEX(BTs!$F $1:$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))=0,"",INDEX(BTs!$F$1 :$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))))) WB2 ... Col C =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,3,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,3,0)) WB2 ... Col D =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)),"",IF( VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)="","",VLOOKUP($B 2,BTs!$C$2:$F$2000,4,0))) WB3 ... Col E =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000,6,0)) Thanks for the guidance ... Kha |
|
#5
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
"Ken" wrote in message ... Bob / Pete ... Thanks Calculation speed (Index/Match vs Vlookup) ... I initially used Vlookup as I thought it was simpler formula ... However, calculation speed seems to favor Index/Match (this application anyway). I think INDEX/MATCH would be faster, by virtue of the fact that VLOOKUP is more versatile/flexible, it has mork work to do to support this flexibility. INDEX/MATCH is more specific, so can be coded more tightly. Bob ... once I get a formula to work, if I then need to disregard "errors" I simply put ... if the Formula produces an error,"",Formula. So in the "tip" you provided I realized you were saying the error was being found by the MATCH portion of my formula so I really did not need to have the INDEX portion when checking for the ISNA error ... So, I edited as you suggested ... :) That's right. By removing the INDEX part in the error trap, you make the code more efficient as that part of the formula does not need to be evaluated. So it meets your overall goal, maybe in a small way, but it all helps. |
|
#6
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
"Ken" wrote in message ... Bob / Pete ... Thanks Pete ... recommendation to use HELPER Col ... I am confident this is correct ... & I am sure this will not make sense to those that are intimate with Excel ... However, bottom line is ... I am just an Excel hack that prefers not to work with HELPER Cols. And, I certainly do realize this is a short-coming on my part (comfort zone). Forgot to say earlier, don't dismiss helper columns so quickly. They do make the formulae more maintainable, and remember, you can always hide them so that they don't get in the way. |
|
#7
Posted to microsoft.public.excel.misc
|
|||
|
|||
|
Calculation Speed (Index/Match vs Vlookup)?
Maybe one day you'll be ready to branch out a bit ... <bg
By the way, what are the results of your timing tests using the different approaches? Pete On Oct 5, 2:23 pm, Ken wrote: Bob / Pete ... Thanks Calculation speed (Index/Match vs Vlookup) ... I initially used Vlookup as I thought it was simpler formula ... However, calculation speed seems to favor Index/Match (this application anyway). Bob ... once I get a formula to work, if I then need to disregard "errors" I simply put ... if the Formula produces an error,"",Formula. So in the "tip" you provided I realized you were saying the error was being found by the MATCH portion of my formula so I really did not need to have the INDEX portion when checking for the ISNA error ... So, I edited as you suggested ... :) Pete ... recommendation to use HELPER Col ... I am confident this is correct ... & I am sure this will not make sense to those that are intimate with Excel ... However, bottom line is ... I am just an Excel hack that prefers not to work with HELPER Cols. And, I certainly do realize this is a short-coming on my part (comfort zone). Above said ... for all of you that are intimate with Excel & offer so much support & guidance to these boards on a daily basis ... My many Thanks for helping me better understand Excel & for helping me to expand my "Comfort Zone" ... Kha "Bob Phillips" wrote: You can simplify the formula a tad as it is the Match that errors =IF(OR($B2="",ISNA(MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",INDEX(BTs!$H$1: $H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) -- HTH Bob (there's no email, no snail mail, but somewhere should be gmail in my addy) "Ken" wrote in message ... Excel2003 ... I have identical WorkBooks where I am testing Index/Match vs Vlookup before I expand ... In Cols C-D-E (Range 2:2000) I entered Index/Match Formula in 1 WB & Vlookup in 2nd WB ... Both formulas appear to be returning same results ... However, Index/Match appears to be calculating faster ... From those that are intimate with Excel ... Does this make sense? ... Also, is there a way I can simplify the formulas I have without creating a "helper" Col? WB1 ... Col C =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col D =IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,MATCH('C C Vari'!$B2,BTs!$C$1:$C$2000,0)))),"",INDEX(BTs!$H$1 :$H$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))) WB1 ... Col E =IF($B2="","",IF(ISNA(INDEX(BTs!$F$1:$F$2000,MATCH ('CC Vari'!$B2,BTs!$C$1:$C$2000,0))),"",IF(INDEX(BTs!$F $1:$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))=0,"",INDEX(BTs!$F$1 :$F$2000,MATCH('CC Vari'!$B2,BTs!$C$1:$C$2000,0))))) WB2 ... Col C =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,3,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000*,3,0)) WB2 ... Col D =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,4,0)),"",IF( VLOOKUP($B2,BTs!$C$2:$H$2*000,4,0)="","",VLOOKUP($ B2,BTs!$C$2:$F$2000,4,0))) WB3 ... Col E =IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLO OKUP($B2,BTs!$C$2:$H$2000*,6,0)) Thanks for the guidance ... Kha- Hide quoted text - - Show quoted text - |
| Reply |
| Thread Tools | Search this Thread |
| Display Modes | |
|
|
Similar Threads
|
||||
| Thread | Forum | |||
| index Match, or Vlookup Match.. | Excel Worksheet Functions | |||
| vlookup, match, index: all some or one? | Excel Discussion (Misc queries) | |||
| Index,match, vlookup? | Excel Discussion (Misc queries) | |||
| VLookup or Index Match or ??? | Excel Worksheet Functions | |||
| VLookup or Index Match ? | Excel Worksheet Functions | |||
Linear Mode
