Find the Max and return value from other column

[jeffreybrown]

I am drawing a complete blank here. I need to look down column A, find a name, look in column C, find the max, and then return the value in column B.

I have so many formulas running through my mind, but just not sure who to combine them.

I can find the max

CSE
=MAX(IF(Sheet1!$A$2:$A$19=F2,Sheet1!$C$2:$C$19))

But when I combined it with the index and match it returns the wrong values because a number could be repeated so the reference returned is not neccesarily for the appropriate name.

Any thoughts?

[DonkeyOte]

Jeff, you could use an Array

Please Login or Register  to view this content.

You could also achieve the same using a variety of INDEXes etc and/or an OFFSET:

Please Login or Register  to view this content.

but the Array is perhaps simplest (and more robust)

[jeffreybrown]

Thanks Luke. Works great