Dear Community,
I would like to build the workflow that fill up position of employees based on the standard rate given (table below).
Below is the general understanding on the data attached in the excel.
1. Every December and June for each year, there will be promotion to the employees. For instance, In the data attached, Melvin work as Senior Associate in the month of December 2017 and promoted to Assistant Manager in the month of January 2018. Technically, the rate should change from 300 to 400 in January 2018. But the firm offer discount rate for certain employees. (not applicable to every employees). Hence, the charge rate for Melvin is USD350 for January 2018. The discount period could be one month, two month or more than 2 months.
Results wanted
I noted that simple vlookup can't solve the above issues since the rate could be different for certain month. May i know if anyone know how to do the dynamic vlookup based on the criteria below:
1. If the rates that cannot be found based on the table, there could be discount given for that particular month. Hence, in order to fill the position for this particular employee for the particular month. We would need to refer to the next month to see if the rate in the data can match to the table above or not. If cannot match, we would need to refer to the August to see if the rate in the data match to the table above. If match, we will fill up the position of the employee in the month of June and July by referring to the rate in August (that can match to the table above).
As it is quite complex , let me know if i need to explain in the differrent and easier way.
