Return Min Value with Index/Match if Min date is two or one month from today's date

[fbinaghi]

Hi Guys:

I have a rent roll for several properties which will have their rents increase over time. I would like to have my worksheet automatically update as those rent increase dates get closer to make sure I am billing them correctly. I am looking for an equation which will populate the next rent increase date column in sheet one with the next rent increase month coming up if that month is two or one months in the future from today's month and year for a given property. I want it to match the tenant in sheet one and look through the tenants in sheet two and match the minimum of the list of dates and compare it with today's month and year. If there isn't anything coming up within 1 or 2 months I want it to return blank. Thoughts?

SHEET 1

Unit	Tenant	Next Rent Increase Date	Increase Amount
100A	TRADER JOES
100B	STARBUCKS
100C	PIZZAHUT

SHEET 2

Today = 12/1/2019

Unit	Tenant	Next Rent Increase Date	Increase Amount
100A	TRADER JOES	01/01/2020	10,000
100A	TRADER JOES	06/01/2020	10,100
100A	TRADER JOES	01/01/2021	10,200
100A	TRADER JOES	06/01/2021	10,300
100A	TRADER JOES	01/01/2022	10,400
100B	STARBUCKS	01/01/2020	5,000
100B	STARBUCKS	06/01/2020	5,100
100B	STARBUCKS	01/01/2021	5,200
100B	STARBUCKS	06/01/2021	5,300
100B	STARBUCKS	01/01/2022	5,400

[6StringJazzer]

It is always better to attach your actual Excel file instead of putting tables of data in your post. Fortunately I could create a few in under a minute, but most people will not take the time to reproduce a file from scratch.

I am a little perplexed to see rent increases 5 days apart, but here is what a solution looks like. This will show increases within 60 days of the current date.

=IFERROR(INDEX(Sheet2!C$2:C$11,MATCH($A3&"-1-",Sheet2!$A$2:$A$11&IF(Sheet2!C$2:C$11<TODAY()+60,"-1-",0),0),1),"")

This is an array formula. After typing in the formula, do not hit ENTER--hit CTRL+SHIFT+ENTER. You have done it correctly if the formula in the formula box has {braces} around it. You cannot type in the braces; they are just an indicator that it is an array formula.

I have implemented it in your file, showing dates so that some will match and some will not. If there are two dates within 60 days, it will only show the first one. This assumes that all dates on Sheet2 are in the future.

[fbinaghi]

You are correct. The dates were off. I have updated my original table. I will take a look at the excel sheet you've made now. Thank you!

[fbinaghi]

Excellent! The equation works great. I have never seen the "-1-" used before. What does that mean? Could you explain the logic behind this equation? Best regards.