How to determine the value?

[Eric]

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

 

[Max]

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

 

[Pete_UK]

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

 

[Max]

Dismiss the earlier, overkill
Go with Pete's suggestion

---

 

[Pete_UK]

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

Pete

 

[Teethless mama]

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