"MAXIF" workaround

[Rayo K]

Hi,

I want to create a formula that would act as a "MAXIF" function. I have data
in rows and I want to find the max of field A for a given field B.

For example:
A B C
4356 4 John
2341 2 Bill
3425 4 Joe
8734 2 Sam

I also want to be able to call data from field C that's on the same row as
the maximum.

I want to show:
Max 2: 8734 Sam
Max 4: 4356 John

How can I do this? I've tried pivottables and can't seem to get the results
I want.

[Bob Phillips]

=MAX(IF(B2:B5=2,A2:A5))

and

=MAX(IF(B2:B5=2,C2:C5))

which is an array formula, it should be committed with Ctrl-Shift-Enter, not
just Enter.

--

HTH

Bob Phillips

(remove nothere from the email address if mailing direct)

"Rayo K" <[email protected]> wrote in message
news:[email protected]...
> Hi,
>
> I want to create a formula that would act as a "MAXIF" function. I have
data
> in rows and I want to find the max of field A for a given field B.
>
> For example:
> A B C
> 4356 4 John
> 2341 2 Bill
> 3425 4 Joe
> 8734 2 Sam
>
> I also want to be able to call data from field C that's on the same row as
> the maximum.
>
> I want to show:
> Max 2: 8734 Sam
> Max 4: 4356 John
>
> How can I do this? I've tried pivottables and can't seem to get the
results
> I want.
>

[daddylonglegs]

If you use this formula in E2

=MAX(IF(B2:B5=2,A2:A5))

confirmed with CTRL+SHIFT+ENTER

then to get the associated name use a VLOOKUP

=VLOOKUP(E2,A2:C5,3,0)