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,


"Cav" wrote:

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.