=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" wrote in message
...
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.