Complex Index Match Max return date and label formula

E

EvanDef

New Member
Joined
Jan 6, 2014
Messages
21
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!
[TABLE="width: 500"]
<tbody>[TR]
[TD][TABLE="width: 415"]
<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>[TR]
[TD="width: 80, bgcolor: gray"]Component Forecast[/TD]
[TD="width: 88, bgcolor: gray"]Product Type[/TD]
[TD="width: 95, bgcolor: gray"]Order Number[/TD]
[TD="width: 101, bgcolor: gray"]Order Status[/TD]
[TD="width: 66, bgcolor: gray"]Primary Forecast[/TD]
[TD="width: 56, bgcolor: gray"]PVA Status[/TD]
[TD="width: 66, bgcolor: gray"]PVA Date[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"]Primary[/TD]
[TD="bgcolor: #F2DCDB"]3P44MZ550001[/TD]
[TD="bgcolor: #F2DCDB"]ASSIGNED[/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZ550001[/TD]
[TD="bgcolor: transparent"]ASSIGNED[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZ550001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/18/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZ550001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/18/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZ550001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/18/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZ550001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/18/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"]Primary[/TD]
[TD="bgcolor: #F2DCDB"]3P44MZFL0009[/TD]
[TD="bgcolor: #F2DCDB"]HOLD[/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZFL0009[/TD]
[TD="bgcolor: transparent"]HOLD[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZFL0009[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/17/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZFL0009[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/17/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P44MZFL0009[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/17/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"]Primary[/TD]
[TD="bgcolor: #F2DCDB"]3P511P7T0001[/TD]
[TD="bgcolor: #F2DCDB"]ASSIGNED[/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P511P7T0001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/26/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA4[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]3P511P7T0001[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/26/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"]Primary[/TD]
[TD="bgcolor: #F2DCDB"]NPA000103815[/TD]
[TD="bgcolor: #F2DCDB"]ASSIGNED[/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]NPA000103815[/TD]
[TD="bgcolor: transparent"]ASSIGNED[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]NPA000103815[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]NEXT[/TD]
[TD="bgcolor: transparent"]3/8/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]NPA000103815[/TD]
[TD="bgcolor: transparent"]WORKING[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]NEXT[/TD]
[TD="bgcolor: transparent"]3/9/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]NPA000103815[/TD]
[TD="bgcolor: transparent"]WORKING[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/4/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]NPA000103815[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/5/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"]Primary[/TD]
[TD="bgcolor: #F2DCDB"]WZ410138[/TD]
[TD="bgcolor: #F2DCDB"]ASSIGNED[/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[TD="bgcolor: #F2DCDB"] [/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]WZ410138[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]NEXT[/TD]
[TD="bgcolor: transparent"]3/11/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]WZ410138[/TD]
[TD="bgcolor: transparent"]FIRM[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]NEXT[/TD]
[TD="bgcolor: transparent"]3/11/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]WZ410138[/TD]
[TD="bgcolor: transparent"]SENT[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/4/2015[/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"]2015 PVA3[/TD]
[TD="bgcolor: transparent"]Component[/TD]
[TD="bgcolor: transparent"]WZ410138[/TD]
[TD="bgcolor: transparent"]SENT[/TD]
[TD="bgcolor: transparent"] [/TD]
[TD="bgcolor: transparent"]CURRENT[/TD]
[TD="bgcolor: transparent"]3/4/2015[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[TD][TABLE="width: 272"]
<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>[TR]
[TD="class: xl76, width: 75, bgcolor: gray"]PVA Start Date[/TD]
[TD="class: xl76, width: 75, bgcolor: gray"]PVA End Date[/TD]
[TD="class: xl77, width: 88, bgcolor: gray"]PVA Forecast Label[/TD]
[TD="class: xl76, width: 82, bgcolor: gray"]Completion Deadline[/TD]
[TD="class: xl77, width: 42, bgcolor: gray"]RANK[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]1/16/2015[/TD]
[TD="class: xl74, bgcolor: transparent"]2/15/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]2015 PVA2[/TD]
[TD="class: xl74, bgcolor: transparent"]2/6/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]1[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]2/16/2015[/TD]
[TD="class: xl74, bgcolor: transparent"]3/15/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]2015 PVA3[/TD]
[TD="class: xl74, bgcolor: transparent"]3/6/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]2[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]3/16/2015[/TD]
[TD="class: xl74, bgcolor: transparent"]4/15/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]2015 PVA4[/TD]
[TD="class: xl74, bgcolor: transparent"]4/7/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]3[/TD]
[/TR]
[TR]
[TD="class: xl74, bgcolor: transparent"]4/16/2015[/TD]
[TD="class: xl74, bgcolor: transparent"]5/15/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]2015 PVA5[/TD]
[TD="class: xl74, bgcolor: transparent"]5/7/2015[/TD]
[TD="class: xl73, bgcolor: transparent"]4[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]
 

Excel Facts

Add Bullets to Range
Select range. Press Ctrl+1. On Number tab, choose Custom. Type Alt+7 then space then @ sign (using 7 on numeric keypad)
Sort by date Sort by votes
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.
 
Upvote 0
You must log in or register to reply here.

Similar threads

M
  • Question Question
Help with FilterXML formula
Replies
0
Views
1K
montex01
M
E
  • Question Question
Can anyone help me get my VBA code working? I have spent tons of time but can't quite fix it.
Replies
3
Views
3K
profray
P
N
  • Question Question
Using MAX() to output multiple values from one formula
Replies
0
Views
2K
nicklasx
N
G
  • Question Question
Conditional formating where there are 2 values in a cell
Replies
3
Views
1K
Godders199
G
A
  • Question Question
Add Sequence numbers Based on Dates
Replies
1
Views
3K
Jeffrey Mahoney
Jeffrey Mahoney

Forum statistics

Threads
1,223,227
Messages
6,170,848
Members
452,361
Latest member
d3ad3y3

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top