index and match

 

In one excel worksheet called FY18 Report i need to populate the Salary Step based on the total salary.  – salary and fringe benefits. In a separate tab called Base and Fridge, i have a salary table with grade, step and salary range. Based on the total salary of the employee the intent is to pick the closes step to that salary. That wprksheet looks as following:

 

 

Grade Step/Incrmt   Base   Fringe   Total 
1 1       2,703,432          1,382,476        4,085,908
1 2       2,775,787          1,413,588        4,189,375
1 3       2,848,142          1,444,701        4,292,843
1 4       2,920,497          1,475,814        4,396,311
1 5       2,992,852          1,506,926        4,499,778

etc.

 

 

Position # Employees Name Salary Grade Salary Step  Base   Fringe   Salary + Fringe Total in LC 
XX1 ADAM 7 #NAME?            5,545,164                        2,604,421            8,149,585
XX2 ADAM2 9              8,757,107                        3,985,556          12,742,663

By: Peter

Leave a Reply

Your email address will not be published. Required fields are marked *