ExcelBanter - View Single Post - Finding the Max of a related Value Lookup

Cheers did the trick nicely.
Thanks

"Max" wrote:

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 ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--
"Cav" wrote in message
...
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.