Formula to get the last value in a data range

Options

Is there a work around that will provide you with the last value in a data range instead of the first as vlookup would do? Also, we would need to ignore any cells that were blank.

Best Answer

Answers

  • David Joyeuse
    David Joyeuse ✭✭✭✭✭
    Options

    Hi @Michael Gibson

    Instead of VLOOKUP, try using MAX(COLLECT([Date to Collect Range}; {Date to collect range}; ISDATE(@cell)))

    Should do the trick.

  • Michael Gibson
    Options

    Hi @David Joyeuse ,

    Thanks for your feedback. I'm having trouble getting that to work. I'll post an example of a situation where I'm looking to get the current value of a contract which has had 4 modifications. In the example the value went up, was unchanged and left blank and then went down.

    Thanks Again.


  • MCorbin
    MCorbin Overachievers Alumni
    Options

    Try this:


    =INDEX([Contract Value]:[Contract Value], MATCH(MAX(COLLECT([Contract Mods]:[Contract Mods], [Contract List]:[Contract List], [Contract Number]@row)), [Contract Mods]:[Contract Mods], 0))

  • MCorbin
    MCorbin Overachievers Alumni
    Options

    Oops - there's a problem with my formula, but I need to go into a meeting. I'll work on fixing it when my meeting is done (sorry!)

  • Michael Gibson
    Options

    Thanks @MCorbin , that did the trick.

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!