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!

 

[NiMip]

You've mentioned 5 rows... Are you always going to have the same number of items in the Lookup Table? If so, this could work I think. I've highlighted where I've made the 5-item assumptions. If this varies for each person, then that'll need to be dynamic.

=IFERROR(INDEX(OFFSET('Lookup Table'!C$1,MATCH('Main Table'!$B2,'Lookup Table'!$A:$A,0)-1,,5,1),MATCH('Main Table'!$C2,'Lookup Table'!$B$1:$B$5,0)),0)

You've also mentioned adding together the 5 rows, rather than just pulling out the information... is that something you want to do in one formula, or do you want the Main Table breakdown you have above?

 

[Elijah_M]

There will not always be 5 rows, unfortunately. It will vary between 4-10. I'm unfamiliar with dynamic formulas, but If you could point me in the right direction, I'm more than happy to research them.

My ultimate goal would be to have the formula add together everything but the EETax, for this example, and spit out a summed number for each employee on the main table page.

Finally, I greatly appreciate your help! I've been working on this problem all day, but I can't seem to find the solution.

 

[NiMip]

Hmmmmm challenging haha. This formula is going to get very messy I think. You've said populating the empty cells with names isn't feasible... what about adding another column? It would be MUCH simpler if you could create another column to fill in those blanks with =IF(ISBLANK(A2),B1,A2), and then use that column to SUM/LOOKUP/etc.

EDIT: Oh and to answer your question about the dynamic formula, I just mean that it'll have to be able to change with the value you're looking up, rather than being a written-in, static value. Hope that makes sense?

 

[Elijah_M]

Yeah, this is quite the problem. My college excel classes definitely did not prepare me for this haha. That would work, except that the empty cells do contain data, which I completely left out of the example above. I did not think they would be relevant. If it would help I can post the actual data and table into a google doc and post the link here.

I gotcha on the dynamic formula. I did a bit of google searching/learning haha.

 

[NiMip]

Yeah go for it, seeing the data will give me a better indication of what I think we can do! I'm by no means an expert at this stuff either haha, just a bit of a hobby of mine. Hopefully someone will come through with the goods if I can't come up with something.

 

[Elijah_M]

Here is the link

******** src="https://docs.google.com/spreadsheets/d/e/2PACX-1vQs7WbuLDgUQqFY-Sm0FX6mXG2gVXHYcw2fYkVsIM8xKpeppAAJ8qpJmlLd5vWAFBDhJFsZoM9SaVZx/pubhtml?widget=true&headers=false">*********>

 

[Elijah_M]

Oops! That was supposed to be embedded. Use this link instead.

https://docs.google.com/spreadsheet...KpeppAAJ8qpJmlLd5vWAFBDhJFsZoM9SaVZx/pubhtml#

 

[NiMip]

Thanks! Ok so I think I've come up with something, but it's a little specific to how the data is set up, and I've assumed everything is as you've placed it in your link, so hopefully you don't have to make many changes!

So, rolling with the additional column idea in Lookup Table, to fill the names against the data, cell E4:
=IF(AND(A4="",B4<>""),E3,IF(AND(A3="",A4<>""),A4,E3)


Then, try this for the Hours (cell D2) in Main Table, pressing CONTROL+SHIFT+ENTER as it's an array formula. Should see {} appear around the formula in the formula bar if done correctly.
=IFERROR(VLOOKUP($B2&$C2,CHOOSE({1,2},'Lookup Table'!$E$4:$E$150&'Lookup Table'!$B$4:$B$150,'Lookup Table'!C$4:C$150),2,FALSE),"")

You should be able to just drag the formula across to the Amount column. Let me know if something doesn't work!

 

[Elijah_M]

Wow, I'm super appreciative of all the help you have provided!
I won't have time today, but I will get try your suggestions first thing in the morning and I will be sure to let you know how it goes!