Pulling last cost of items

[Jongo1]

I have a sheet set up like such:



[TABLE="width: 174"]
<tbody>[TR]
[TD="class: xl65, width: 93"]Item No.[/TD]
[TD="class: xl65, width: 74"]Purch Date[/TD]
[TD="class: xl65, width: 64"] Cost[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="class: xl66, align: right"]1/18/2016[/TD]
[TD="align: right"]12.4[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD="class: xl66, align: right"]2/1/2016[/TD]
[TD="align: right"]11.12[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="class: xl66, align: right"]3/22/2016[/TD]
[TD="align: right"]43.2[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD="class: xl66, align: right"]4/18/2016[/TD]
[TD="align: right"]55[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]4/22/2016[/TD]
[TD="align: right"]100[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]5/13/2016[/TD]
[TD="align: right"]125[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD="class: xl66, align: right"]6/17/2016[/TD]
[TD="align: right"]300[/TD]
[/TR]
</tbody>[/TABLE]


It goes on and on for 20,000 rows. What I need to do is extract the most recent cost by date for each item and then I need to extract the date when that last purchase was made for that item. I feel like its a simple formula that I can't think of.

 

[mrhstn]

Assuming this data set, all formulas will need to be adjusted to fit your needs

Book1
	A	B	C
1	Item No.	Purch Date	Cost
2	Item 1	18/01/2016	12.4
3	Item 1	01/02/2016	11.12
4	Item 2	22/03/2016	43.2
5	Item 2	18/04/2016	55
6	Item 3	22/04/2016	100
7	Item 3	13/05/2016	125
8	Item 3	17/06/2016	300
Sheet1

Book1
	G	H	I	J
1	Item No.	Last Purchased	Last Price Paid	Non-Array
2	Item 1	01/02/2016	11.12	11.12
3	Item 2	18/04/2016	55	55
4	Item 3	17/06/2016	300	300
Sheet1

Cell Formulas
Range	Formula
H2	=AGGREGATE(14, 6, ($B$2:$B$8)/($A$2:$A$8=G2), 1)
J2	=INDEX($C$2:$C$8, AGGREGATE(14, 6, (ROW($A$2:$A$8)-ROW($A$2)+1)/(($A$2:$A$8=G2)*($B$2:$B$8=H2)), 1))
I2	{=INDEX($C$2:$C$8, MATCH(G2&H2, $A$2:$A$8&$B$2:$B$8, 0))}
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[Jongo1]

Assuming this data set, all formulas will need to be adjusted to fit your needs

	A	B	C
Item No.	Purch Date	Cost
Item 1
Item 1
Item 2
Item 2
Item 3
Item 3
Item 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18/01/2016[/TD]
[TD="align: right"]12.4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]43.2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22/04/2016[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]13/05/2016[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]

</tbody>

Sheet1​

	G	H	I	J
Item No.	Last Purchased	Last Price Paid	Non-Array
Item 1
Item 2
Item 3

<tbody>
[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]
[TD="align: right"]300[/TD]

</tbody>

Sheet1​

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Worksheet Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]H2[/TH]
[TD="align: left"]=AGGREGATE(14, 6, ($B$2:$B$8)/($A$2:$A$8=G2), 1)[/TD]
[/TR]
[TR]
[TH]J2[/TH]
[TD="align: left"]=INDEX($C$2:$C$8, AGGREGATE(14, 6, (ROW($A$2:$A$8)-ROW($A$2)+1)/(($A$2:$A$8=G2)*($B$2:$B$8=H2)), 1))[/TD]
[/TR]
</tbody>[/TABLE]
[/TD]
[/TR]
</tbody>[/TABLE]

[TABLE="width: 85%"]
<tbody>[TR]
[TD]Array Formulas[TABLE="width: 100%"]
<tbody>[TR]
[TH]Cell[/TH]
[TH="align: left"]Formula[/TH]
[/TR]
[TR]
[TH]I2[/TH]
[TD="align: left"]{=INDEX($C$2:$C$8, MATCH(G2&H2, $A$2:$A$8&$B$2:$B$8, 0))}[/TD]
[/TR]
</tbody>[/TABLE]
Entered with Ctrl+Shift+Enter. If entered correctly, Excel will surround with curly braces {}.
Note: Do not try and enter the {} manually yourself[/TD]
[/TR]
</tbody>[/TABLE]

Thank you very much, but I noticed a huge flaw in how I reported this. There is another big element to it all.


This is incorrect

Item No.	Purch Date	Cost
Item 1
Item 1
Item 2
Item 2
Item 3
Item 3
Item 3

<tbody>
[TH="align: center"]A[/TH]
[TH="align: center"]B[/TH]
[TH="align: center"]C[/TH]

[TD="align: center"]1[/TD]

[TD="align: center"]2[/TD]

[TD="align: right"]18/01/2016[/TD]
[TD="align: right"]12.4[/TD]

[TD="align: center"]3[/TD]

[TD="align: right"]01/02/2016[/TD]
[TD="align: right"]11.12[/TD]

[TD="align: center"]4[/TD]

[TD="align: right"]22/03/2016[/TD]
[TD="align: right"]43.2[/TD]

[TD="align: center"]5[/TD]

[TD="align: right"]18/04/2016[/TD]
[TD="align: right"]55[/TD]

[TD="align: center"]6[/TD]

[TD="align: right"]22/04/2016[/TD]
[TD="align: right"]100[/TD]

[TD="align: center"]7[/TD]

[TD="align: right"]13/05/2016[/TD]
[TD="align: right"]125[/TD]

[TD="align: center"]8[/TD]

[TD="align: right"]17/06/2016[/TD]
[TD="align: right"]300[/TD]

</tbody>

Each Item has multiple vendors. So it would be last cost by date per vendor per item.

[TABLE="width: 260"]
<tbody>[TR]
[TD]Item[/TD]
[TD]Vendor[/TD]
[TD]Purchase Date[/TD]
[TD]Cost[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/1/2016[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/1/2017[/TD]
[TD="align: right"]5[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 2[/TD]
[TD="align: right"]2/1/2016[/TD]
[TD="align: right"]2[/TD]
[/TR]
[TR]
[TD]Item 1[/TD]
[TD]Vendor 3[/TD]
[TD="align: right"]2/1/2015[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 4[/TD]
[TD="align: right"]1/1/2018[/TD]
[TD="align: right"]6[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 1[/TD]
[TD="align: right"]1/5/2018[/TD]
[TD="align: right"]1[/TD]
[/TR]
[TR]
[TD]Item 2[/TD]
[TD]Vendor 2[/TD]
[TD="align: right"]1/6/2018[/TD]
[TD="align: right"]7[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Vendor 5[/TD]
[TD="align: right"]12/2/2017[/TD]
[TD="align: right"]3[/TD]
[/TR]
[TR]
[TD]Item 3[/TD]
[TD]Vendor 5[/TD]
[TD="align: right"]10/3/2017[/TD]
[TD="align: right"]1[/TD]
[/TR]
</tbody>[/TABLE]

I would want to show what the last price was for that specific item for each vendor.

 

[Jongo1]

Got it. Used a If(max() for the date requirements and then added the extra criteria to the index array formula.