INDEX MATCH and COLLECT all at once
I have used INDEX({Array},MATCH(Value in Column),MATCH(Value in Row)) to retrieve a value by matching the column and row
I have used INDEX(COLLECT(Reference Column, Criteria 1, Criteria 2)) to collect
What I am trying to do now is retrieve a value from an array based on the values in two columns and one row
I have a sheet "Time Tracking Sheet" where people enter their time each week, the weeks are in columns and the name and function being tracked are in two separate rows.
I have an other sheet where I am trying to pull in this data "Collection Sheet"
I thought this would work. INDEX(COLLECT(All time tracking data, Primary Col, Collection Name, Function Col, Collection Function, Week, Collection Week),1)
However I keep getting a #INCORRECT ARGUMENT SET error
The real formula I am using is this
=INDEX(COLLECT({Time Tracking Range of Weeks}, {Time Tracking Name}, =PARENT($[Primary Column]@row), {Time Tracking Function}, =[Primary Column]@row, {Time Tracking Header Row}, [Column2]$1), 1)
However, even substituting hard coded values for the criteria, it still does not work
I guess if there is not a formulaic way to accomplish this, I could concatenate the name and function into a helper column and INDEX on that, but I would really like to avoid helper columns.
Thanks for any help :-)
Best Answer
-
Hey @David Dolch
The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).
That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.
=INDEX(Sheet Range, Row, Column)
To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.
=INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now
Answers
-
Hey @David Dolch
The way I would do this is to add that helper column and + the two values together (and hide them on the sheet).
That way you can use INDEX(MATCH(MATCH to get what you need, since the MATCH needs a single value to reference, as you've found.
=INDEX(Sheet Range, Row, Column)
To get the Row, we need the MATCH to be on its own. You can't use COLLECT within the Match because Collect will adjust the "row numbers" that match sees and you'll always return "1" instead of the sheet row number.
=INDEX({Time Tracking Range of Weeks}, MATCH(PARENT($[Primary Column]@row) + [Primary Column]@row, {Helper Column}, 0), MATCH([Column2]$1, {Time Tracking Header Row}, 0))
Cheers,
Genevieve
Join us at Smartsheet ENGAGE 2024 🎉
October 8 - 10, Seattle, WA | Register now -
Thanks Genevieve
That is what I was suspecting. I was hoping there was a way to accomplish this within the formula, as opposed to yet an other helper column, but it works with a helper column so it is not that bad.
-dave
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 212 Industry Talk
- 442 Announcements
- 4.6K Ideas & Feature Requests
- 140 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 302 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!