Find last occurrence in one column return value from another

Larry Bruso

I am having difficulties trying to do something that I am able to do in Excel. I want to find the last instance of a value in one column and return the value from another column in the same row if there is a value in that column. Here is an example:

Name Number

Peter 4

Paul 6

Mark 7

Peter 15

Peter 10

Paul

Peter

Peter

I want to be able to find the last instance of Peter in the Name column and pull the number 10 from the Number column.

My sheet is much larger than this with many instances of the same value but am using a much simpler example and would be unsorted.

The data will vary in size as it will be updated every month with new entries.

Also the last occurrence could be higher or lower value than the others so a max or min would not work and there would be empty values in the Number column.

I am no Excel expert and am new to Smartsheets but in Excel I would use an array formula.

Any help would be appreciated.

Genevieve P.

Hi Larry,

When you say the "last" entry, do you mean in terms of place in the sheet or based on time created?

If you're meaning time-wise (so last entered), then MAX would actually be the right formula... but instead of looking at the max number you would want to include a Created (Date) System Generated column.

Then you can use MAX(COLLECT, in a helper checkbox column, to check the box in that row if it's the MAX date for that specific person. (Reason being, I'm presuming you're trying to do a cross-sheet formula, is that correct?)

The MAX(COLLECT would look something like this:

=IF([Created Date]@row = MAX(COLLECT([Created Date]:[Created Date], [Name Column]:[Name Column], [Name Column]@row)), 1, 0)

Then in your cross-sheet formula, you can look for the name & if the box is checked, and return the Number column value. I would personally use a JOIN(COLLECT formula for this:

=JOIN(COLLECT([Number Column]:[Number Column], [Helper Checkbox]:[Helper Checkbox], 1, [Name Column]:[Name Column], "Name"))

This presumes there would only be one instance where the checkbox is checked, associated with that name. If there happen to be two entries on the exact same date and time with the same MAX value, then the JOIN(COLLECT would JOIN together those two values, so it depends on your sheet and the entries.

Would this work for you? If not, could you maybe clarify how the information is being input, and what you mean by "last"?

Cheers!

Genevieve