# Using MAX with a LOOKUP

J

#### 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.
Thank you
Joe

W

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

M

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

A

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