Does anyone have any suggestions on how to determine the value with condition?
Under A column, there is a list of types.
Under B column, there is a list of numbers.
For example
Apple 10
Orange 15
Banana 21
Apple 4
Banana 3
Orange 7
Orange 9
Apple 11

I would like to determine the maximum number under Apple type using index
and match function.
Does anyone have any suggestions?
Thank you very much
Eric

With lookup values listed in D1 down, eg: Apples

In E1, array-entered*:
=INDEX($B$1:$B$100,MATCH(MAX(IF($A$1:$A$100=D1,$B$ 1:$B$100)),IF($A$1:$A$100=D1,$B$1:$B$100),0))
Copy down. Adapt the ranges to suit.

*Press CTRL+SHIFT+ENTER to confirm the formula,
instead of just pressing ENTER
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---
"Eric" wrote:

Does anyone have any suggestions on how to determine the value with condition?
Under A column, there is a list of types.
Under B column, there is a list of numbers.
For example
Apple 10
Orange 15
Banana 21
Apple 4
Banana 3
Orange 7
Orange 9
Apple 11

I would like to determine the maximum number under Apple type using index
and match function.
Does anyone have any suggestions?
Thank you very much
Eric

Dismiss the earlier, overkill
Go with Pete's suggestion
--
Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

I thought I had misread what the OP asked for when I saw your formula,
Max !! <bg

Pete

On Mar 18, 2:13*am, "Max" wrote:
Dismiss the earlier, overkill
Go with Pete's suggestion
--
Max
Singaporehttp://savefile.com/projects/236895
xdemechanik
--- *

Why do you want to use INDEX and MATCH? Try this array* formula
instead:

=MAX(IF(A1:A10="Apple",B1:B10))

* Array formulae have to be committed using 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 - do no type these yourself. If you edit the formula, then commit
with CSE again.

Hope this helps.

Pete

On Mar 18, 1:46*am, Eric wrote:
Does anyone have any suggestions on how to determine the value with condition?
Under A column, there is a list of types.
Under B column, there is a list of numbers.
For example
Apple 10
Orange 15
Banana 21
Apple 4
Banana 3
Orange 7
Orange 9
Apple 11

I would like to determine the maximum number under Apple type using index
and match function.
Does anyone have any suggestions?
Thank you very much
Eric

Try one of these:

=SUMPRODUCT(MAX((A1:A10="Apple")*(B1:B10)))
or
=MAX(INDEX((A1:A10="Apple")*(B1:B10),0))

These formulae don't required ctrl+shift+enter, just press enter


"Eric" wrote:

Does anyone have any suggestions on how to determine the value with condition?
Under A column, there is a list of types.
Under B column, there is a list of numbers.
For example
Apple 10
Orange 15
Banana 21
Apple 4
Banana 3
Orange 7
Orange 9
Apple 11

I would like to determine the maximum number under Apple type using index
and match function.
Does anyone have any suggestions?
Thank you very much
Eric