Look up Help

[Axim5]

Hi,


First of all many congratulations on this excellent MS Excel resource for excel users regardless of their level of expertise in Excel. Before joining the forum today, I visit the main site on regular basis and I have learnt quite a few things.


Today, however I have an Excel issue of my own for which I would very much appreciate a solution.


Issue: I have three columns in a source worksheet

Column A: it contains client ID's (all numeric numbers)

Column B: purchase dates

Column C: dollar amounts


In the above source file, under Column A some of the clients do show up more than once because they have more than one purchase date.


Solution Seeking:

What I am trying to accomplish (which may sound very simple)is to (on a separate sheet) a list of unique client list (which is not an issue at all) to show the date of purchase corresponding to the highest dollar amount from Column C. In addition to this I would also like the formula to be flexible enough to pick up the latest date if the dollar amounts for a client are identical.


So far, in order to prevent holding up the work done I sorted the data (Column A -C) based on the dollar value from highest to lowest and utilized the VLOOKUP function to get the date. However I am trying to built a formula where I do not have to use the sort function.


I am guessing that Excel functions INDEX and MATCH can be deployed to get the desired result but unfortunately I am unable to come up with the correct formula syntax.


All and any assistance is greatly appreciated.


Axim5

 

[keymaster]

As usual, we resort to the trusty sumproduct..


Assuming the IDs are in A1:A100, dates in B and dollar amounts in C...

the formula for highest $ amount is:

=SUMPRODUCT(MAX((--($a$1:$a$100)="ID")*($C$1:$C$100)))


and for latest date is: =SUMPRODUCT(MAX((--($a$1:$a$100)="ID")*($B$1:$B$100)))

 

[Axim5]

Thanks Chandoo,


The solution you have provided is good, as it identifies the either the later date or the highest amount. However, may be my explanation of the issue was not very clear, what I am actually looking for is to identify the latest date associated with the largest/maximum dollar amount to a specific client ID. So for example:


Client ID Date Amount

123 July 4, 2009 1,000

123 August 25, 2009 2,000

456 June 5, 2009 4,000


So when I type client ID 123 the formula should be able to give me August 25, 2009 as the dollar amount(5,000)is the largest for client ID 123.


So I am not just looking for the largest amount or the latest date for a specific client ID.


Is there any way we can combine the two formulas you have shown into one, where the resulting date corresponds to the highest dollar amount for a specific client?


Axim5

 

[keymaster]

okay.. that is just few more conditions on the sumproduct

=SUMPRODUCT(MAX(($C$1:$C$100=max(($C$1:$C$100)*(--($a$1:$a$100)="ID")))*($B$1:$B$100)))

I havent tested it.. but I guess it should work. This gives you the latest date on the largest amount...


You can extend this formula to all IDs by simply changing the ="ID" part and dragging it over range