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?
-
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.
-
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
- Customer Resources
- 64.8K Get Help
- 434 Global Discussions
- 138 Industry Talk
- 470 Announcements
- 4.9K Ideas & Feature Requests
- 129 Brandfolder
- 148 Just for fun
- 65 Community Job Board
- 486 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 300 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!