vba help pls - find min based on a condition and return val of an offset cell

Hi all,

I need vba code that will say:

Find the minimum value in col M that is in the same row as the
condition "Male" (col C), then return the value (text) from col A in
that row. EG:

A C M
Name Division Time

Joe Male 00:05:23
Sue Female 00:06:36
Tom Male 00:04:56
Tim Open 00:04:33

The correct information to return in this example would be "Tom". I
need to have "Tom" appear as a lable.caption in a form. I need to be
able to code it so that it can find the minimum value for each
division (the winner of a race).

Any help greatly appreciated.

Something like:

=INDEX(A2:A10,MATCH(MIN(IF(C2:C10="Male",M2:M10)),M2:M10,0))

entered as an array formula with <Ctrl> <Shift> <Enter>.

--

Vasant

"Impakt" <[email protected]> wrote in message
news:[email protected]...
> Hi all,
>
> I need vba code that will say:
>
> Find the minimum value in col M that is in the same row as the
> condition "Male" (col C), then return the value (text) from col A in
> that row. EG:
>
> A C M
> Name Division Time
>
> Joe Male 00:05:23
> Sue Female 00:06:36
> Tom Male 00:04:56
> Tim Open 00:04:33
>
> The correct information to return in this example would be "Tom". I
> need to have "Tom" appear as a lable.caption in a form. I need to be
> able to code it so that it can find the minimum value for each
> division (the winner of a race).
>
> Any help greatly appreciated.

On Mon, 16 May 2005 11:11:47 -0400, "Vasant Nanavati" <vasantn *AT*
aol *DOT* com> wrote:

>Something like:
>
>=INDEX(A2:A10,MATCH(MIN(IF(C2:C10="Male",M2:M10)),M2:M10,0))
>
>entered as an array formula with <Ctrl> <Shift> <Enter>.
>
>--
>
>Vasant

Thanks..I will play with that for a while.