How can I index/match the latest information from my source sheet when 'match' data duplicates?

VLD

Hi,

Hopefully this make sense :)

I have two sheets. A source sheet and a master sheet. I would like the master sheet to pull the start date from the source sheet when it matches the client number. This works fine when I use INDEX MATCH if there is just one client number.

My issue is that the source sheet will contain a duplicate of the client number that is on the master sheet and I really only want the latest data to pull through. I tried with more than one of the same client numbers and it only pulls through the one on the top of the sheet which is an issue as it is usually not the most up to date and sorting will affect the output.

Example of what I want it to do is below, I am only interested in the coloured items pulling through.

Source Sheet:

Master Sheet:

Thank you in advance for any help :)

Itai

Hey @VLD,

I dont know of a way to search from the bottom up in Smartsheet but my solution is to sort the Client Number column in a decending order so then the Master sheet will always find the latest data when you use INDEX MATCH or VLOOKUP.

Hope that helps.

Paul Newcome

I would suggest a helper column on the source sheet that pulls the year into each of the child rows.

=PARENT([Client Number]@row)

A helper column on the master sheet will pull in the most recent year:

=MAX(COLLECT({Source Sheet Helper Column}, {Source Sheet Client # Column}, @cell = [Client Number.]@row))

Then an INDEX/COLLECT will pull in the appropriate date:

=INDEX(COLLECT({Source Sheet Date Column}, {Source Sheet Client # Column}, @cell = [Client Number.]@row, {Source Sheet Helper Column}, @cell = [Helper Column]@row), 1)

It could be even more simple depending on how you fill in the source data. Do you always fill it in in chronological order, or could you enter a client in 2023 and then later on enter that same client in the 2021 grouping? Will the 2021 grouping always be higher than the 2022 grouping?

VLD

Thanks for this and it is filled in in chronological order.

I do not want sorting a column to affect the master sheet. The master sheet should always pull through the latest date in the date column.

Paul Newcome

In that case you can disregard my last comment and only use a single helper column in the source sheet (and no helper columns in the master).

It would be an auto-number column with no special formatting that goes into the source sheet.

Then in the Master sheet you would use:

=IFERROR(INDEX({Source Sheet Date Column}, MATCH(MAX(COLLECT({Source Sheet Auto-Number Column}, {Source Sheet Client # Column}, @cell = [Client Number.]@row)), {Source Sheet Auto-Number Column}, 0)), "")

This allows for less helper columns and is "sort proof" assuming it is filled out chronologically.