Need last date vice first date returned in the formula

  • [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]

  • Re: Need last date vice first date returned in the formula


    try using
    =MAX(IF(A2=CM!B2:B19, CM!E2:E19))
    and enter as an array using


    Control+shift+enter
    so that {} brackets are around the formula


    see attached


    if the account cannot be found then it will return a zero (or the date for zero)


    =IF(MAX(IF(A2=CM!B2:B19, CM!E2:E19))=0,"No Sale",MAX(IF(A2=CM!B2:B19, CM!E2:E19)))

Participate now!

Don’t have an account yet? Register yourself now and be a part of our community!