How to add conditions to Match function?
 

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

How to add conditions to Match function?
 

this appears to work okay

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

array entered w/Ctrl+Shift+Enter

"Eric" wrote:

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

How to add conditions to Match function?
 

Works for me!

Biff

"JMB" wrote in message
...
this appears to work okay

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

array entered w/Ctrl+Shift+Enter

"Eric" wrote:

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

How to add conditions to Match function?
 

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?



"T. Valko" wrote:

Works for me!

Biff

"JMB" wrote in message
...
this appears to work okay

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

array entered w/Ctrl+Shift+Enter

"Eric" wrote:

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