"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)

 

[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