min in a subgroup

  • Thread starter Thread starter SteveC
  • Start date Start date
S

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
 
To find the 20, use a DMAX function.

To find the text52, use an INDEX formula together with a MATCH function.
 
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
 
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)
 
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
 
"None Array" formula. Just press ENTER

=INDEX(B1:B6,MATCH(MAX(INDEX((A1:A6=D2)*C1:C6,)),C1:C6,))
 
That could return an incorrect result if there is more than 1 instance of
MAX(IF(A1:A6=D2 for another type of fruit.
 
Yes, thanks for pointing that out Biff, and I can see how you have got
around it in your formula.

Pete
 
Back
Top