Multiple Criteria Lookup Results
 

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)*(Ra nge3=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.

Multiple Criteria Lookup Results
 

Maybe

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



--
HTH

Bob

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

"Forgone" wrote in message
...
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)*(Ra nge3=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.

Multiple Criteria Lookup Results
 

Bob,

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

On Jul 15, 5:30*pm, "Bob Phillips" wrote:
Maybe

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

Multiple Criteria Lookup Results
 

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)

"Forgone" wrote in message
...
Bob,

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

On Jul 15, 5:30 pm, "Bob Phillips" wrote:
Maybe

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