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


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


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!