Complex Index Match Max return date and label formula

[EvanDef]

Hello Excel Gurus,

I have a problem that has been stifling me for the past 2 weeks. I have a data table that includes a primary product and its components i am trying to forecast for completion within a set of 30 day periods. The components must complete first within enough time to complete the primary for the given period. Otherwise the primary will bill for the following period. I have tried combinations of VLOOKUP, INDEX, MATCH, MAX, MIN and RANK.EQ all to no avail.

The goal is to lookup the order number, match the primary forecast to the component forecast if the PVA status is current. If the PVA Status is next, the primary would fall into the following PVA period. Also, if a component or primary is on hold, no forecast would be provided.

Any guidance in the proper direction would be greatly appreciated!

Component Forecast Product Type Order Number Order Status Primary Forecast PVA Status PVA Date Primary 3P44MZ550001 ASSIGNED Component 3P44MZ550001 ASSIGNED 2015 PVA4 Component 3P44MZ550001 FIRM CURRENT 3/18/2015 2015 PVA4 Component 3P44MZ550001 FIRM CURRENT 3/18/2015 2015 PVA4 Component 3P44MZ550001 FIRM CURRENT 3/18/2015 2015 PVA4 Component 3P44MZ550001 FIRM CURRENT 3/18/2015 Primary 3P44MZFL0009 HOLD Component 3P44MZFL0009 HOLD 2015 PVA4 Component 3P44MZFL0009 FIRM CURRENT 3/17/2015 2015 PVA4 Component 3P44MZFL0009 FIRM CURRENT 3/17/2015 2015 PVA4 Component 3P44MZFL0009 FIRM CURRENT 3/17/2015 Primary 3P511P7T0001 ASSIGNED 2015 PVA4 Component 3P511P7T0001 FIRM CURRENT 3/26/2015 2015 PVA4 Component 3P511P7T0001 FIRM CURRENT 3/26/2015 Primary NPA000103815 ASSIGNED Component NPA000103815 ASSIGNED 2015 PVA3 Component NPA000103815 FIRM NEXT 3/8/2015 2015 PVA3 Component NPA000103815 WORKING NEXT 3/9/2015 2015 PVA3 Component NPA000103815 WORKING CURRENT 3/4/2015 2015 PVA3 Component NPA000103815 FIRM CURRENT 3/5/2015 Primary WZ410138 ASSIGNED 2015 PVA3 Component WZ410138 FIRM NEXT 3/11/2015 2015 PVA3 Component WZ410138 FIRM NEXT 3/11/2015 2015 PVA3 Component WZ410138 SENT CURRENT 3/4/2015 2015 PVA3 Component WZ410138 SENT CURRENT 3/4/2015 <colgroup><col style="width: 60pt; mso-width-source: userset; mso-width-alt: 2925;" width="80"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 71pt; mso-width-source: userset; mso-width-alt: 3474;" width="95"> <col style="width: 76pt; mso-width-source: userset; mso-width-alt: 3693;" width="101"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" width="66"> <col style="width: 42pt; mso-width-source: userset; mso-width-alt: 2048;" width="56"> <col style="width: 50pt; mso-width-source: userset; mso-width-alt: 2413;" width="66"> <tbody> </tbody>

PVA Start Date PVA End Date PVA Forecast Label Completion Deadline RANK 1/16/2015 2/15/2015 2015 PVA2 2/6/2015 1 2/16/2015 3/15/2015 2015 PVA3 3/6/2015 2 3/16/2015 4/15/2015 2015 PVA4 4/7/2015 3 4/16/2015 5/15/2015 2015 PVA5 5/7/2015 4 <colgroup><col style="width: 56pt; mso-width-source: userset; mso-width-alt: 2742;" span="2" width="75"> <col style="width: 66pt; mso-width-source: userset; mso-width-alt: 3218;" width="88"> <col style="width: 62pt; mso-width-source: userset; mso-width-alt: 2998;" width="82"> <col style="width: 32pt; mso-width-source: userset; mso-width-alt: 1536;" width="42"> <tbody> </tbody>

<tbody>
</tbody>

 

[JeanRene]

Hello,

Assuming your first table starts in A1 and the secondary table is in J1, try this in ccolumn E:
=IF(F4="current",G4,IF(F4="next",(INDEX($M$2:$M$5,MATCH(G4,$J$2:$J$5,1)+1)),""))

My understandign was that, when there is "next" in column F, the primary forecast will be equal to the completion deadline of the next period (eg if status is "next" and PVA date = 3/8/2015 wich is in the 2nd period, then the result will be the completion deadline of the 3rd period)

I hope I understood correctly and that can help.