Re: Match function...random search?

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Fri, 22 Jul 2005 04:36:39 GMT, "Les" <lescarlson@xxxxxxxxxxxxxxxx> 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
.