Retrieve latest Value for specific criteria

Zanne_Mari

Hi all,

I have created a estimation sheet and would like to retrieve the last quoted value based on the part number and the vendor. I have populated a separate sheet called "Vendor Pricing" where I have some historical data and I am planning to copy the new entries there to build the database.

I used the following formula:

=INDEX({UnitPrice}, MATCH([Part Number]@row + [Vendor Name]@row, {ID}, 0))

I suspect I need a way to automatically sort the sheet?

JBG

Hi @Zanne_Mari

I believe this formula should do the trick.

=INDEX(COLLECT({UnitPrice}; {Vendor}; [Vendor Name]@row; {Part}; [Part Number]@row; {Date}; MAX(COLLECT({Date}; {Vendor}; [Vendor Name]@row; {Part}; [Part Number]@row))); 1)

===================================================

"Nothing is impossible. The word itself says 'I'm possible!'"