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?

[TABLE="class: grid, width: 320"]
<tbody>[TR]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[TD="width: 64, bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent, align: right"]15[/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]20[/TD]
[TD="bgcolor: transparent, align: right"]5[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]12[/TD]
[TD="bgcolor: transparent, align: right"]7[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]30[/TD]
[TD="bgcolor: transparent, align: right"]9[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]2[/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]1[/TD]
[TD="bgcolor: transparent, align: right"]32[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
[TR]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent"][/TD]
[TD="bgcolor: transparent, align: right"]80[/TD]
[TD="bgcolor: transparent"][/TD]
[/TR]
</tbody>[/TABLE]

 

[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...

[Table="width:, class:grid"][tr][td]Row\Col[/td][td]

A​

[/td][td]

B​

[/td][td]

C​

[/td][td]

D​

[/td][td]

E​

[/td][/tr][tr][td]

1​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

2​

[/td][td]

15​

[/td][td]

30​

[/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

3​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

4​

[/td][td][/td][td][/td][td][/td][td][/td][td][/td][/tr]
[tr][td]

5​

[/td][td][/td][td][/td][td]

20​

[/td][td]

5​

[/td][td][/td][/tr]
[tr][td]

6​

[/td][td][/td][td][/td][td]

12​

[/td][td]

7​

[/td][td][/td][/tr]
[tr][td]

7​

[/td][td][/td][td][/td][td]

30​

[/td][td]

9​

[/td][td][/td][/tr]
[tr][td]

8​

[/td][td][/td][td][/td][td]

2​

[/td][td][/td][td][/td][/tr]
[tr][td]

9​

[/td][td][/td][td][/td][td]

1​

[/td][td]

32​

[/td][td][/td][/tr]
[tr][td]

10​

[/td][td][/td][td][/td][td][/td][td]

80​

[/td][td][/td][/tr]
[/table]

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?