Find FedEx Number with the Latest Date

Options
cabbsman
cabbsman ✭✭✭✭

I have duplicate DEVICE NAMEs with associated FedEx numbers and different RECEIVED DATEs in the target Sheet above.

I have another sheet (my Source Sheet) that has a list of corresponding Device names that I need to match against the Device Name in the Target Sheet. If a match, find the latest RECEIVED DATE associated with the DEVICE NAME. Once determined, display the FedEx Number.

I think the following formula is moving towards the right direction:

=MAX(COLLECT({Fed Ex Number}, {Device Name}, [Device Name]@row)) where [Device Name]@row is a column in the source sheet that is needed to lookup the DEVICE NAMES on the Target Sheet.

But it is returning a "0" and does not account for the Latest Date. So, I'm hoping someone has a recommendation on how to move forward.

Many thanks!

Tags:

Best Answer

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hey @cabbsman

    You're very close! πŸ™‚

    The MAX needs to find the MAX date, but with the way your current Collect is set up, it's trying to find the Max Fed Ex number instead.

    Try something like this:

    =INDEX(COLLECT({Fed Ex Number}, {Device Name}, [Device Name]@row, {RECEIVED DATE}, MAX(COLLECT({RECEIVED DATE}, {Device Name}, [Device Name]@row))), 1)


    If you get "INVALID VALUE" then it's likely that there's no match. You could add an IFERROR in the front to change that:

    =IFERROR(INDEX(COLLECT({Fed Ex Number}, {Device Name}, [Device Name]@row, {RECEIVED DATE}, MAX(COLLECT({RECEIVED DATE}, {Device Name}, [Device Name]@row))), 1), "No Match")

    Cheers,

    Genevieve

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Answer βœ“
    Options

    Hey @cabbsman

    You're very close! πŸ™‚

    The MAX needs to find the MAX date, but with the way your current Collect is set up, it's trying to find the Max Fed Ex number instead.

    Try something like this:

    =INDEX(COLLECT({Fed Ex Number}, {Device Name}, [Device Name]@row, {RECEIVED DATE}, MAX(COLLECT({RECEIVED DATE}, {Device Name}, [Device Name]@row))), 1)


    If you get "INVALID VALUE" then it's likely that there's no match. You could add an IFERROR in the front to change that:

    =IFERROR(INDEX(COLLECT({Fed Ex Number}, {Device Name}, [Device Name]@row, {RECEIVED DATE}, MAX(COLLECT({RECEIVED DATE}, {Device Name}, [Device Name]@row))), 1), "No Match")

    Cheers,

    Genevieve

  • cabbsman
    cabbsman ✭✭✭✭
    Options

    That did it! Thank you!!!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!