Find last cell that meets condition in unsorted list [SOLVED]

[Vink]

Hi,

Could anyone help me construct a formula that finds the last cell (row nr) in a range that meets a condition. The values in the range are unsorted. Example with B containing the unsorted range:
A __ B
10 2,2
20 3,3
30 2,2
40 3,3
Suppose the condition is B<3 then the function should return 30 because the third row contains the last cell where B<3.

Kind regards, Daniel.

 

[Somendra Misra]

Hi Daniel,

Welcome to chandoo.org forum.

One question are you always check for condition less than "<"? If so try below formula:

=INDEX($A$1:$A$4,LARGE(IF($B$1:$B$4<D2,ROW($B$1:$B$4)-ROW($B$1)+1),1))

Note this is an array formula so must be entered with Ctrl+Shift+Enter.

Here data is in A1:B4.

Change the red relational operator inside IF to meet your requirement.

Regards,

 

[Vink]

Hi Daniel,

Welcome to chandoo.org forum.

One question are you always check for condition less than "<"? If so try below formula:

=INDEX($A$1:$A$4,LARGE(IF($B$1:$B$4<D2,ROW($B$1:$B$4)-ROW($B$1)+1),1))

Note this is an array formula so must be entered with Ctrl+Shift+Enter.

Here data is in A1:B4.

Change the red relational operator inside IF to meet your requirement.

Regards,

Dear Somendra,
Thank you very much, works great. I had to change the fixed ranges to a dynamic offset-formula because at the end of the list some cells contained "" due to IF(condition; someformula; ""). The "" resulted in an error.
I understand the working of the formula, but could not have come up with it myself, so thanks again.
Daniel.

BTW, do I have to close the thread because question has been satisfactorily addressed?

 

[shrivallabha]

The threads are not closed normally here unless they infringe some forum rules.

 

[NARAYANK991]

Hi Shrivallabha ,

The thread which according to the OP has been resolved to his / her satisfaction , can be marked SOLVED.

Narayan

 

[Somendra Misra]

@Vink

Glad you solve it. Welcome back anytime.
Regards,

 

[Sajan]

Hi,
Here is one more approach:
=LOOKUP(2,1/(B1:B4<3),A1:A4)

Cheers,
Sajan.