I have a table that has 3 columns: location (A), shift (B) and headcount (C).
I have another table that has location and shift columns by work day rows. I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. €œTrace Precedents€�
shows that only the A and B columns are €œseen.€� Using VLOOKUP got the same
results.

Is what Im trying to do possible? How?

Thanks in advance for any help on this.
Bob

So, you are trying to sum headcount where location and shift match.

=SUMPRODUCT(--(A2:A1000=E2),--(B2:B1000=F2),(C2:C1000))

Where E is your summary table's location field and B is your summary shift
field.

"RWD715" wrote:

I have a table that has 3 columns: location (A), shift (B) and headcount (C).
I have another table that has location and shift columns by work day rows. I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. €œTrace Precedents€�
shows that only the A and B columns are €œseen.€� Using VLOOKUP got the same
results.

Is what Im trying to do possible? How?

Thanks in advance for any help on this.
Bob

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

My formula above covers this example. If you were to have multiple instances
of a location and shift for some reason, it would total up the headcount as
well...

"RWD715" wrote:

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

Both my formula and Sean's formula would do this, since you are looking for
a
numerical value and in case there can be multiple hits then it is better
using SUMPRODUCT

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I want to return the headcount value from this table based upon the
location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second
column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND
part
is also incorrect since you can't return a FALSE alternative because
either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42
equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day
rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the
same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

SUMPRODUCT worked, thanks for that, now for the new wrinkle: 3 shift columns.

Shift # Shift # Shift # Altamont
Sunday 10/12 1 2
Sunday 10/12 6 3
Monday 10/13 1 4
Monday 10/13 6 8
Tuesday 10/14 1 4
Tuesday 10/14 6 8
Wednesday 10/15 1 3
Wednesday 10/15 6 7
Thursday 10/16 4 3


"Sean Timmons" wrote:

My formula above covers this example. If you were to have multiple instances
of a location and shift for some reason, it would total up the headcount as
well...

"RWD715" wrote:

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

Oops.. change A:B to B2:E500 and C:C to F2:F500

"RWD715" wrote:

SUMPRODUCT worked, thanks for that, now for the new wrinkle: 3 shift columns.

Shift # Shift # Shift # Altamont
Sunday 10/12 1 2
Sunday 10/12 6 3
Monday 10/13 1 4
Monday 10/13 6 8
Tuesday 10/14 1 4
Tuesday 10/14 6 8
Wednesday 10/15 1 3
Wednesday 10/15 6 7
Thursday 10/16 4 3


"Sean Timmons" wrote:

My formula above covers this example. If you were to have multiple instances
of a location and shift for some reason, it would total up the headcount as
well...

"RWD715" wrote:

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

So, you want the sum of all hours from shift 1..

=SUMPRODUCT(--(A1:B4=shift#)*(C1:C4))

"RWD715" wrote:

SUMPRODUCT worked, thanks for that, now for the new wrinkle: 3 shift columns.

Shift # Shift # Shift # Altamont
Sunday 10/12 1 2
Sunday 10/12 6 3
Monday 10/13 1 4
Monday 10/13 6 8
Tuesday 10/14 1 4
Tuesday 10/14 6 8
Wednesday 10/15 1 3
Wednesday 10/15 6 7
Thursday 10/16 4 3


"Sean Timmons" wrote:

My formula above covers this example. If you were to have multiple instances
of a location and shift for some reason, it would total up the headcount as
well...

"RWD715" wrote:

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob

My oops. Sorry for the misleading wrinkle description-should have made it
clear that the 3 shift columns are in the target table, not the source. Fixed
it by adding 3 SUMPRODUCTS together, 1 for each shift column. May not be
elegant, but it works.

Thanks again for the help.

"Sean Timmons" wrote:

Oops.. change A:B to B2:E500 and C:C to F2:F500

"RWD715" wrote:

SUMPRODUCT worked, thanks for that, now for the new wrinkle: 3 shift columns.

Shift # Shift # Shift # Altamont
Sunday 10/12 1 2
Sunday 10/12 6 3
Monday 10/13 1 4
Monday 10/13 6 8
Tuesday 10/14 1 4
Tuesday 10/14 6 8
Wednesday 10/15 1 3
Wednesday 10/15 6 7
Thursday 10/16 4 3


"Sean Timmons" wrote:

My formula above covers this example. If you were to have multiple instances
of a location and shift for some reason, it would total up the headcount as
well...

"RWD715" wrote:

I want to return the headcount value from this table based upon the location
and shift values. If location = Altamont and shift = 4 then return 2. If
location = Elk City and shift = 8 then return 4.
A B C
20 Location Shift HeadCount
21 Altamont 4 2
22 Altamont 8 2
23 Elk City 1 1
24 Elk City 8 4
25 Heritage 1 1
26 Heritage 8 2
27 WJP 3 6
28 WJP 1 10



"Peo Sjoblom" wrote:

You can't get anything but either #N/A or something from the second column
with that formula? For instance the 2 at the end of VLOOKUP tells the
formula to return a match from the 2nd column and it doesn't look for an
exact match either so B21:B42 needs to be in ascending order. The AND part
is also incorrect since you can't return a FALSE alternative because either
the 2 LOOKUPS return TRUE or #N/A

If you want what's in C21:C42 when A21:A42 equals F3 and when B21:B42 equals
B4 then you can use


=INDEX(C21:C42,MATCH(1,(A21:A42=F3)*(B21:B42=B4),0 ))

entered with ctrl + shift & enter


If that's not what you are looking for please post back with a thorough
explanation of what's in
the different ranges and what you want it to return, by looking at your
formula that is impossible to guess.

--


Regards,


Peo Sjoblom

"RWD715" wrote in message
...
I have a table that has 3 columns: location (A), shift (B) and headcount
(C).
I have another table that has location and shift columns by work day rows.
I
want to return the headcount value from the 1st table into the location
column of the 2nd table based upon the location column and shift row
values
for that day. I tried:

=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B21:C42,2)))

and got a return from the 2nd column instead of the 3rd. "Trace
Precedents"
shows that only the A and B columns are "seen." Using VLOOKUP got the same
results.

Is what I'm trying to do possible? How?

Thanks in advance for any help on this.
Bob