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

R

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

S

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

P

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

R

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

S

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

P

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

R

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

S

#### Sean Timmons

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

RWD715 said:
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

S

#### Sean Timmons

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

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

R