Index Match tough formulas

[baker_89]

I have some terminology that may be confusing but I hope you can make sense of it.

I have a table on sheet2 named Table1 with

B2 is a drop down list that changes the pan type to be shown which all formulas refer to for searching
B6 I want to show the latest date the word "Glazed" appears in table col F
C6 same just the word "Straightened"
D6 same just the word "Cleaned"

B9 I have a formula already for this
C9 I want to show the total number of time the word "Glazed" shows up in table col F
D9 I want to search by the latest date the pan has but show table col J (col J looks like this, "3309 ea") just need the digits

B13 the same row as C6 but show table col K value
C13 Average time between dates that have text "glazed" in months is fine if not days
D13 would be the average time period between multiplied by the last cost for the remainder average cycle times left in the a rolling 12 month period (this is to show as time period between cycles shortens the life of the pan is reaching its limit) or Would it be cost effective to continue re-glazing or purchase new

SHEET1​

[TABLE="class: grid, width: 473"]
<colgroup style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;" span="3"><col style="border-collapse: collapse; width: auto;"></colgroup><tbody style="border-collapse: collapse; width: auto;">[TR]
[TD][/TD]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD][/TD]
[TD]Pan Type[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD][/TD]
[TD]Reg[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD][/TD]
[TD]Last Glazed On[/TD]
[TD]Last Straightened[/TD]
[TD]Last Cleaned[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6[/TD]
[TD][/TD]
[TD]10/31/2018[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[TD]Pan Age[/TD]
[TD]# of Glaze Cycles[/TD]
[TD]Last Pan Count[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[TD]1/1/2011[/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD][/TD]
[TD]7 Years 10 months[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]11[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]12[/TD]
[TD][/TD]
[TD]Last Glazing Cost[/TD]
[TD]Avg Life of Glaze[/TD]
[TD]Exp Yearly Cost[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]13[/TD]
[TD][/TD]
[TD] [/TD]
[TD] [/TD]
[TD] [/TD]
[TD][/TD]
[/TR]
[TR]
[TD]14[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]15[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

SHEET2 TABLE HEADINGS​

[TABLE="class: grid, width: 1241"]
<colgroup style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;" span="2"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;" span="2"><col style="border-collapse: collapse; width: auto;"><col style="border-collapse: collapse; width: auto;"></colgroup><tbody style="border-collapse: collapse; width: auto;">[TR]
[TD]A[/TD]
[TD]B[/TD]
[TD]C[/TD]
[TD]D[/TD]
[TD]E[/TD]
[TD]F[/TD]
[TD]G[/TD]
[TD]H[/TD]
[TD]I[/TD]
[TD]J[/TD]
[TD]K[/TD]
[/TR]
[TR]
[TD]Purchase Order ID[/TD]
[TD]Purchase Order Item[/TD]
[TD]Supplier #[/TD]
[TD]Supplier[/TD]
[TD]Pan Type[/TD]
[TD]Request Type[/TD]
[TD]Purchase Order Creation Date[/TD]
[TD]Net Price[/TD]
[TD]Price Unit[/TD]
[TD]Ordered Quantity[/TD]
[TD]Ordered Net Value/Limit[/TD]
[/TR]
</tbody>[/TABLE]

 

[baker_89]

I've made headway with B6:D6 with this formula, =MAX(IF((Table1[Pan Type]=$B$3)*(Table1[Request Type]=B6),Table1[Purchase Order Creation Date]))

But now I need to finish the lower half, I think I am on the right track but I cannot get the last pan count and cost to work yet. When I do it just shows the largest value in the list instead of the most recent.

I still don't know where to start on the average time between dates. (preferably in days)