Finding the second to last in a collection

Mark Hibyan

Hello -

I'm using INDEX to find the value of [Scanned Location] that corresponds to the MAX [ROW#] of a collection that matches my current [Asset ID]. My formula is =INDEX([Scanned Location]:[Scanned Location], MAX(COLLECT([ROW#]:[ROW#], [Asset ID]:[Asset ID], [Asset ID]@row))).

Now what I want to do is find the second to last [Scanned Location] value of that same collection. Any ideas on the syntax here? So, if asset 13 has a collection of locations (A, D, B, Z), it would return "B". Right now, I can isolate "A" (using INDEX, 1), and "Z", using MAX. I'd like to isolate the second to last, but I can't use 3, since that collection could be 1 or many values.

Thanks,

Mark

Paul H

See my solution in this other post, you could use the same approach

https://community.smartsheet.com/discussion/comment/322868#Comment_322868

Paul H

See my solution in this other post, you could use the same approach

https://community.smartsheet.com/discussion/comment/322868#Comment_322868

Mark Hibyan

Thank you, use of helper columns here got me on the right track.