Finding the Max of a related Value Lookup

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Cav - 23 Nov 2005 02:15 GMT

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.

Reply to this Message

Max - 23 Nov 2005 02:43 GMT

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
--

> Hi Folks,
> I have been struggling with this for a bit now.
[quoted text clipped - 11 lines]
> where w9=amount of units purchased.(error removal)
> Its really buggung me because its a simple result but a pain to generate.

Reply to this Message

Cav - 23 Nov 2005 02:59 GMT

Cheers did the trick nicely.
Thanks

> One way ..
>
[quoted text clipped - 34 lines]
> > where w9=amount of units purchased.(error removal)
> > Its really buggung me because its a simple result but a pain to generate.

Reply to this Message

Max - 23 Nov 2005 04:39 GMT

Glad it worked !
Thanks for the feedback ..
--
Rgds
Max
xl 97
---
Singapore, GMT+8
xdemechanik
http://savefile.com/projects/236895
--

> Cheers did the trick nicely.
> Thanks

Reply to this Message

Ashish Mathur - 23 Nov 2005 14:53 GMT

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,

Reply to this Message