Multiple criteria with INDEX and MATCH / COLLECT with MAX Functions
Hi,
I have the below columns in my Smartsheet and I would like to retrieve Finance column value if Quarter=2, Year=2021, Req Type=L and Max(Row ID).
I tried with below query but failed and I am not able to see how MATCH / COLLECT Function can be used. Any help would be greatly appreciated.
Thanks in advance,
~Chitta
Best Answer
-
Small change in my query and it worked!
Working query:
=INDEX([Finance]:[Finance],MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)
Answers
-
I tried below 2 queries, one works with returning row id and other one fails with "INCORRECT ARGUMENT SET" error:, I needed 2nd query to be used to fetch the Finance value.
Below query works with MAX Row ID returns but I want to store Finance field value in my summary sheet.
=MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021))
Below query fails with "INCORRECT ARGUMENT SET" error
=INDEX(MAX(COLLECT([Finance]:[Finance], [Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)
~Chitta
-
Small change in my query and it worked!
Working query:
=INDEX([Finance]:[Finance],MAX(COLLECT([Row ID]:[Row ID], [Req Type]:[Req Type], "L", Quarter:Quarter, 2, Year:Year, 2021)), 1)
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63.5K Get Help
- 402 Global Discussions
- 213 Industry Talk
- 450 Announcements
- 4.7K Ideas & Feature Requests
- 141 Brandfolder
- 135 Just for fun
- 56 Community Job Board
- 454 Show & Tell
- 31 Member Spotlight
- 1 SmartStories
- 296 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!