[INDENT]Formula help need:
In one sheet (CM) I have a list of accounts (range B2:B297) which have numbers assigned and the next row over I have the date the account had a transaction (E2:E297). In another sheet I have the account number (a2) and want to pull the last transaction date found in the first sheet (cm) matching the account number (a2) in the second sheet in this column place the most recent date, highest date, last date or whatever might be the term for the last date.
The formula :
=iferror(index(cm!$e$2:$e$297),match(a2,CM!$b$2:$B$297,0),1),"No Sale")
Returns me a No Sale if there is no transaction but it will return the "FIRST" date found.
If I could modify this formula to return the "LAST" date then that would work for what I need - the last time the customer had a transaction.
If you use the sheet names and ranges from this formula you will see how and where I am pulling data. It is just I can't return the "MAX" value but rather it stops at the first instance. Customers come back over the years and i would like to know when they came in last to direct resources appropriately.
[/INDENT]