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

[RWD715]

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)=B4),(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

 

[Sean Timmons]

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.

 

[Peo Sjoblom]

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]

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

 

[Sean Timmons]

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...

 

[Peo Sjoblom]

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]

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]

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

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]

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

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

 

[RWD715]

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.