Formula to match data

[RattlingCarp3048]

I am trying to match data from one sheet to the next. I tried a vlookup but keep getting the #spill! error. Maybe you guys can help? Basically what i am trying to do is.... we have multiple employees with an assigned department but they cross train in other departments. I need to line their hours up in the appropriate fields. On Sheet #1 I need columns F, G, and H to search for the name in A + Packing, Produce, or register and return the value. This is a small example, there are many many more names so limiting the search to the name would not be dynamic enough. I would prefer a formula if possible rather that VBA.

In this case F2 should search for Matt + Packing and return 4.
F3 should search for John + Packing and return 17 etc...

Then G3 should search for John + Produce and return 1.

Then H4 should search for Annie + Register and return 15.

 

[Fluff]

If you are getting a #Spill error that suggests you are using either 365 or 2021, rather than 2010 which your profile shows. I would suggest that you update your profile.

Maybe

=FILTER(sheet4!C6:C20,(sheet4!A6:A20=A2)*(sheet4!B6:B20="Packing"))

 

[jtakw]

Hi,

If you adjust your Headers in Sheet1, you can use a Single formula using SUMIFS.
If you don't change the Headers, you'll need to use 3 separate formulas, hardcoding "Packing", "Produce", etc.
Add Sheet4 name to formula:

Book3.xlsx
	A	B	C	D	E	F	G	H
1						Packing	Produce	Register
2					Matt	4	0	0
3					John	17	1	0
4					Annie	15	0	15
5					Sue	0	0	12
6	Matt	Packing	4
7	John	Produce	1
8	John	Packing	17
9	Sue	Register	12
10	Annie	Packing	15
11	Annie	Register	15
Sheet979

Cell Formulas
Range	Formula
F2:H5	F2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,F$1)

 

[RattlingCarp3048]

If you are getting a #Spill error that suggests you are using either 365 or 2021, rather than 2010 which your profile shows. I would suggest that you update your profile.

Maybe

=FILTER(sheet4!C6:C20,(sheet4!A6:A20=A2)*(sheet4!B6:B20="Packing"))

updated to 365.

#Calc! error

 

[Fluff]

That means there are no cells on sheet4 that match both criteria.
Have you tried jtakw's suggestion? If so what does that return?

 

[RattlingCarp3048]

Hi,

If you adjust your Headers in Sheet1, you can use a Single formula using SUMIFS.
If you don't change the Headers, you'll need to use 3 separate formulas, hardcoding "Packing", "Produce", etc.
Add Sheet4 name to formula:

Book3.xlsx
	A	B	C	D	E	F	G	H
1						Packing	Produce	Register
2					Matt	4	0	0
3					John	17	1	0
4					Annie	15	0	15
5					Sue	0	0	12
6	Matt	Packing	4
7	John	Produce	1
8	John	Packing	17
9	Sue	Register	12
10	Annie	Packing	15
11	Annie	Register	15
Sheet979

Cell Formulas
Range	Formula
F2:H5	F2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,F$1)

The headers cannot be changed. I added the sheet names but it returned a 0.

 

[RattlingCarp3048]

That means there are no cells on sheet4 that match both criteria.
Have you tried jtakw's suggestion? If so what does that return?

yes. It returned a 0

 

[Fluff]

In that case as I said before. There is nothing on sheet4 that matches both criteria. Check for typos & leading/trailing spaces.

 

[jtakw]

The headers cannot be changed. I added the sheet names but it returned a 0.

If the headers can't be changed, the formulas will be:

Book3.xlsx
	A	B	C	D	E	F	G	H
1						Total Packing	Total Produce	Total Register
2					Matt	4	0	0
3					John	17	1	0
4					Annie	15	0	15
5					Sue	0	0	12
6	Matt	Packing	4
7	John	Produce	1
8	John	Packing	17
9	Sue	Register	12
10	Annie	Packing	15
11	Annie	Register	15
Sheet979

Cell Formulas
Range	Formula
F2:F5	F2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Packing")
G2:G5	G2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Produce")
H2:H5	H2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Register")

But if you're Still getting 0, as Fluff said, your data is Not matching
In an empty cell anywhere, =Sheet4!Bx="Packing", where Bx is a cell you see as "Packing", what's the result?
Also, try the same with A column Names.
and finally, =ISNUMBER(Sheet4!Cx), what's the result?
Also, you might want to do the same check for Sheet1! Name column.

 

[RattlingCarp3048]

If the headers can't be changed, the formulas will be:

Book3.xlsx
	A	B	C	D	E	F	G	H
1						Total Packing	Total Produce	Total Register
2					Matt	4	0	0
3					John	17	1	0
4					Annie	15	0	15
5					Sue	0	0	12
6	Matt	Packing	4
7	John	Produce	1
8	John	Packing	17
9	Sue	Register	12
10	Annie	Packing	15
11	Annie	Register	15
Sheet979

Cell Formulas
Range	Formula
F2:F5	F2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Packing")
G2:G5	G2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Produce")
H2:H5	H2	=SUMIFS($C$6:$C$11,$A$6:$A$11,$E2,$B$6:$B$11,"Register")

But if you're Still getting 0, as Fluff said, your data is Not matching
In an empty cell anywhere, =Sheet4!Bx="Packing", where Bx is a cell you see as "Packing", what's the result?
Also, try the same with A column Names.
and finally, =ISNUMBER(Sheet4!Cx), what's the result?
Also, you might want to do the same check for Sheet1! Name column.

I think i got it. I changed JTAKW's formula to be a little more dynamic C:C and A:A etc instead which worked for this smaller test sample. I continue to test on the larger data as soon as I am able. Thanks guys!