Nested IF formula to return value based on date but stop after 12 quarters

Hello Excel Gurus,
I am struggling a bit. Pretty sure I have about half of what I need, but what I attempting to do is ask Excel to look at an input date and then look at the quarter start & end date and if the date falls within the quarter start & end dates to populate quarterly revenue. I have the formula working when when it identifies the first relevant quarter. The problem is I then need the formula to move forward in time - so next row would populate the next quarter's revenue and so one for 12 consecutive quarters (3 years), then I need the formula to return $0s again. I am attaching a mock up file for explanatory purposes.

Date: cell C7
Column with current formula: F, starting in F42, you can see formula is correct starting in cell F46 (brings in cell C11 from above) however cell F47 needs to populate what's in C12 on onward for 12 quarters. Starting in cell F58 the value should be $0 again

Please let me know if any question.

Can we talk about the intent of this field.

You want it to lookup the revenue for the given quarter IF the current rows Period End is Less than or Equal to your call out for the Realized Revenue Start Date.

When that is true you are referencing the total revenue... not looking it up...so it is always looking at C$11 <-- Locked to Y1 Q1 Total Rev... Should this not be unlocked as C11? so that the reference moves with it?
*Keep reading because this is not the answer... we need to shift based on values -

Once you are outside of 3 years (12 rows/records) you will no longer have a point of reference as you start referring to totals starting at line 23... now this breaks the reference...

I guess... if you change the date above that will offset the point in which it starts to review... so I think what you need to do is say IF AND

IF the Realized Revenue Date is Less than Quarter End AND the Realized Revenue Date IS GREATER THAN the Quarter End - 3 Years then lookup the first quarter in the series assuming this is the first quarter...

there is nothing in the formula telling it which year we are in compared to the Referenced Date...

So now we understand we need to have a quarter review compared to the realized date.


This means we need to say first is this quarter after the referenced date... you have that already

Now we need to say How many Quarters in are we from the referenced date...

Please Login or Register  to view this content.

Where the
ENDDATE is the PERIOD END
STARTDATE = Realized Revenue "Start Date"

Finally...this should do the trick... it can be much prettier if you had real dates or even quarters since in a column... to reference for the offset

Please Login or Register  to view this content.