min in a subgroup

[SteveC]

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

 

[Wigi]

To find the 20, use a DMAX function.

To find the text52, use an INDEX formula together with a MATCH function.

 

[Pete_UK]

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

 

[T. Valko]

Try this array formula** :

=INDEX(B2:B7,MATCH(MAX(IF(A2:A7=D2,C2:C7)),IF(A2:A7=D2,C2:C7),0))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER)

 

[T. Valko]

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


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

 

[Teethless mama]

"None Array" formula. Just press ENTER

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

 

[T. Valko]

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

 

[Pete_UK]

Yes, thanks for pointing that out Biff, and I can see how you have got
around it in your formula.

Pete

 

[SteveC]

Thanks for all your input; this is the formula I ended up using. It works
great!

 

[T. Valko]

You're welcome. Thanks for the feedback!