Combine VLookup and MAX ?

[Ayato]

Hi @ll,

I need a formula (or VBA code) that could help me to extract the MA
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,
Ayat

 

[Cecilkumara Fernando]

Ayato,
assumed row1 has headers
Using Advanced filter on Vendors column only get a unique list of Vendors to
column.
say columnD so as your example D2 is a,D3 is b,D4 is c,
and in E2 put this array formula (Entered while Ctrl and Shift keys are
being held down)
=MAX((($A$2:$A$8)=D2)*($B$2:$B$8))
and fill down.
HTH
Cecil

 

[Bob Phillips]

Hi Ayato,

Try this formula

=MAX((B1:B10)*(A1:A10="b"))

it's an array formula, so enter with Ctrl-Shift-Enter

--

HTH

Bob Phillips
... looking out across Poole Harbour to the Purbecks
(remove nothere from the email address if mailing direct)

 

[Ayato]

Hi @ll,

Thx for the advise I will give a try,
Of course the idea is to insert a value in a cell (or create
selection out of a list) and in the cell next to it insert the Ma
value corresponding... that's why I initially talked abou
vlookup+max.

regards,
Ayat

 

[Dianne]

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)