LOOKUP or IF??

[ABC Seamless Wayne]

I would like to know if there is a way to do the following:

File #1

Product Key Count
Red/12 1000
Red/24 2000
Green/36 3000

File#2
Size Red Blue Green....
12
24
36

I have 19 colors, 3 sizes--I want to look at File#1 (this may have all 19
colors and 3 sizes, or 3 colors and 2 sizes listed by color/size) and fill in
the File #2 with amounts.

Any Help Please???

 

[RagDyer]

Is Red/12 in a *single* cell?

 

[ABC Seamless Wayne]

Yes it is. I had them in three cells then converted them to two, so they can
be either.

 

[RagDyer]

It's easier if you they're each in their own column, so go back to using 3.

On Sheet1,
With color in A2 to A25,
Size in B2 to B25,
And count in C2 to C25

On Sheet2,
Size in A2 to A4,
And color starting in B1, and going out along Row 1 to T1 (19 colors).

Try this in B2:

=SUMPRODUCT((Sheet4!$A$2:$A$25=B$1)*
(Sheet4!$B$2:$B$25=$A2)*Sheet4!$C$2:$C$25)

And copy down to B4,
Then copy across to T1.

 

[ABC Seamless Wayne]

THANK YOU!!! It is working Great!!

It's easier if you they're each in their own column, so go back to using 3.

On Sheet1,
With color in A2 to A25,
Size in B2 to B25,
And count in C2 to C25

On Sheet2,
Size in A2 to A4,
And color starting in B1, and going out along Row 1 to T1 (19 colors).

Try this in B2:

=SUMPRODUCT((Sheet4!$A$2:$A$25=B$1)*
(Sheet4!$B$2:$B$25=$A2)*Sheet4!$C$2:$C$25)

And copy down to B4,
Then copy across to T1.

 

[RagDyeR]

I apologize for not matching the sheet references in my formula to the sheet
references in my scenario.

You're welcome, and thanks for the feed-back.