You could create a pivot table without grand totals on another worksheet
that used Max of Sales as Data and Vendor as Row. Then create a dynamic
range (in this example called MyPivotTable) that refers to the pivot
table:

=OFFSET(Sheet4!$A$1,2,0,COUNTA(Sheet4!$A:$A)-2,2)

then do a VLOOKUP from your main spreadsheet:

=VLOOKUP(A2,MyPivotTable,2,0)

--
HTH,
Dianne

Hi @ll,

I need a formula (or VBA code) that could help me to extract the MAX
value for a specific identifer in a range.

For example:

I have vendors in column A and sales in column B

Vendor/Sales
a 50
a 53
a 90
b 110
b 36
a 100
c 500

I need to know the MAX for vendor a = 100, b = 110, c = 500

Please advise,
Ayato


---
Message posted from http://www.ExcelForum.com/