Pretty sure you can get by with a simple IF formula:

Assuming by "report date" you are entering a date into a cell (or that cell
returns the current date), your rent formula cell would be:

=IF
(AND(K2<=DATECELL,DATECELL<=L2),M2,IF(AND(R2<=DATE CELL,DATECELL<=S2),T2,AA2)

"Lexi" wrote:

I have 2 sheets in a workbook, one called data and one called rent roll. Data
has rental increases based on date ranges. I have set up separate columns for
date start, date end and rent and I have 3 sets for rental increases on the
data sheet.

Data sheet ranges: k2=date start1, L2=date start1, M2=rent1, R2=date start2,
S2=date end2, T2=rent2, Y2=rent start3, Z2=rent end3, AA2=rent3

I want to have the rent roll sheet pick up only the rent in effect based on
current report date.