Lookup between Time Periods and Text

rhightower · July 2020

I'm reaching out for guidance on how to build a link between Full-Year time periods and a list-equivalent Fake Years. i.e.: FY19 = "Year One", FY20 = "Year Two", etc.

Some background, I have a P&L line item "Revenue" that has a year ending balance of $100,000 in 2019 and $150,000 in 2020. These revenue amounts begin to amortize following the year it is listed. I need to amortize the FY19 $100,000 on my defined % pattern based on annual rates: (Year One "2019" = 20% during 2020, 30% during 2021, 35% during 2022, et al.). FY20's $150,000 will follow the same % pattern starting in 2021, but I need to keep it separate in "Year Two". My end result is a module which, for Fiscal Year 2021, will sum 2019's 2nd year Pattern (30%) and 2020's 1st year Pattern (20%).

I have a module which converts the FY Time Scales to a text version of my Fake Years "Year One, Year Two, Year Three..." through the "FINDITEM" formula. Then a second module shows my full time scale (by months) against my List of Fake Years which will apply the % Pattern each month. I'm struggling with using a Lookup formula to populate "Year One" column with FY20's Revenue amount ($100K).

How can I bring the 2019 $100,000 into the "Year One" column in this second module? Lookups between text and time periods has been my most challenging area to understand coming from a strong Excel background.