• Status: Solved
• Priority: Medium
• Security: Public
• Views: 230

# How to dynamically match and sum values in two seperate tables

The following table is a summary of wages paid to an employee compared to wages they are actually entitled to be paid.

The user makes selections on the left hand side of the spreadsheet based upon when they worked and any penalties, allowances or other payments they were entitled to over each pay period.

These selections are then summarised in the “Pay Period-Sub Totals” section (columns AC-AJ).

The user is then able to manually enter data for “Wages Paid by Employer” (columns AL-AT)

The drop down lists will provide the same options to types of payment but the amounts may be different.

The next section “Variances” (columns AU-BC) needs to be able to match each payment type entered into the previous two sections and then provide the values of any underpayments and overpayments.

For example, the solution would need to match any Wages Paid  “Base Rate” amount (in this case in cell AM13) and subtract the Entitlements “Base Rate” amount (in this case cell AD12).
If there is no match for a value in “Wages paid” (such as “Salary Only” in the attached example in AL12:AM12) then the sum would be 900-0.

Currently I have set up a formula based solution (seen in cells AV2:AV8) which uses vlookup to search for matches and then subtracts the actual entitlements from what was actually paid. For my crude solution I have only applied it to the first pay period as an example.

Looking for a solution that is capable of matching like for like and working out the differences for each pay period (the pay periods are 5 days in this case, but could be weekly, monthly, fortnightly or some other custom number of days <32).

Any ideas for solutions would be great!
Variance-solution-mock-up.xlsx
0
Andross9
1 Solution

Commented:
I think the SumIf function would make your calculations a lot simpler. Enter this formula in cell AV12: