Lookup 3 different conditions and return (1) value

[Craig de Gail]

Here is my problem which i need help with:-
In the Apr-09 column below, I need to return 1 value which is contained in a separate worksheet (within the same workbook). The problem is that this value is based on 3 conditions:-

• The Patch ID
• tHE Work Type
• The monthyear

All three sets of criteria exist in the other worksheet but I do not know how to reference them all in one simple formula. Can anyone help please.





<TABLE style="WIDTH: 431pt; BORDER-COLLAPSE: collapse" cellSpacing=0 cellPadding=0 width=573 border=0 x:str><COLGROUP><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" width=89><COL style="WIDTH: 139pt; mso-width-source: userset; mso-width-alt: 6765" width=185><COL style="WIDTH: 91pt; mso-width-source: userset; mso-width-alt: 4425" width=121><COL style="WIDTH: 67pt; mso-width-source: userset; mso-width-alt: 3254" span=2 width=89><TBODY><TR style="HEIGHT: 18.75pt" height=25><TD class=xl68 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 364pt; BORDER-BOTTOM: #c0c0c0; HEIGHT: 18.75pt; BACKGROUND-COLOR: transparent; mso-ignore: colspan" width=484 colSpan=4 height=25>Total Install & Maintenance Activity Volume</TD><TD style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: #c0c0c0; BORDER-LEFT: #c0c0c0; WIDTH: 67pt; BORDER-BOTTOM: #c0c0c0; BACKGROUND-COLOR: transparent" width=89></TD></TR><TR style="HEIGHT: 13.5pt" height=18><TD class=xl70 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: windowtext 1pt solid; HEIGHT: 13.5pt; BACKGROUND-COLOR: #99ccff" height=18>Patch ID</TD><TD class=xl71 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: #99ccff" x:str="Work Type ">Work Type </TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff" x:num="39904">Apr-09</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff" x:num="39934">May-09</TD><TD class=xl69 style="BORDER-RIGHT: windowtext 1pt solid; BORDER-TOP: windowtext 1pt solid; BORDER-LEFT: #c0c0c0; BORDER-BOTTOM: windowtext 1pt solid; BACKGROUND-COLOR: #99ccff" x:num="39965">Jun-09</TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl75 style="BORDER-RIGHT: #c0c0c0; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 1pt solid; BORDER-BOTTOM: black 1pt solid; HEIGHT: 131.25pt; BACKGROUND-COLOR: transparent" height=175 rowSpan=10>P3</TD><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent">CAC INSTALL_ULL</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray 0.5pt solid; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CAC TASK30_ADSL</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>CBC FAULT</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NBC ADSL_CMUX</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NBC NETWORK_BASIC_CUST</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>NBC ULL</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PBC INSTALL</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEC SIMPLEX_FAULT</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 12.75pt" height=17><TD class=xl72 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 12.75pt; BACKGROUND-COLOR: transparent" height=17>PEC SIMPLEX_INSTALL</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR><TR style="HEIGHT: 16.5pt" height=22><TD class=xl73 style="BORDER-RIGHT: windowtext 0.5pt solid; BORDER-TOP: windowtext; BORDER-LEFT: windowtext 0.5pt solid; WIDTH: 139pt; BORDER-BOTTOM: windowtext 0.5pt solid; HEIGHT: 16.5pt; BACKGROUND-COLOR: transparent" width=185 height=22>PEC WHOLESALE FAULT</TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray 0.5pt solid; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD><TD class=xl74 style="BORDER-RIGHT: gray 0.5pt solid; BORDER-TOP: gray; BORDER-LEFT: gray; BORDER-BOTTOM: gray 0.5pt solid; BACKGROUND-COLOR: transparent"> </TD></TR></TBODY></TABLE>

 

[shemayisroel]

Example - if something matches P3 & NBC ULL & Apr 09 what did you want to return?

 

[shemayisroel]

The below is a look up with 2 different values - I think this should help you in the right direction

http://www.mrexcel.com/forum/showthread.php?t=34384