Why isn't the IF portion of this formula working?

[sambharris6]

So this is apart of a larger data subset and I'm simplifying it here, why is the IF formula not doing the FALSE? D12:D16 & D25:D33 should be zero, right?

Book1
	C	D	E	F	G	H	I
5		FORMULAS				DATA
6	1					1	a
7		a				1	b
8		b				1	c
9		c				1	d
10		d				1	e
11		e
12		0
13		0
14		0				2	jj
15		jj				2	dd
16		dd				2	ee
17						2	ww
18						2	vv
19	2
20		jj
21		dd
22		ee
23		ww
24		vv				3	ccc
25		0				3	ddd
26		0				3	rrrr
27		0
28		0
29		0
30		ccc
31		ddd
32		rrrr
33		0
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(C6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)),"")
D8	D8	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D7:D$7)),"")
D9:D16	D9	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D8)),"")
D20	D20	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)),"")
D21:D33	D21	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$20:D20)),"")

 

[Peter_SSs]

why is the IF formula not doing the FALSE?

Because the test at the beginning of the IF statement is always True, not False. For example, the formula in D12 is
=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D11)),"")

Here, $C$6=1 is True so the True part of the formula is evaluated.

 

[sambharris6]

So this is apart of a larger data subset and I'm simplifying it here, why is the IF formula not doing the FALSE? D12:D16 & D25:D33 should be zero, right?

Book1
	C	D	E	F	G	H	I
5		FORMULAS				DATA
6	1					1	a
7		a				1	b
8		b				1	c
9		c				1	d
10		d				1	e
11		e
12		0
13		0
14		0				2	jj
15		jj				2	dd
16		dd				2	ee
17						2	ww
18						2	vv
19	2
20		jj
21		dd
22		ee
23		ww
24		vv				3	ccc
25		0				3	ddd
26		0				3	rrrr
27		0
28		0
29		0
30		ccc
31		ddd
32		rrrr
33		0
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(C6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)),"")
D8	D8	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D7:D$7)),"")
D9:D16	D9	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D8)),"")
D20	D20	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)),"")
D21:D33	D21	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$20:D20)),"")

Oops bad post I think I got a solution!

 

[sambharris6]

Because the test at the beginning of the IF statement is always True, not False. For example, the formula in D12 is
=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D11)),"")

Here, $C$6=1 is True so the True part of the formula is evaluated.

Right totally should have took c6 locked it and rolled it against h column.

 

[sambharris6]

Can't figure this out, is there a better solution or formula to make this work?

 

[sambharris6]

Right totally should have took c6 locked it and rolled it against h column.

Didn't actually work here.

 

[sambharris6]

Because the test at the beginning of the IF statement is always True, not False. For example, the formula in D12 is
=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D11)),"")

Here, $C$6=1 is True so the True part of the formula is evaluated.

Do you have fix for this issue? A formula that would work in this instance? I need it to roll in secession and be blank where its supposed to

Book1
	C	D	E	F	G	H	I
5		FORMULAS				DATA
6	1					1	a
7		a				1	b
8		b				1	c
9		c				1	d
10		d				1	e
11		e
12		0
13		0
14		0				2	jj
15		jj				2	dd
16		dd				2	ee
17		ee				2	ww
18		ww				2	vv
19	2	rrrr
20		0
21		0
22		0
23		0
24		0				3	ccc
25		ccc				3	ddd
26		ddd				3	rrrr
27		rrrr
28	3	0
29		0
30		0
31		0
32		0
33		0
34		0
35		0
Sheet1

Cell Formulas
Range	Formula
D7	D7	=INDEX($I:$I,MATCH($C$6,$H:$H,0))
D8	D8	=INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D7:D$7))
D9:D18,D20:D35	D9	=INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D8))
D19	D19	=INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$7:D18))

 

[sambharris6]

Added an OR formula but if the Data moves like the 2 data set then it doesn't pick it up, yellow cell.

Book1
	C	D	E	F	G	H	I
5		FORMULAS				DATA
6	1					1	a
7		a				1	b
8		b				1	c
9		c				1	d
10		d				1	e
11		e
12
13
14
15
16
17
18
19	2
20
21		dd
22		ee
23		ww
24		vv				3	ccc
25						3	ddd
26						3	rrrr
27
28	3	ccc
29
30
31
32
33
34
35
36
37						2	jj
38						2	dd
39						2	ee
40						2	ww
41						2	vv
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(OR($C$6<>H6,$C$6=""),"",INDEX($I:$I,MATCH($C$6,$H:$H,0)))
D8	D8	=IF(OR($C$6<>H7,$C$6=""),"",INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D7:D$7)))
D9:D14	D9	=IF(OR($C$6<>H8,$C$6=""),"",INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D8)))
D15:D18	D15	=IF(OR($C$6<>H37,$C$6=""),"",INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D14)))
D20	D20	=IF(OR($C$19<>H14,$C$19=""),"",INDEX($I:$I,MATCH($C$19,$H:$H,0)))
D21	D21	=IF(OR($C$19<>H38,$C$19=""),"",INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D20:D$20)))
D22:D24	D22	=IF(OR($C$19<>H39,$C$19=""),"",INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$20:D21)))
D25:D27	D25	=IF(OR($C$19<>H20,$C$19=""),"",INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$20:D24)))
D28,D32:D39	D28	=IF(OR($C$28<>H24,$C$28=""),"",INDEX($I:$I,MATCH($C$28,$H:$H,0)))

 

[sambharris6]

So this is apart of a larger data subset and I'm simplifying it here, why is the IF formula not doing the FALSE? D12:D16 & D25:D33 should be zero, right?

Book1
	C	D	E	F	G	H	I
5		FORMULAS				DATA
6	1					1	a
7		a				1	b
8		b				1	c
9		c				1	d
10		d				1	e
11		e
12		0
13		0
14		0				2	jj
15		jj				2	dd
16		dd				2	ee
17						2	ww
18						2	vv
19	2
20		jj
21		dd
22		ee
23		ww
24		vv				3	ccc
25		0				3	ddd
26		0				3	rrrr
27		0
28		0
29		0
30		ccc
31		ddd
32		rrrr
33		0
Sheet1

Cell Formulas
Range	Formula
D7	D7	=IF(C6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)),"")
D8	D8	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D7:D$7)),"")
D9:D16	D9	=IF($C$6=1,INDEX($I:$I,MATCH($C$6,$H:$H,0)+COUNTA(D$7:D8)),"")
D20	D20	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)),"")
D21:D33	D21	=IF($C$19=2,INDEX($I:$I,MATCH($C$19,$H:$H,0)+COUNTA(D$20:D20)),"")

INDEX($I:$I,AGGREGATE(15,3,($H:$H=$C$6)/($H:$H=$C$6)*ROW($H:$H),ROWS($N$6:N7))) solved it