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.

--
Wigi
http://www.wimgielis.be = Excel/VBA, soccer and music


"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

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

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

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

Pete

On Aug 7, 10:34*pm, "T. Valko" wrote:
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- Hide quoted text -

- Show quoted text -

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


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

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

"T. Valko" wrote:

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


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

You're welcome. Thanks for the feedback!

--
Biff
Microsoft Excel MVP


"SteveC" wrote in message
...
Thanks for all your input; this is the formula I ended up using. It works
great!

"T. Valko" wrote:

Try this array formula** :

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

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

--
Biff
Microsoft Excel MVP


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

"None Array" formula. Just press ENTER

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


"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

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

--
Biff
Microsoft Excel MVP


"Teethless mama" wrote in message
...
"None Array" formula. Just press ENTER

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


"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