How to add conditions to Match function?

[Guest]

Re-post Question


Does anyone have any suggestions on how to add conditions to Match function?
I get 4 numbers under the cell A1:A4, and I would like to determine which
number under cell A2:A4 is larger than cell A1 and closer to / equal to A1,
such as

Those numbers are 10[A1], 17[A2], 20[A3], 10[A4], then the number 10 should
be return in cell B1 in this case. On the other hands, if all numbers under
A2:A4 are smaller than A1, then returns the max number A2:A4 in cell B1.
Does anyone have any suggestions?
Thank you in advance
Eric

 

[Guest]

this appears to work okay

=MEDIAN(MIN(IF(A2:A4>=A1,A2:A4)),MAX(A2:A4),A1)

array entered w/Ctrl+Shift+Enter

 

[T. Valko]

Works for me!

Biff

this appears to work okay

=MEDIAN(MIN(IF(A2:A4>=A1,A2:A4)),MAX(A2:A4),A1)

array entered w/Ctrl+Shift+Enter

Re-post Question


Does anyone have any suggestions on how to add conditions to Match
function?
I get 4 numbers under the cell A1:A4, and I would like to determine which
number under cell A2:A4 is larger than cell A1 and closer to / equal to
A1,
such as

Those numbers are 10[A1], 17[A2], 20[A3], 10[A4], then the number 10
should
be return in cell B1 in this case. On the other hands, if all numbers
under
A2:A4 are smaller than A1, then returns the max number A2:A4 in cell B1.
Does anyone have any suggestions?
Thank you in advance
Eric

 

[Guest]

Except that I now notice it will not work if A1 is positive or zero and the
numbers below it are all negative.

For the OP - can the numbers below A1 be negative?

Works for me!

Biff

this appears to work okay

=MEDIAN(MIN(IF(A2:A4>=A1,A2:A4)),MAX(A2:A4),A1)

array entered w/Ctrl+Shift+Enter

Re-post Question


Does anyone have any suggestions on how to add conditions to Match
function?
I get 4 numbers under the cell A1:A4, and I would like to determine which
number under cell A2:A4 is larger than cell A1 and closer to / equal to
A1,
such as

Those numbers are 10[A1], 17[A2], 20[A3], 10[A4], then the number 10
should
be return in cell B1 in this case. On the other hands, if all numbers
under
A2:A4 are smaller than A1, then returns the max number A2:A4 in cell B1.
Does anyone have any suggestions?
Thank you in advance
Eric