CODEWITHSUNDEEP
excelexcel-formula
oryan5000
oryan5000

Reputation: 91

Formula to find the most recent date and based on an additional matching criteria using AGGREGATE

Update: I tried to do this using Index/Match but the most effective method is using AGGREGATE

In a table of interest rates, I'm looking to pull in the rate associated with the correct tenor based on the most recent date. I'm looking to do this with index match but would be open to other formulas if they work efficiently. This data will be updating dynamically, this is why I need a formula that always finds the most recent date

Desired Output = 80

Current Output = 71

=Index (A1:E100, And(Max(A2:A100), Match(F1,B1:B100,0)), 4)


Cell F1 = 3Y



Data Table (rows 1 through 00)


A              B     C     D      E

DATE         TENOR INDEX SPREAD END_DATE
9/30/2015 0:00  3M  3ML 21.25   3/31/2016 0:00
9/30/2015 0:00  6M  3ML 21.25   3/31/2016 0:00
9/30/2015 0:00  1Y  3ML 21.25   3/31/2016 0:00
9/30/2015 0:00  3Y  3ML 71.00   3/31/2016 0:00
9/30/2015 0:00  5Y  3ML 98.50   3/31/2016 0:00
9/30/2015 0:00  10Y 3ML 137.5   3/31/2016 0:00
4/1/2016 0:00   3M  3ML 21.25   4/30/2016 0:00
4/1/2016 0:00   6M  3ML 21.25   4/30/2016 0:00
4/1/2016 0:00   1Y  3ML 21.25   4/30/2016 0:00
4/1/2016 0:00   3Y  3ML 73.50   4/30/2016 0:00
4/1/2016 0:00   5Y  3ML 101.00  4/30/2016 0:00
4/1/2016 0:00   10Y 3ML 141.00  4/30/2016 0:00
5/1/2016 0:00   3M  3ML 21.25   5/31/2016 0:00
5/1/2016 0:00   6M  3ML 21.25   5/31/2016 0:00
5/1/2016 0:00   1Y  3ML 21.25   5/31/2016 0:00
5/1/2016 0:00   3Y  3ML 72.50   5/31/2016 0:00
5/1/2016 0:00   5Y  3ML 100.00  5/31/2016 0:00
5/1/2016 0:00   10Y 3ML 140.00  5/31/2016 0:00
6/1/2016 0:00   3M  3ML 21.25   6/30/2016 0:00
6/1/2016 0:00   6M  3ML 21.25   6/30/2016 0:00
6/1/2016 0:00   1Y  3ML 21.25   6/30/2016 0:00
6/1/2016 0:00   3Y  3ML 80.00   6/30/2016 0:00
6/1/2016 0:00   5Y  3ML 107.50  6/30/2016 0:00
6/1/2016 0:00   10Y 3ML 152.50  6/30/2016 0:00

Upvotes: 0

Views: 220

Answers (2)

Pomul
Pomul

Reputation: 390

You can do it using this:

{=INDEX(D2:D24,MATCH(1,(F1=B2:B24)*(MAX(A2:A24)=A2:A24),0))}

Make sure to use ctrl+shift+enter

Upvotes: 0

Ron Rosenfeld
Ron Rosenfeld

Reputation: 60174

Especially since the data will be updating, and possibly expanding (or contracting), I chose to use a Table with structured references.

I also moved the Tenor selection range to G1 to move it away from the table.

=AGGREGATE(14,6,(Table1[DATE]=MAX(Table1[DATE]))*(Table1[Tenor]=G1)*Table1[SPREAD],1)

You could also use:

=MAX((Table1[DATE]=MAX(Table1[DATE]))*(Table1[Tenor]=G1)*Table1[SPREAD])

but you would have to confirm this array formula with Ctrl + Shift + Enter

enter image description here

Upvotes: 3

Related Questions