Using MAX with a LOOKUP

[Joe Gieder]

First thank you for looking at this
I have a list of part numbers that appear multiple times and I want to first
extract each unique part number and then use that value and find the maximum
price listed for it. I have tried this formula for the max price part
=MAX(OFFSET(A3,MATCH(A3,$A$3:$A$3500,0)-1,9,1,1))
but it doesn't give the right result, it seems to find the first match. And
I have no idea on the unique part number part.
Please help if possible
Thank you
Joe

 

[winnie123]

Hi,
Say Part no is ColA and Price is ColB
Data entered from row 2 onwards

Create a helper col in C of all the unique part numbers
You can create this by entering =INDEX(A2:A10,MATCH(0,-ISBLANK(A2:A10),0))
in C1

Then In C2 enter this array formula using Crtl,shift & enter

=IF(COUNT(MATCH(A$2:A$10,C$1:C1,0))<COUNT(1/(A$2:A$10<>"")),INDEX(A$2:A$10,MATCH(0,(A$2:A$10<>"")-ISNA(MATCH(A$2:A$10,C$1:C1,0)),0)),"")

Copy down to your last entry

Then in D2 enter array formula, Ctrl,shift & enter

=MAX(IF($A$2:$A$10=C2,$B$2:$B$10))

Copy down to your last unique part number

 

[Max]

Try a pivot. Get both results in a flash. If "PartNum" is the col header for
part numbers, "Val" the col header for the values, create a pivot on the
source data. In LAYOUT, drag n drop the PartNum into both ROW & DATA areas.
Set it to Count of PartNum in DATA. Drag n drop Val into DATA, set it to Max
of Val. Click to Finish. Hop over to the pivot sheet, just drag "Data" and
drop it over "Total" to re-arrange it into columns. Voila, there you go, all
the unique part numbers listed with their corresponding counts and maximums.
You're done in 15 sec. Success? hit the YES below.
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Ashish Mathur]

Hi,

First extract the unique values by using advanced filters (check the box for
advanced filters and leave the criteria blank). Now you can use the MAX()
finction

--
Regards,

Ashish Mathur
Microsoft Excel MVP
www.ashishmathur.com