Calculation Speed (Index/Match vs Vlookup)?

[Ken]

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('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)))

WB1 ... Col D

=IF(OR($B2="",ISNA(INDEX(BTs!$H$1:$H$2000,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)))

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)),"",VLOOKUP($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($B2,BTs!$C$2:$F$2000,4,0)))

WB3 ... Col E

=IF(ISNA(VLOOKUP($B2,BTs!$C$2:$H$2000,6,0)),"",VLOOKUP($B2,BTs!$C$2:$H$2000,6,0))

Thanks for the guidance ... Kha

 

[Bob Phillips]

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)

 

[Pete_UK]

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

 

[Ken]

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]

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.

 

[Bob Phillips]

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.

 

[Pete_UK]

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