# How to determine the value?

E

#### 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

M

#### 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,

P

#### Pete_UK

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

=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

M

#### Max

Dismiss the earlier, overkill
Go with Pete's suggestion

---

P

Max !! <bg>

Pete

T

#### 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