Find Max and Min based on cell reference
 

I want to have a formula that finds the maximum value in a list. Easy enough
but I want to add a condition - the cell to the left has to equal a cell
reference.
So for example:
If I enter the number 4 in a cell I want the formula to lookup the list
(column B) and find all listings that have 4....then find the max value in
column A.
Its kind of like a maxif but I don't know if there is such a function?

GK

=MAX(IF($B$2:$B$100=E4,$A$2:$A$100))

which must be confirmed with control+shift+enter instead of just with enter.

E2 houses a criterion value like 4 that must hold for the range in B.

gregork wrote:
I want to have a formula that finds the maximum value in a list. Easy enough
but I want to add a condition - the cell to the left has to equal a cell
reference.
So for example:
If I enter the number 4 in a cell I want the formula to lookup the list
(column B) and find all listings that have 4....then find the max value in
column A.
Its kind of like a maxif but I don't know if there is such a function?

GK

And of course, we have the old "stand by" of *non-array* entered functions:

=SUMPRODUCT(MAX((B1:B100=D1)*A1:A100))

Where the number to lookup is entered in D1.
--
HTH,

RD

---------------------------------------------------------------------------
Please keep all correspondence within the NewsGroup, so all may benefit !
---------------------------------------------------------------------------
"gregork" wrote in message
...
I want to have a formula that finds the maximum value in a list. Easy
enough
but I want to add a condition - the cell to the left has to equal a cell
reference.
So for example:
If I enter the number 4 in a cell I want the formula to lookup the list
(column B) and find all listings that have 4....then find the max value in
column A.
Its kind of like a maxif but I don't know if there is such a function?

GK

Thanks Aladin.....exactly what I was after.

cheers
GK


"Aladin Akyurek" wrote in message
...
=MAX(IF($B$2:$B$100=E4,$A$2:$A$100))

which must be confirmed with control+shift+enter instead of just with
enter.

E2 houses a criterion value like 4 that must hold for the range in B.

gregork wrote:
I want to have a formula that finds the maximum value in a list. Easy
enough
but I want to add a condition - the cell to the left has to equal a cell
reference.
So for example:
If I enter the number 4 in a cell I want the formula to lookup the list
(column B) and find all listings that have 4....then find the max value
in
column A.
Its kind of like a maxif but I don't know if there is such a function?

GK