Formula Needed for Identifying Higher Number AFTER Lower Number Identified

[matthewlouis]

I have a number in $A$2. I have another number in $B$2

In Column C, I have 60 rows of numbers.

In Column D, I have another 60 rows of numbers

In Column E, I have a formula that identifies when a number in Column C is less than $A$2. Let's say C30 is the cell that contains that number that is lower than $A$2.

Once cell C30 has been identified, I then need a formula that will look at Column D and identify the number that is HIGHER than $B$2. This identification must occur ONLY after Cell C30 has been identified, not before.

I need the first occurrence only, none after that.

Does anyone have an idea how to do a formula for that? I don't do VBA, I need a formula.

Thanks in advance!!

 

[matthewlouis]

DUH! My bad, you're right, sorry. Didn't mean to skip that.

So the 12 prints and then it looks for the next number in D greater than 30.

Sorry about that.

 

[Aladin Akyurek]

DUH! My bad, you're right, sorry. Didn't mean to skip that.

So the 12 prints and then it looks for the next number in D greater than 30.

Sorry about that.

Row\Col	A​	B​	C​	D​	E​	F​
1​
2​	15​	30​
3​
4​			field-1	field-2	field-3	field-4
5​			20​	5​
6​			12​	7​	12​
7​			30​	9​
8​			2​
9​			1​	32​		32​
10​				80​

In E5 enter and copy down:

=IF(1-ISNUMBER(LOOKUP(9.99999999999999E+307,$E$4:E4)),IF(C5<=$A$2,$C5,""),"")

In F5 enter and copy down:

=IF(ISNUMBER(LOOKUP(9.99999999999999E+307,$E$5:$E$10)),IF(1-ISNUMBER(LOOKUP(9.99999999999999E+307,$F$4:F4)),IF($D5>=$B$2,$D5,""),""),"")

 

[matthewlouis]

Your first formula worked GREAT, perfect. But the 2nd one did not (?) Here is my actual table, hope this helps explain it better.

SPL	SPL High	High	Close	Closed Under SPL	Closed Over High
262.71	263.73
		264.43	264.07		264.07	Your formula returned this, it isn't right
		265.52	265.51
		266.38	266.31
		267.32	266.78
		267.56	266.75
		267.22	265.66
		267.04	266.51
		268.60	268.20
		268.53	267.17
		268.33	267.03
		268.39	267.58
		267.64	267.51
		267.64	267.51
		267.44	267.19
		267.73	267.32
		267.92	267.87
		268.55	266.86
		270.64	270.47
		272.16	271.61
		273.56	273.42
		274.10	273.92
		275.25	274.54
		274.42	274.12
		276.12	276.12
		278.11	277.92
		280.05	279.61
		279.96	279.14
		280.41	280.41
		282.69	282.69
		283.62	283.29
		284.70	283.18
		284.27	283.30
		286.63	286.58
		286.43	284.68
		284.74	281.76
		283.30	281.90
		283.06	281.58
		280.23	275.45
		275.85	263.93
		269.70	269.13	257.63
		272.36	267.67			should be 267.67 because it came AFTER the 257.63
		268.17	257.63
		263.61	261.50
		267.01	265.34
		266.62	266.00
		270.00	269.59
		273.04	273.03
		275.32	273.11
		274.72	270.05
		273.05	270.40

<tbody>
</tbody>

 

[Aladin Akyurek]

I wonder... I don't see any 257.63 under High...

 

[matthewlouis]

See the 257.63 down some n the Close column?

 

[Aladin Akyurek]

No, that 257.63 is the close under the low in A2, a calc I have somewhere else and is linked to this page.

I need to know when the Close is higher than the High AFTER that first calculation (your first formula) printed.

Why don't you just put what must be returned in E and F for the sample you posted?

 

[matthewlouis]

Because the 2nd calculation can't be done until the first one confirms. The 2nd formula you did put a number in the first cell. It should have been 267.67 in the CLOSE column which is the first close HIGHER than the SPL's High ($B$2).

So the E calc = 257.63

The F calc = 267.67

Again, your first formula nailed it, it's the 2nd one that doesn't work.

 

[Aladin Akyurek]

The E calc result = 257.63 comes from D (not from C), right?

The F calc must come form C, right?

 

[matthewlouis]

That table I sent is messed up. Let me resend
To answer your question . . . YES . . . the E calc result = 257.63 comes from D (not from C)

YES . . . the F calc must come from C -- the answer is 267.01 from column C

Thanks for all your help on this, by the way, you're the best.

I'll resend table

 

[matthewlouis]

That table I sent is messed up. Let me resend
To answer your question . . . YES . . . the E calc result = 257.63 comes from D (not from C)

YES . . . the F calc must come from C -- the answer is 267.01 from column C

Thanks for all your help on this, by the way, you're the best.

I'll resend table

OK, messed up again here.

The E calc result = 257.63 comes from D AND the F calc also comes from D . . . they BOTH come from D. The first one is LOWER than $A$2 and the 2nd one is HIGHER than $B$2

I am so sorry I am all over the place but my table columns don't match your example and that's why I've been so off.