On Fri, 22 Jul 2005 04:36:39 GMT, "Les" wrote:

The match function will search a random order range to find an exact match.
It will search an ascending order range to find an equal or lesser value.
And inversely it will find an equal or greater value in a descending range.
Is there a way or a function which will find a equal or greater value in a
random order range?

Thanks,
Les


The ARRAY ENTERED formula:

=MIN(IF(A1<=rng,rng))

will do what you require.

Put your value to lookup in A1.

rng may be a named range, or a cell reference.

To ARRAY ENTER a formula, hold down <ctrl<shift while hitting <enter. Excel
will place braces {...} around the formula if you did it correctly.

The formula will return a zero (0) if A1 is greater than all the values in rng.
If this is not satisfactory, you could use either a custom format, or an IF
formula, to return something else.


--ron