How to search for the last higher value from a list?

[Guest]

Does anyone know how to search for the last higher value from a list? for
example,
[A1] 100
[A2] 75
[A3] 60
[A4] 50
[A5] 40
[A6] 30
[A7] 20
[A8] 45, which is the last value from the lists, and return 50 on cell B8,
since
45 is higher than 20,30,40, until 45 is less than 50, therefore, the result
is less than 50. Does anyone know how to code it in excel?
Thank you in advance
Eric

 

[T. Valko]

huh?

Why return 50? I think you need to rephrase your explanation.

This formula will return the last value in an array that is less than a
target value (50):

=LOOKUP(2,1/(A1:A8<50),A1:A8)

Biff

 

[Guest]

Thank you for your reply, I would describe in more details with follwoing
examples

for example,
[A1] 100
[A2] 75
[A3] 60
[A4] 50
[A5] 40
[A6] 30
[A7] 20
[A8] 45, which is the last value from the lists, and return 50 on cell B8,
since 45 is higher than 20,30,40, until 45 is less than 50, therefore, the
result
is 50.

for example,
[A1] 100
[A2] 75
[A3] 60
[A4] 50
[A5] 40
[A6] 30
[A7] 20
[A8] 75, which is the last value from the lists, and return 75 on cell B8,
since 75 is higher than 20,30,40,50,60 until 75 is equal to 75 in [A2],
therefore, the result is 75.

for example,
[A1] 100
[A2] 75
[A3] 60
[A4] 50
[A5] 40
[A6] 30
[A7] 20
[A8] 101, which is the last value from the lists, and return 100 on cell B8,
since 100 is highest value from the upper lists, therefore, the result is
100.

Does anyone know how to code it in excel?
Thank you in advance
Eric

 

[Guest]

Try this formula in B2, Drag/Fill down as needed:

=IF( COUNTIF( $A$1:$A1, ">" & $A2 ) = 0, MAX( $A$1:$A1 ), INDEX( $A$1:$A1,
SUMPRODUCT( MAX( ( $A$1:$A1 >= $A2 ) * ROW( $A$1:$A1 ) ) ) ) )

 

[T. Valko]

This formula returns the expected results of your samples:

sample1 = 50
sample2 = 75
sample3 = 100

Entered as an array using the key combination of CTRL,SHIFT,ENTER (not just
ENTER):

=IF(A8=MAX(A1:A8),LARGE(A1:A8,2),MIN(IF(A1:A7>=A8,A1:A7)))

Biff

 

[Guest]

Thank everyone very much
Eric