Hello everyone,
I've gone through the forums quite a bit to find a solution, but I've been unable to, so forgive me if the answer is already out there.
My issue stems from a payroll question. I need to find a way to match a name on a master table to a payroll detail table. The issue is the name only appears in one cell of the data table, but there are multiple pay categories I need to pull onto the master sheet. Ultimately, I want a formula that will see a name in one cell and add together 5 rows of cells in another column, relative to wherever the name is. I know that is a bit wordy so I've included a sample.
<tbody>
</tbody>
Above is the main table. I'm using a formula an index match formula in the 4th column that looks like this:
{=IFERROR(INDEX('Lookup Table'!$A$1:$D$4,MATCH(1,('Main Table'!B2='Lookup Table'!$A$1:$A$4)*('Main Table'!C2='Lookup Table'!$B$1:$B$4),0),3),0)}
(for the 5th column on the main table, I just changed the index column to 4 to get the needed result)
Below is the Lookup table it is pulling from:
<tbody>
</tbody>
I need a formula that will find the sick and vac time for Jill as well and populate the main table. Putting Jill in the all the open cells will obviously fix the problem with my formula, but is not feasible when using the actual spreadsheet. Does anyone know how to solve this problem? Thank you all in advance!
I've gone through the forums quite a bit to find a solution, but I've been unable to, so forgive me if the answer is already out there.
My issue stems from a payroll question. I need to find a way to match a name on a master table to a payroll detail table. The issue is the name only appears in one cell of the data table, but there are multiple pay categories I need to pull onto the master sheet. Ultimately, I want a formula that will see a name in one cell and add together 5 rows of cells in another column, relative to wherever the name is. I know that is a bit wordy so I've included a sample.
Department | Employee | Type | Hours | Amount |
Admin | Jill | Sal | 70.67 | 3057.69 |
Admin | Jill | Vac | 0 | 0 |
Admin | Jill | Holiday | 0 | 0 |
Admin | Jill | Sick | 0 | 0 |
Admin | Jill | Bonus | 0 | 0 |
Admin | Jill | EETax | 0 | 0 |
Admin | Jill | Regular | 0 | 0 |
Admin | Jill | OT | 0 | 0 |
<tbody>
</tbody>
Above is the main table. I'm using a formula an index match formula in the 4th column that looks like this:
{=IFERROR(INDEX('Lookup Table'!$A$1:$D$4,MATCH(1,('Main Table'!B2='Lookup Table'!$A$1:$A$4)*('Main Table'!C2='Lookup Table'!$B$1:$B$4),0),3),0)}
(for the 5th column on the main table, I just changed the index column to 4 to get the needed result)
Below is the Lookup table it is pulling from:
Jill | Sal | 70.67 | 3057.69 |
EETax | 0 | 0 | |
Sick | 2 | 86.54 | |
Vac | 14 | 605.77 | |
Bonus | 0 | 0 |
<tbody>
</tbody>
I need a formula that will find the sick and vac time for Jill as well and populate the main table. Putting Jill in the all the open cells will obviously fix the problem with my formula, but is not feasible when using the actual spreadsheet. Does anyone know how to solve this problem? Thank you all in advance!