Pulling last cost of items

[Jongo1]

I have a sheet set up like such:

Item No.	Purch Date	Cost
Item 1	1/18/2016	12.4
Item 1	2/1/2016	11.12
Item 2	3/22/2016	43.2
Item 2	4/18/2016	55
Item 3	4/22/2016	100
Item 3	5/13/2016	125
Item 3	6/17/2016	300

<tbody>
</tbody>

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
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

<tbody>
</tbody>

Sheet1​

	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

<tbody>
</tbody>

Sheet1​

Worksheet Formulas Cell 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)) <tbody> </tbody>

<tbody>
</tbody>

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

<tbody>
</tbody>

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

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

<tbody>
</tbody>

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

Item	Vendor	Purchase Date	Cost
Item 1	Vendor 1	1/1/2016	5
Item 1	Vendor 1	1/1/2017	5
Item 1	Vendor 2	2/1/2016	2
Item 1	Vendor 3	2/1/2015	3
Item 2	Vendor 4	1/1/2018	6
Item 2	Vendor 1	1/5/2018	1
Item 2	Vendor 2	1/6/2018	7
Item 3	Vendor 5	12/2/2017	3
Item 3	Vendor 5	10/3/2017	1

<tbody>
</tbody>

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.