vlookup for maximum value

[Guest]

Hi,

i have a table with two columns: in column A are five different companies
and in column B are the numbers of outgoing invoices. i would like to find -
maybe by using vlookup function - the highest outgoing invoice nzmber at one
selected company. are there some kind of combination of formulas to solve
this problem?

thx in advance!

 

[Guest]

Go to Data--Subtotals and subtotal by PO number and select the MAX function
in the dialogue box. That should show you the largest numbered PO for each
company.

Just make sure your data is formatted as a table. See here for more info:
http://office.microsoft.com/en-us/assistance/HA011097981033.aspx

Dave

 

[Guest]

many thanks! works great!
unfortunately i always try the harder way by myself:-( i made a macro which
ask for the company at start and then sort the invoices. now i can delete it


thx again!

 

[Guest]

Well you could use a macro to do what you describe, but why bother when Excel
has the SUBTOTAL function....

 

[Guest]

Try this

=SUMPRODUCT(MAX((A2:A100="company name")*(B2:B100)))