Finding the Max of a related Value Lookup

[Guest]

Hi Folks,
I have been struggling with this for a bit now.
I have items available for purchase in E8:e66 (using list validation).
I have item written in $r8.
I have the actual cost paid for the item for that particular purchase in
h8:h66.
an Item can be purchased multiple times at different prices.

What I am trying to do is create a formula that will lookup all amounts paid
for the specific item type and return the maximum cost paid for that item.
My current formula version only reports back the first one found rather
than the maximum of all types of that item purchased.
=IF(W9=0,"None",MAX(VLOOKUP($R9,$E$8:$H$66,4,FALSE)))
where w9=amount of units purchased.(error removal)
Its really buggung me because its a simple result but a pain to generate.

 

[Max]

One way ..

Put in say, the formula bar for X9,
then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=IF(W9=0,"None",MAX(IF($E$8:$E$66=R9,$H$8:$H$66)))

X9 returns the max from col H for the item in R9

Copy X9 down

Adapt the ranges to suit ..

 

[Guest]

Cheers did the trick nicely.
Thanks

One way ..

Put in say, the formula bar for X9,
then array-enter the formula by pressing CTRL+SHIFT+ENTER
(instead of just pressing ENTER):

=IF(W9=0,"None",MAX(IF($E$8:$E$66=R9,$H$8:$H$66)))

X9 returns the max from col H for the item in R9

Copy X9 down

Adapt the ranges to suit ..

 

[Max]

Glad it worked !
Thanks for the feedback ..

 

[Guest]

Hi,

Assume your sheet is set up like this in range A1:B7.

Ashish 100
Sanjay 200
Pongal 300
Ashish 400
Rajesh 500
Suresh 600
Ashish 700

Ashish (in cell A10)

In cell B10, type the following array formula (Ctrl+shift+Enter)

=IF(ISERROR(INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2)),"",INDEX($A$1:$B$7,SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1)),2))

Copy this formula down.

you will now get all values which appear against Ashish. Now you can use
the max function in a seperate cell.

Please suit the example to your needs.

Regards,