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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
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
Need more help? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
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
- 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!