Vlookup with certain criteria (Employee Data)

[SH_94]

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.

[JosephSerpis · Accepted solution]

Hi @SH_94 I mocked up an approach which tries to simplify the task you trying to achieve it matches the output you provided. Let me know what you think?

[atcodedog05]

Wow, this is indeed much simpler 😅

[SH_94]

Dear @JosephSerpis ,

Thanks for the smart application..really amazing

[atcodedog05]

Hi @SH_94 

I know this has already been solved. A different logic hit my mind thought I will share it.

Lets say a rate after discount is 485 the possibility is the rate is 500 which is next 100. In this scenario there is no discount greater than 100 hence the actual rate of discount will always be next 100.

Workflow:

1. Adding record id

2. Using formula tool calculating the next 100 of the rate.

3. Using join tool to join on rate.

4. Sorting back in order.

Output matches

Hope this helps 🙂

[SH_94]

Dear @atcodedog05 ,

After checking original data, there are some changes in term of rate. Not every gap is less than 100. There could be 1000 gap between CEO and Manager.

Will this method still apply?

Thank you.

[SH_94]

Dear @JosephSerpis ,

I went through the data and noted that some of the data can't be applied using this method due to following reason.

1. New Employee ,Alexander entered the firm and the rate is 320 . According to the this workflow, we couldn't identify which position it should be.

In this case, Alexander position is assistant manager. The idea for this rate is the actual rate (maybe due to discount) could be lesser than original rate as per table. In this case, the rate is between Senior associate and Assistant manager. The actual rate can either same or less than original rate.(cannot be greater than original position rate). Hence, Alexander is fall under assistant manager.

Thanks you.

[JosephSerpis]

Hi @SH_94 I came up with an approach which can handle the scenario you outlined and it matches your expected output.

[atcodedog05]

---

@SH_94 wrote:

Dear @atcodedog05 ,

After checking original data, there are some changes in term of rate. Not every gap is less than 100. There could be 1000 gap between CEO and Manager.

Will this method still apply?

Thank you.

---

Hi @SH_94 

Sorry for the late reply I was away this weekend.

It doesn't matter if the gap between CEO and Manager is equal to or greater than 100. What matters is, is the discount given greater than 100. For example, if the CEO needs to pay a price of 1000 and the discounted price is 920 it will work, but if the discounted price is 890 it won't work. We may need to modify the next ceil logic to get it working.