Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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. |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
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)))) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
V lookup with 2 criteria to return results for multiple columns | Excel Worksheet Functions | |||
Index & Match functions - multiple criteria and multiple results | Excel Worksheet Functions | |||
Multiple criteria with multiple results in one cell | Excel Worksheet Functions | |||
Multiple results from multiple criteria using IF function | Excel Discussion (Misc queries) | |||
Multi-criteria lookup with Multiple results | Excel Worksheet Functions |