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
 
Ad

Advertisements

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,
instead of just pressing ENTER
 
P

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
 
P

Pete_UK

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

Pete
 
Ad

Advertisements

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
 
Ad

Advertisements


Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top