Index(Collect with multiple criteria

I'm trying to make a formula in a sheet summary that checks if the "duration" column is not blank and then uses all rows that arent blank as a range to then find the row with the newest "created" date and return a value from a different "IndexKey" column. I'm not sure if i need to use an IF staement to limit the range or not but this is what i have =INDEX(COLLECT(IndexKey@row, Created:Created, MAX(Created:Created), [Duration (weeks)]:[Duration (weeks)], <>""),1) but i'm getting UNPARSEABLE

Tags:

Best Answer

  • ross chambers
    ross chambers ✭✭✭✭✭
    Answer ✓

    =VLOOKUP(LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1), Created:[Priority Rating], 5, false) this seems to be working i had just forgotten the false part so it was giving me a #no match when i changed values in the duration column

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you able to provide screenshots for context?

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ross chambers
    ross chambers ✭✭✭✭✭

    I've moved the duration column over to capture it in screenshot

    but basically I want to find the newest date when the duration column isn't blank so I now have =LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1) and that works but when I add that part to an index or vlookup I get #invalid data. I want to have multiple of this formula that just return a different column instead of Indexkey

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you trying to pull in the INDEX Key that has the largest date? I'm not sure I understand exactly what you are trying to accomplish.

    Come see me in Seattle at ENGAGE 2024! I will be at the 10xViz partner booth throughout as well as the "Lets Talk About Community" Meet & Eat table on Tuesday from 11:45am - 12:45pm!

    CERTIFIED SMARTSHEET PLATINUM PARTNER

    10xViz.com

  • ross chambers
    ross chambers ✭✭✭✭✭

    i want the index key for the row most recently created and has a number in the duration column

  • ross chambers
    ross chambers ✭✭✭✭✭
    Answer ✓

    =VLOOKUP(LARGE(COLLECT(Created:Created, [Duration (weeks)]:[Duration (weeks)], <>""), 1), Created:[Priority Rating], 5, false) this seems to be working i had just forgotten the false part so it was giving me a #no match when i changed values in the duration column

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!