This hopefully is more simple than how I am doing it. Thanks in advance for any suggestions.

I have an excel sheet of a rent schedule for a commercial property. As example, think a different rent amount for each Jan-Dec period. The starting rent date is in column A, ie 1/1/18, then 1/1/19, then 1/1/20, etc. let's say for 120 months.

In Column B naturally is the monthly rent amount. The idea here is that rent increases each year on Jan 1.

I have another cell which I want to be "Current Rent." I want the formula to look at today's date, and find the corresponding rent in my table.

The idea being when I look at this sheet on any day, it'll automatically give me the rent on that day.

Note I have 140 locations, ie 140 rent schedules on 140 excel tabs for which do to this. And the rent schedule is not always Jan-Dec for each rent schedule. So no shortcuts here.

My solution so far: =if(and(today()>a1,Today()<a2,b1,if(and(today()>a2,today()<a3,b2,=if(and(today. . etc.

The idea is to say if today's date is between any adjacent two dates in my table, give me the rent for that date. This seems overly cumbersome, and I feel there must be a simpler way to do this.

VLookup I think would be a solution. Put the formula in the cell you want to show the "Current Rent amount". Use the cell with the current date to drive the lookup and return the cell adjacent to the line item that matches (or falls on either side of) the value of today's date.

zipriderson,

If you didn't get this figured out, I believe I have a solution for you. It uses three different function calls, MATCH, ADDRESS, and INDIRECT.

Note: This will only work if the start dates are in ascending canonical order (1, 2, 3,...).

The Match call does the search work, it finds the offset position in the list of start dates, which is less than or equal to the subject date (today).

The Address function is used to form the address of the cell with the current rent value, using the offset found by Match. You just add 8 (in this case) to get the row position in the sheet.

The Indirect function uses the address formed by the Address function to retrieve the value (Rent) in that cell.

This should always work, without having to do the upper date comparison, because Match will find the first date in the list that is less than or equal to today's date.

Formulas are at the bottom of the screenshot, labeled in blue.

Hope this helps.

ETA: This may seem a bit complicated. So if you have any questions, please ask.

This message has been edited. Last edited by: Storm,