VLOOKUP

[Johnny]

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

 

[Barb Reinhardt]

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]

I'm getting a result of (0) for everything

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?

 

[Barb Reinhardt]

Can you copy your equation into here and I'll take a peak?

 

[Johnny]

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]

Is it possible that values in column D aren't numbers?

 

[Johnny]

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)

 

[Johnny]

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.