Unsure if I should use Index Match or something else

[Elijah_M]

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.

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!

 

[Elijah_M]

So just as an update, I think I have it up and working now! I stayed with the original formula for the main table {=IFERROR(INDEX('Lookup Table'!$A$5:$E$142,MATCH(1,('Main Table'!$B2='Lookup Table'!$E$5:$E$142)*('Main Table'!$C2='Lookup Table'!$B$5:$B$142),0),4),0)} , but created the additional column using your formula and am having awesome results! The only issue now is for when people get paid two checks in the same period, but that doesn't happen all that often. Thank you again for the help, I definitely could not have done it without you!