1 Reply Latest reply on Nov 14, 2018 9:00 PM by Shinichiro Murakami

    Salary Study- Looking for a value until one is found

    Christian Adams

      Greetings everyone,


      I've found great help on this forum in the past, and I hope someone might be able to offer some direction on my current problem.


      In short, I am performing a salary study.  The basis of the calculation is to pull the average of a group of salaries of a specific population within a fiscal year, and then multiply that average by a series of escalations (e.g. years of service, expected promotions).  The escalations I have covered and I use LOD's and some other formulas to get the raise base for most of my positions.  Where I have issues if there is no one in the population to create my basis within a specific fiscal year. Because no one is in the population, I have no base salary for my calculation. 


      Ideally, what I want to do is "If the fiscal year has no base, go look in the previous fiscal year and use that base. If the previous fiscal year has no base, go one more year back." Continue with that logic until a base salary is found.  LOOKUP is not working because it just does one lookup.  Any ideas?


      I've attached a dummy workbook, which shows my problem on a small scale.  In this subset of the data, there is no one the population to create the base in FY 2017 or FY 2018, so FY 2018 is blank of data. 


      Any advice would be appreciated.