Function to filter excel table data in to a new dataset based on value defined in another table

Question

I have two tables of data, the first is EmployeeSalaryTbl which holds the salaries for employees over time:

and the second StaffDetailsTbl which has details on the employee discipline etc.

I'd like to repurpose a function I use to calculate total employee wages each month so that it only calculates salaries that are reliant on a specific discipline in a given cell, like programming.

To do this, I wondered if it was possible to filter the EmployeeSalaryTbl[Employee], EmployeeSalaryTbl[Salary Start Date], EmployeeSalaryTbl[Salary End Date], etc. results to only contain the rows where the employee has the Programming discipline through a lookup in the StaffDetailsTbl.

Answer 1

If you just want to add up the totals and it doesn't have to look attractive, This might be placed in the adjacent column to your table, beginning with the first row you want to examine. then add the values together at the bottom. If you wish to check for different disciplines, you can edit the place where it says programming to make it a cell reference.

=IF(INDEX(StaffDetailsTbl[Discipline],MATCH(EmployeeSalaryTbl[[#This Row],[Employee]],StaffDetailsTbl[Employee],0))="programming",EmployeeSalaryTbl[[#This Row],[Salary]],"")