Combine VLookup and MAX ?

  • Thread starter Thread starter Ayato
  • Start date Start date
A

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
 
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
 
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)
 
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
 
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)
 
Back
Top