Formula Needed!!

[Degaulle]

Hi

I need to be able to look up a value of a product code that there are two of the same. There is a variable, the catalogue code.

In the example i need to look up the qty of code 401 for the SR Catalogue.

Book1
	A	B	C	D	E
1	Code	Name	CatalogueType	Price	QTYOnOrder
2	401	FF	S	160	100
3	401	FF	SR	160	72
4	402	SS	S	194	15
5	402	SS	SR	194	45
Sheet1

 

[Brian from Maui]

Create in another column, a unique value by concatenating, then use Index/Match

Book2
	A	B	C	D	E
1	Code	Catalogue	Qty	401	SR
2	401	S	100	401S	75
3	401	SR	75	401SR
4	402	S	15	402S
5	402	SR	45	402SR
Sheet1

In column C

=A2&CHAR(127)&B2 and drag down

In E2,

=INDEX($C$2:$C$5,MATCH(D1&CHAR(127)&E1,$D$2:$D$5,0))

 

[Aladin Akyurek]

Book1
	A	B	C	D	E	F
1	Code	Name	Catalogue Type	Price	QTY On Order	Concat
2	401	FF	S	160	100	401,FF,S
3	401	FF	SR	160	72	401,FF,SR
4	402	SS	S	194	15	402,SS,S
5	402	SS	SR	194	45	402,SS,SR
6
7
8	Code	Cat Type	Qty
9	401	SR	72
10	402	S	15
11
Sheet1

F2, copied down:

=A2&","&B2&","&C2

C9, copied down:

=INDEX($E$2:$E$5,MATCH(A9&",*"&B9,$F$2:$F$5,0))

 

[acw]

Hi

How about

=SUMPRODUCT(--(A2:A5=401),--(C2:C5="SR"),E2:E5)


Tony