Finding the second to last in a collection


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.




Best Answer


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!