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
-
=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
-
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!
-
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
-
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!
-
i want the index key for the row most recently created and has a number in the duration column
-
=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
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!