If ,index , match

[aarti_rto]

Hi,

i used below forlmula with curley brackets in C Column , to get values like in D column but something is wrong in this formula .

=IF(COUNTIF($B$24:$B24,B24)=1,AND(COUNTIF($C$24,$C$24)=1,INDEX($J$24:$J$30,MATCH(1,(B24=$H$24:$H$30)*(C24=$I$24:$I$30),0)),0))

Pl help...

Column	A	B	C	D			G	H	I
Row
4	Supplier	Part No	Stock As on Date ( forluma get results "False" )	Values Required by Formula			Supplier	Part Source	Stock
5	AAA	ABC	FALSE​	100​			AAA	ABC	100​
6	AAA	ABC	FALSE​	0​			BBB	CBD	200​
7	BBB	CBD	FALSE​	200​			CCC	XYZ	100​
8	CCC	XYZ	FALSE​	100​			DDD	ABC	300​
9	DDD	XYZ	FALSE​	700​			EEE	CBD	400
10	EEE	CBD	FALSE​	400			CCC	ABC	600​
11	CCC	ABC	FALSE​	600​			DDD	XYZ	700​
12	CCC	XYZ	FALSE​	0​
13	BBB	CBD	FALSE​	0​

 

[ravi2628]

hi aarti,

Please Find the Logic.

Book1
	A	B	C	D	E	F	G	H	I
8	Supplier	Part No	Stock As on Date ( forluma get results "False" )	Values Required by Formula			Supplier	Part Source	Stock
9	AAA	ABC	FALSE	100			AAA	ABC	100
10	AAA	ABC	FALSE	0			BBB	CBD	200
11	BBB	CBD	FALSE	200			CCC	XYZ	100
12	CCC	XYZ	FALSE	100			DDD	ABC	300
13	DDD	XYZ	FALSE	700			EEE	CBD	400
14	EEE	CBD	FALSE	400			CCC	ABC	600
15	CCC	ABC	FALSE	600			DDD	XYZ	700
16	CCC	XYZ	FALSE	0
17	BBB	CBD	FALSE	0
Sheet1

Cell Formulas
Range	Formula
D9:D17	D9	=IF(COUNTIFS(A$9:A9,A9,B$9:B9,B9)=1,INDEX($I$9:$I$15,MATCH(1,(A9=$G$9:$G$15)*(B9=$H$9:$H$15),0)),0)

 

[Fluff]

Another option that doesn't need array entry

+Fluff 1.xlsm
	A	B	C	D	E	F	G	H	I
1
2
3
4	Supplier	Part No	Stock As on Date				Supplier	Part Source	Stock
5	AAA	ABC	100				AAA	ABC	100
6	AAA	ABC	0				BBB	CBD	200
7	BBB	CBD	200				CCC	XYZ	100
8	CCC	XYZ	100				DDD	ABC	300
9	DDD	XYZ	700				EEE	CBD	400
10	EEE	CBD	400				CCC	ABC	600
11	CCC	ABC	600				DDD	XYZ	700
12	CCC	XYZ	0
13	BBB	CBD	0
14
Main

Cell Formulas
Range	Formula
C5:C13	C5	=IF(COUNTIFS(A$5:A5,A5,B$5:B5,B5)=1,SUMIFS($I$5:$I$11,$G$5:$G$11,A5,$H$5:$H$11,B5),0)