MAX Function

[LadyDi]

Is there a way to use the MAX function based on conditional data? I have two spreadsheets. One contains all orders entered for a certain time period and the other spreadsheet contains a list of customers. I want to enter a formula that will look at the customer name and then return the highest order number for that customer. Is there a way to do that?

 

[CraigDolphin]

Have you tried using the Vlookup worksheet function? This should be able to do a basic lookup like you describe as long as the information is correctly sorted and organized.

 

[LadyDi]

What do you mean "VLOOKUP worksheet" function? I have used VLOOKUP many times on a one for one basis. I need this to look at multiple values and pull the highest one. How would you write this formula?

 

[CraigDolphin]

Assuming that Sheet2 has two fields: Company_Name and Order_Number
and Sheet1 has Company_name in column A: then

I would simply sort Sheet2 by company name/id (Asc) and then by Order_number (Desc).

In Sheet1, I'd simply use vlookup to return the value in the Order_number field of the other Sheet2, where you have an exact match for the company name.

e.g., Sheet1 Cell B2
=Vlookup(A2,Sheet2!A$1:B$200,2,True)

Obviously, you'd need to tweak the number of rows in your table and whatever other cell references etc to match your spreadsheet

 

[CraigDolphin]

Or alternatively, just use a pivot table...

 

[lemo]

or - DMAX can work, although it's not particularly flexible, i.e. probably better for looking at customers' max one by one..
i second pivot table..
l