|
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(B4,B
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
=IF(AND(LOOKUP(F3,A21:A42)=F3,LOOKUP(B4,B21:B42)=B 4),(VLOOKUP(
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 |
| All times are GMT +1. The time now is 05:55 PM. |
Powered by vBulletin® Copyright ©2000 - 2025, Jelsoft Enterprises Ltd.
ExcelBanter.com