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 information? 👀 | 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 information? 👀 | 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
- 66.7K Get Help
- 436 Global Discussions
- 152 Industry Talk
- 497 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 77 Community Job Board
- 508 Show & Tell
- 34 Member Spotlight
- 2 SmartStories
- 308 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!
Trending in Formulas and Functions
- I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
- Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
- I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …