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!!

 

[DRSteele]

Do you mean to say that you wish to identify the value of the first cell in ColumnD that is higher than B2 AFTER row 30?

 

[StephenCrump]

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.

What is the connection between:

1. The identification of cell C30, and

2. The formula that will look at Column D and identify the number that is HIGHER than $B$2.

Based on your description, these look to be independent?

 

[matthewlouis]

Yes, I just used row C30 as an example. That number could be in row C12, C44, etc.... So yes, I need the FIRST number from Column D that is higher than $B$2 ONLY AFTER the number from Column C has been identified, not before. It's conditional. Once the number that is below $A$2 has been identified, now look to find the number in Column D that is higher than $B$2. I just need the first occurrence for each one (the number that is lower than $A$2 looking in Column C -- and once that occurs -- the next number that is higher than $B$ looking in Column D.

Thanks for responding! Hope this helps. what do you think?

 

[matthewlouis]

Thanks for responding StephenCrump. The numbers are not independent. The ID of C30 must occur first. Only then, do you move the Column D formula that identifies the number HIGHER than $B$2.

In other words, ONLY when you have identified the FIRST number in Column C that is below $A$2 do you look in Column D to identify the first number that is HIGHER than $B$2.

Thanks, I hope this response helps in addition to the posted response above.

 

[Aladin Akyurek]

Given this, what is the expected result?

15	30
		20	5
		12	7
		30	9
		2
		1	32
			80

<tbody>
</tbody>

 

[matthewlouis]

There needs to be a column (Column D) between where the 20 (Column C) and the 5 is . . . if you go down where the 2, that is the first cell that is below 15 so it would print a 2 in Column D. That satisfies the first condition . . . now it's time look for the 2nd condition and print that satisfied condition in Column 6 -- now that the 2 has printed, what is the next cell in the column that begins with 5 have a value greater than 30?

That would be 32 since 32 is greater than 30 (at the top). A 32 would print in the column right next to the 32.

So you have two column. Once column has a 2 as the only cell and the other column has a 32. There would be no other cells filled in either column.

Hope this helps. Can you do a formula for these?

 

[Aladin Akyurek]

Once more...

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

What is the expected value given A2 = 15 and B2 = 30?

 

[matthewlouis]

In e8, 2 would print since 2 is < A2

In f9, 32 would print since 2 printed in E8 AND 32 > b2.

There are 2 expected values, not just one. Two formulas for Columns e and f

 

[Aladin Akyurek]

In e8, 2 would print since 2 is < A2

In f9, 32 would print since 2 printed in E8 AND 32 > b2.

There are 2 expected values, not just one. Two formulas for Columns e and f

12 in column A is < A2. Why do you skip that?