Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#2
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#3
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
#4
Posted to microsoft.public.excel.misc
|
|||
|
|||
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 |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How to add conditions to Match function? | Excel Discussion (Misc queries) | |||
Match 2 Conditions then Avg Range | Excel Discussion (Misc queries) | |||
lookup with INDEX MATCH formule depending on 2 conditions | Excel Worksheet Functions | |||
index match array function-returning only first match, need last. | Excel Worksheet Functions | |||
How do I sum numbers that match two conditions? | Excel Worksheet Functions |