Combine VLookup and MAX ?
 

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

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

Combine VLookup and MAX ?
 

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

"Ayato " wrote in message
...
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/

Combine VLookup and MAX ?
 

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 " wrote in message
...
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/

Combine VLookup and MAX ?
 

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

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

Combine VLookup and MAX ?
 

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/