Search a variable range based on cell address

[GColeman]

The formula or process I'm looking for can't be as hard as I'm making it. Basically I have 2 columns of numbers. I want to search the right column for the first time a value over 70 occurs. If that occurs in D8, then I want to search above that point in C, so C3:C8 to see if a 0 occurs. If 70 is first found in D10, then search C3:C10 to see if there is a 0. Just looking to see if the left column has a 0 before the right column exceeds 70. True/False. In the example provided, there is a 0 C5 so I should get a result of "True". Seems simple, but I can't make it work.
Now, in my case, the right side is cumulative, so D4=D3+C4., and D5=D4+C5 (with some additional criteria). I could get away with seaching D3:D16 for consecutive identical numbers greater than 70.
Any help is appreciated.

 

[awoohaw]

Try this:

Book1
	A	B	C	D	E
1
2
3			10	10	FALSE
4			14	24	FALSE
5			0	24	FALSE
6			14	38	FALSE
7			12	50	FALSE
8			12	62	FALSE
9			12	74	TRUE
10			12	86	TRUE
11			12	98	TRUE
12			12	110	TRUE
13			10	120	TRUE
14			0	120	TRUE
15			12	132	TRUE
16			12	144	TRUE
Sheet3

Cell Formulas
Range	Formula
E3:E16	E3	=AND(D3>70,ISNUMBER(MATCH(0,$C$3:$C3,0)))

 

[Peter_SSs]

I know my data doesn't fit with this ..

the right side is cumulative,

.. but see if it does what you want anyway.
It wasn't clear whether there will always be a zero somewhere in the left column or not.
It also wasn't clear whether there will always be a >70 value in the right column or not.
So I have tried to allow for all those possibilities.

24 02 19.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P	Q	R	S	T	U
1
2				0 before 70 exceeded?					0 before 70 exceeded?					0 before 70 exceeded?					0 before 70 exceeded?
3	10	10		TRUE		10	10		FALSE		10	10		FALSE		10	10		TRUE
4	14	24				14	24				14	24				14	24
5	0	24				14	24				14	24				14	24
6	14	38				14	38				14	24				14	24
7	12	50				12	50				14	24				14	24
8	12	62				12	62				12	24				12	24
9	12	74				12	74				12	24				12	24
10	12	86				12	86				12	24				12	24
11	12	98				12	98				12	24				12	24
12	12	110				12	110				12	24				12	24
13	10	120				10	120				10	24				10	24
14	0	120				0	120				12	24				0	24
15	12	132				12	132				12	24				12	24
16	12	144				12	144				12	24				12	24
0 before 70 exceeded

Cell Formulas
Range	Formula
F3,U3,P3,K3	F3	=COUNTIF(C3:INDEX(C3:C16,IFNA(MATCH(TRUE,D3:D16>70,0),ROWS(C3:C16))),0)>0
Press CTRL+SHIFT+ENTER to enter array formulas.

 

[GColeman]

Thank you. I was definately overthinking it. Both answers worked, but the second one from Peter_SSs was more compact.
And I appreciate the variables built in. Yes, column D will always exceed 70, no, column C will not always have a 0.

 

[Peter_SSs]

Yes, column D will always exceed 70, no, column C will not always have a 0.

In that case the formula can be simplified to this

24 02 19.xlsm
	C	D	E	F	G	H	I	J	K	L	M	N	O	P
1
2				0 before 70 exceeded?					0 before 70 exceeded?					0 before 70 exceeded?
3	10	10		TRUE		10	10		FALSE		10	10		FALSE
4	14	24				14	24				14	24
5	0	24				14	24				14	24
6	14	38				14	38				14	38
7	12	50				12	50				12	50
8	12	62				12	62				12	62
9	12	74				12	74				12	74
10	12	86				12	86				12	86
11	12	98				12	98				12	98
12	12	110				12	110				12	110
13	10	120				10	120				10	120
14	0	120				0	120				12	120
15	12	132				12	132				12	132
16	12	144				12	144				12	144
0 before 70 exceeded

Cell Formulas
Range	Formula
F3,P3,K3	F3	=COUNTIF(C3:INDEX(C3:C16,MATCH(TRUE,D3:D16>70,0)),0)>0
Press CTRL+SHIFT+ENTER to enter array formulas.