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 -