Finding "last price" paid for multiple receipts in a large set of data

I am trying to identify a formula that allows me to quickly identifiy the "last price" paid for a part number over several different receipt transactions. I've tried unsuccessfully with other posts that I have found on the web. An example of the data is below, also notice it is possible to have data with different prices for the same part (refer to p/n 00093953) on the same transaction date to complicate matters (in this instance I would prefer to return higher of the 2 values as the tiebreaker or alternatively but less desirable the value associated with the higher/newer PO # as a tiebreaker if that is an option). Alternatively, maybe there is a Pivot Table logic I can apply even though I am in the wrong forum. Sample data attached as well as shown below:

Period Month Legacy P/N PO# PO Line UOM Transaction Date Cost/ UOM ($USD)
1 Oct 00186226 1944 9 EA 10/18/2013 500
1 Oct 00186226 1947 1 EA 10/23/2013 200
1 Oct 00186226 1959 3 EA 10/25/2013 1,000
4 JAN 00186226 1959 6 EA 1/10/2014 750
1 Oct 00093953 1378 5 EA 10/7/2013 2
1 Oct 00093953 1381 8 EA 10/7/2013 3
1 Oct 00102294 3066 5 NM 10/1/2013 6,000
1 Oct 00102294 3066 5 NM 10/15/2013 5,500
1 Oct 00102294 3054 5 NM 10/23/2013 5,000

Hi there,

The attached is an array formula to find the last price paid for each part number.

With the second part, you wish to find the highest price paid instead?

Have a look, I will come back to the second part once you have confirmed.

Excel Help_Last price paid example data v2.xlsx

Solution:
Redcoal - Looks like a great solution, it returned all the correct answers for each of the 3 part numbers in the data set. I'll go test it out further in the large data set I have. Thank you so much for the prompt response....considering I spent a good 2 hours trying to work this out myself w/o any success.

Marvin P, unfortunately your solution did not return the correct results but I appreciate your response.

Redcoal, I was unable to apply your solution/logic to my actual data set which contains additional columns as it is returning a #Value error. The part # remains in Column C, the price paid moves to Column U, transaction dates is Column R. Any chance you could assist again? File w/ additional columns now attached.

Open to other suggestions as well.

Regards,

What you have is correct, however because is an array formula you need to apply Control+Shift+Enter (CSE) to make it valid. Simply hit F2 on the cell and press (CSE), you will notice an extra set of parentheses added to the formula.

Before: =INDEX($C$2:$H$10,SMALL(IF($C$2:$H$10=C2,ROW($C$2:$H$10)-ROW($C$2)+1,ROW($H$10)+1),COUNTIF($C:$C,C2)),6)

After CSE: {=INDEX($C$2:$H$10,SMALL(IF($C$2:$H$10=C2,ROW($C$2:$H$10)-ROW($C$2)+1,ROW($H$10)+1),COUNTIF($C:$C,C2)),6)}

See revised working file - Excel Help_Last price paid example data_added columns v2.xlsx

Redcoal & Marvin P, thanks to both of your for sticking with me to get resolution and for kindly sharing your knowledge & skills.

*Redcoal, I still had trouble getting the array formula to work.

*Marvin P, it was much easier for me to follow and apply the sort option with the simple formula you provided. By modifying the sort first by part #, then descending transaction date, then descending by price paid I was able to set the foundation to return the results as desired. This same logic allowed me to then return the data associated with the last price paid, this data will be very helpful for my team to carry out an upcoming project.

Thanks again!
Rim2Rim

Thanks again MarvinP and I appreciate you bringing the "reputation" function to my attention as well as I did not notice it previously. This was my first posting requesting assistance on the web ever, the feedback was excellent. I also just tagged my request as Solved. I also spent quite a bit of time researching the array option last night, you and Redcoal clearly both have far greather Excel skills than I. Once again, much appreciate all the thought and effort by both of you!

Thank you Redcoal, I'll definitely review the URL link as I'd like to learn more about this functionality as I was not familiar with it at all prior to your suggestion. Clearly it is very powerful. I'd tag your reputation with a "star" as well but I seem to have lost the option to do so.

Much appreciated!
Rim2Rim