Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
worksheet: 1
I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Let's say you have the following:
ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
I'm getting a result of (0) for everything
"Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Can you copy your equation into here and I'll take a peak?
"Johnny" wrote: I'm getting a result of (0) for everything "Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
This is what I was attempting to do before I posted my original question. The
if formula was not working. =IF(INVENTORY!B:B=$F$2,(VLOOKUP(F6,INVENTORY!$A$3: $D$6000,4,0)),"") This is the formula that you recommended. This formula is resulting in (0) for everything. =SUMPRODUCT(--(INVENTORY!$A$4:$A$3500=$F6),--(INVENTORY!$B$4:$B$3500=F$2),($D$4:$D$3500)) "Barb Reinhardt" wrote: Can you copy your equation into here and I'll take a peak? "Johnny" wrote: I'm getting a result of (0) for everything "Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Is it possible that values in column D aren't numbers?
"Johnny" wrote: This is what I was attempting to do before I posted my original question. The if formula was not working. =IF(INVENTORY!B:B=$F$2,(VLOOKUP(F6,INVENTORY!$A$3: $D$6000,4,0)),"") This is the formula that you recommended. This formula is resulting in (0) for everything. =SUMPRODUCT(--(INVENTORY!$A$4:$A$3500=$F6),--(INVENTORY!$B$4:$B$3500=F$2),($D$4:$D$3500)) "Barb Reinhardt" wrote: Can you copy your equation into here and I'll take a peak? "Johnny" wrote: I'm getting a result of (0) for everything "Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
column D is ok but I know that in column A I do have some ITEM#'s that are in
text formate EXAMPLE: (A-VG200-H5) "Barb Reinhardt" wrote: Is it possible that values in column D aren't numbers? "Johnny" wrote: This is what I was attempting to do before I posted my original question. The if formula was not working. =IF(INVENTORY!B:B=$F$2,(VLOOKUP(F6,INVENTORY!$A$3: $D$6000,4,0)),"") This is the formula that you recommended. This formula is resulting in (0) for everything. =SUMPRODUCT(--(INVENTORY!$A$4:$A$3500=$F6),--(INVENTORY!$B$4:$B$3500=F$2),($D$4:$D$3500)) "Barb Reinhardt" wrote: Can you copy your equation into here and I'll take a peak? "Johnny" wrote: I'm getting a result of (0) for everything "Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
VLOOKUP
Don't mind me I am a little slow at times. The formula that you gave me is
working fine. Apparently I didn't update the worksheet with the last portion of the formula. Thank you very much for you time. You have been very helpful. "Johnny" wrote: This is what I was attempting to do before I posted my original question. The if formula was not working. =IF(INVENTORY!B:B=$F$2,(VLOOKUP(F6,INVENTORY!$A$3: $D$6000,4,0)),"") This is the formula that you recommended. This formula is resulting in (0) for everything. =SUMPRODUCT(--(INVENTORY!$A$4:$A$3500=$F6),--(INVENTORY!$B$4:$B$3500=F$2),($D$4:$D$3500)) "Barb Reinhardt" wrote: Can you copy your equation into here and I'll take a peak? "Johnny" wrote: I'm getting a result of (0) for everything "Barb Reinhardt" wrote: Let's say you have the following: ITEM #: A2:A5 Stock location: B2:B5 Quantity: C2:C5 G2: 101398 G3: 101598 H1 = 1 I1 = 2 H2: =SUMPRODUCT(--($A$2:$A$5=$G2),--($B$2:$B$5=H$1),($C$2:$C$5)) Copy to H3, I2 and I3. Is that what you want? "Johnny" wrote: worksheet: 1 I have a list of ITEM#'s in column (A) and the coresponding STOCK LOCATION column (B). The QUANTITY of each item is in column (C). The ITEMS are sorted by STOCK LOCATION. (A) (B) (C) 101398 1 120 101538 1 500 101398 2 50 101538 2 100 worksheet: 2 I want to do VLOOKUP of the ITEM#s(in columns A & C) to get the QUANITY levels(in columns B & D), but I want to the formula in column (B) to only look for the items the match the STOCK LOCATION the is in cell ($A$1). And for column (D)'s formula to match cell ($C$1). (A) (B) | (C) (D) 1 | 2 ITEM QTY level | ITEM QTY level 101398 120 | 101398 50 101538 500 | 101538 100 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
VLOOKUP and its properties | Excel Worksheet Functions | |||
VLookup a Vlookup | Excel Worksheet Functions | |||
VLOOKUP Problem | Excel Discussion (Misc queries) | |||
VLOOKUP Limitations | Excel Worksheet Functions | |||
vlookup data hidden within worksheet | Excel Worksheet Functions |