That could return the incorrect result if there is more than 1 instance of
MAX(IF(A1:A6=D2 for another type of fruit.

--
Biff
Microsoft Excel MVP


"Pete_UK" wrote in message
...
Put this array* formula in E2:

=INDEX(B1:B6,MATCH(MAX(IF(A1:A6=D2,C1:C6)),C1:C6,0 ))

*Array formulae have to be committed using the key combination of Ctrl-
Shift-Enter (CSE) instead of the usual <Enter. If you do this
correctly then Excel will wrap curly braces around the formula when
viewed in the formula bar - you must not type this yourself. If you
edit/amend the formula then you must use CSE again to commit it.

Hope this helps.

Pete


On Aug 7, 9:58 pm, SteveC wrote:
A variation of a multiple criteria lookup question, this time using the
"max"
function:

Column A Column B Column C
Apples text1 1
Apples text23 2
Apples text52 20
Apples etc 14
Oranges 2
Oranges 23

Cell D2 = Apples

What formula in cell E2 will find the maximum value Column C for all
Apples,
and return the associated cell in Column B (in this case, "text52")

thanks a lot!

Stevec