Search returned value in another table excel

Question

So I have this issue, I have two tables one is employees, and another one is the projects.

Table Employees:

Year Name Type       Amount    
2018 Kevin Salary    5000    
2018 Kevin Insurance 200    
2018 Alex  Salary    3000    
2018 Alex  Insurance 300

Projects Table

Year Project_Name  Employee_Name  Hours_Worked    
2018  Apple        Alex                7    
2018  Apple        Kevin               5    
2018  LG           Kevin               3 

Now I am creating a result list of all the projects and costs recurred for them, what I need is for each project in the Table 2 find which employees are involved and then use that information to find related costs for the employee and calculate total costs for that project.

(e.g for project LG, I have Kevin working on that,for him company paid 5200(salary+insurance) and the costs recurred for the LG project would be 5200 divided by hours spent on the project, for the project Apple it would be the same but sum of Kevin's and Alex's costs recurred)

I have tried several things but didn't work, is there a way to lookup project name in one table and use the returned value(employees) to get the related costs from another Table ?

Answer 1

Your formula would use SUMPRODUCT and SUMIFS:

=SUMPRODUCT(SUMIF(B:B,$H$2:$H$4,D:D)/$I$2:$I$4,--($G$2:$G$4=K2))

The references to the second table must be limited to the data set.