How do I select the correct interest rate from a table of historical interest rates in Excel?

I have two worksheets in my workbook.

The first worksheet keeps track of the value of my mortgage/loan. The first column contains a date, the second contains the interest rate that pertains to that date. The rest of the columns contain things like interest accrued over the period, current value of the loan etc. The date in the first column pertains to an event, so it could be the day that I made a payment on the loan, or it could be a day that I increased the size of the loan.

In the second worksheet I have a list of historical interest rates. The first column contains the date at which a new interest rate came into effect and the second contains the interest rate itself.

Both sheets are ordered by date descending.

My question is, how do I pick the relevant interest rate from worksheet 2 to insert into the corresponding cell in worksheet 1?

-

2 Answers

You can use a lookup formula, but you will need to sort the second worksheet ascending by date. Then you can use a formula like

``````=VLOOKUP(<YourDate>,'Second Worksheet'!\$A\$1:\$B\$22,2,1)
``````

or

``````=INDEX('Second Worksheet'!\$B\$2:\$B\$22,MATCH(<YourDate>,'Second Worksheet'!\$A\$2:\$A\$22,1))
``````

The "1" as the last parameter in the Vlookup or the Match function will return the largest value that is less than or equal to lookup_value. This only works in ascending data sets. Match() also works on data sets sorted in descending order, but the result would not be the one you want.

-

I recommend teylyn's answer - ideally you would have the dates in ascending order (oldest at the top) and that makes it simple to use a lookup as she suggests, but with dates in any order you can use an "array formula" like this:

`=IFERROR(LARGE(IF(MyDate>=Dates,Dates),1),"No date")`

which needs to be confirmed with `CTRL+SHIFT+ENTER`

-