Latest date for each of the year

[Sujatha]

I have four columns - Product, Date, Year(based on date), Price. For each of
the product there will be price revisions occuring. I need to pick up the
latest price for each of the product in each of the year.

Say for example - Product A, the price details of that are 31st Jan'04 -
Rs.500, 5th Aug'04 - Rs.550, 20th Aug'05 - Rs.450, 15th Nov'05 - Rs.440, 30th
July'06 - 520

I need the answer to be Product A - Year 2004 Rs.550, Year 2005 Rs.440, Year
2006 Rs.520 (i.e I need the price for each of the year based on the lates
date of each of the year)

 

[Joel]

Try something like this

=VLOOKUP(MAX(--(A$2:A$6="Product A"),(B$2:B$6)),B$2$6,3)

A2:A6 is the range of data where you products are located
B$2:B$6 is the range of dates
B$2$6 is a table with the dates in the first column and the price in the
3rd column
the last 3 is to indicates the price is in the 3rd column ove rthe table
B$2$6

the MAX function will get the latest date that matches Product A. vlookup
looks up the latest date in the tqable and then returns the price associated
wiith the latest date from the 3rd column in the table..

 

[Stefi]

=INDEX(D25,MATCH(MAX(--(C2:C5=2004)*B2:B5),B2:B5,0))
Its an array formula, confirm it with Ctrl+Shift+Enter!
Adjust ranges and year!

Regards,
Stefi


„Sujatha†ezt írta:

 

[Stefi]

Sorry, I forgot to filter Product A:
=INDEX(D25,MATCH(MAX(--(A2:A5="Product A")*--(C2:C5=2004)*B2:B5),B2:B5,0))
Its an array formula, confirm it with Ctrl+Shift+Enter!
Adjust product, ranges and year!

Stefi

„Stefi†ezt írta: