How to determine the value?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Eric - 18 Mar 2008 02:46 GMT

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

Reply to this Message

Max - 18 Mar 2008 02:54 GMT

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

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

> Does anyone have any suggestions on how to determine the value with condition?
> Under A column, there is a list of types.
[quoted text clipped - 14 lines]
> Thank you very much
> Eric

Reply to this Message

Max - 18 Mar 2008 03:13 GMT

Dismiss the earlier, overkill
Go with Pete's suggestion

Signature

Max
Singapore
http://savefile.com/projects/236895
xdemechanik
---

Reply to this Message

Pete_UK - 18 Mar 2008 03:17 GMT

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

Pete

> Dismiss the earlier, overkill
> Go with Pete's suggestion
[quoted text clipped - 3 lines]
> xdemechanik
> ---

Reply to this Message

Pete_UK - 18 Mar 2008 02:55 GMT

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

> Does anyone have any suggestions on how to determine the value with condition?
> Under A column, there is a list of types.
[quoted text clipped - 14 lines]
> Thank you very much
> Eric

Reply to this Message

Teethless mama - 18 Mar 2008 05:07 GMT

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

> Does anyone have any suggestions on how to determine the value with condition?
> Under A column, there is a list of types.
[quoted text clipped - 14 lines]
> Thank you very much
> Eric

Reply to this Message