Find FedEx Number with the Latest Date

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!

Genevieve P.

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

Genevieve P.

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

That did it! Thank you!!!