CODEWITHSUNDEEP
excelvlookup
ScottCee
ScottCee

Reputation: 179

IF value in list, perform VLOOKUP and add result

I have a list of employees, a column stating their 'available days multiplier' (eg a full time employee would be 1.0, a part time employee working 4 days out of 5 would be 0.91, 3 days would be 0.80 and so on. The next column calculates the employees available holidays, based on a standard 30 days multiplied by the 'available days multiplier'. See below, with an example formula in brackets :

    A                     B                    C
1 Name       |  Available Days Multiplier | Holidays
2 Employee A |            1               | 30 (=30*B2)
3 Employee B |            0.91            | 27
4 Employee C |            0.91            | 27
5 Employee D |            1               | 30

Employees have an option to purchase additional holidays, which I have stored in a separate table containing 'Name' and 'Days'

    S                T      
1 Name       |  Purchased Days 
2 Employee 2 |       5
3 Employee 4 |       3

I would like to amend the formula in column C to not only calculate the holidays based on available days multiplier, but also to look up the list in columns S:T and add on any purchased days, if the employee name is found.

I suspect this could be quite easy with an IF/VLOOKUP combination, but I cannot find the correct syntax. Any help greatly appreciated.

Upvotes: 1

Views: 48

Answers (1)

Gravitate
Gravitate

Reputation: 3064

If I have understood correctly, you could use SUMIFS:

enter image description here

=(30*$B2)+SUMIFS($T$2:$T$3,$S$2:$S$3,$A2)

This works by first calculating the number of holidays as you have been doing and then adding any purchased ones.

If the employees name appears more than once in the list, it will add the total sum of purchased holidays to the total.

SUMIFS sums the first range (T2:T3) if the second range (S2:S3) matches the criteria (A2).

Hope this helps. If you need further clarification, please let me know.

Upvotes: 1

Related Questions