Latest Interest rate

[Swaroon]

Hi, I have a list of agreemeents and the history of the interest rate associated with each agreement.
So eg. Agreement XYZ000001 appears 3 times as there have been 3 changes to the interest rate.
Against a full list of agreement numbers (aooear only once in list), I need to pull the most recent effective date and interest rate
Out of the table below, I'm showing the required result underneath - make sense ?

Agreement# Intreffectivedate Interestrate
XYZ000001 14/05/2015 0.1856
XYZ000001 30/03/2015 0.18425
XYZ000001 14/02/2015 0.1785
XYZ000002 18/05/2015 0.18656
XYZ000002 30/04/2015 0.18425
XYZ000002 14/04/2015 0.1785
XYZ000003 01/05/2015 0.1866
XYZ000003 15/04/2015 0.18425
XYZ000003 14/03/2015 0.1785



Answer

Agreement# Intreffectivedate Interestrate
XYZ000001 14/05/2015 0.1856
XYZ000002 18/05/2015 0.18656
XYZ000003 01/05/2015 0.1866

 

[VBA Geek]

Your table is sorted per Agreember # and then per Most Recent Date, this means you could run a straight VLOOKUP to return the first matches

 

[gaz_chops]

Are the dates always sorted latest to oldest? (Within each agreement #?).

If so then try this for the date.

=INDEX($B$2:$C$10,MATCH($A21,$A$2:$A$10,0),1)

Change the 1 at the end to 2 for Interest Rate.

 

[Swaroon]

Thanks for the help, sorted

 

[gaz_chops]

You're welcome, thanks for the feedback.