Multiple Criteria Lookup Results

[Forgone]

I have used the three tier lookup formula done by using the "Bob
Ulmas" technique that I found on the http://www.mvps.org/dmcritchie/excel/vlookup.htm
website but am trying to tweak it a bit.

I have a table with comments where not all of the lines do not
actually have a comment and the result being displayed is 0 in the
cell.

I have used this formula....

=IF(ISERROR(INDEX(O9B.Comments,MATCH(1,
(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),
0))),"",INDEX(O9B.Comments,MATCH(1,
(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),
0)))

or without the IF(ISERROR(

INDEX(O9B.Comments,MATCH(1,
(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),
0))

The formula is working (Control+Shift+Enter) but any suggestions as to
how to get rid of the 0 as a result of no comments being provided?

Out of curiousity, would

VLOOKUP({(Range1=Criteria1)*(Range2=Criteria2)*(Range3=Criteria3)},....
work?

I did however, just tried a random thing which was to have a custom
cell format as "" which appears to be working..... for now.

 

[Bob Phillips]

Maybe

=IF(ISERROR(INDEX(O9B.Comments,
MATCH(1,(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),0))),"",
IF(INDEX(O9B.Comments,
MATCH(1,(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),0))=0,"",
INDEX(O9B.Comments,
MATCH(1,(O9B.CCB=VALUE(BCostCentre))*(O9B.CCD=VALUE(BFund))*(O9B.CCC=$C166),0))))



--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

 

[Forgone]

Bob,

Would this work using VBA in an effort to reduce the amount of time it
would take to process this equation?

 

[Bob Phillips]

Although it looks complex, if it is sparsely used, this is not that
expensive a formula.

--
HTH

Bob

(there's no email, no snail mail, but somewhere should be gmail in my addy)

Bob,

Would this work using VBA in an effort to reduce the amount of time it
would take to process this equation?