Returning multiple INDEX/MATCH or COLLECT on separate rows
I have a sheet (call it "Master Sheet") with a column called "Item ID". The "Item ID" is a unique system generated field. This sheet has an other column "Status", Status is a single select drop down column. For sake of argument lets say one of the values is "TRUE". I would like to create a separate sheet (call it "Reporting Sheet") using cross sheet references that shows ALL Items IDs where Status is TRUE on the Master Sheet. Much like you would do with a report, only I cannot use a report for certain reasons.
There has got to be a way I am missing to do this with a combination of INDEX, MATCH, and/or COLLECT functions. I know it could not be a column formula, but something that increments row by row, and I am fine with that. Something such as in row 1, perform the index match and return the first instance you find, in row 2 perform the index match and return the second instance you find, etc. The number of cases where this match would occur is limited and I am fine with IFERRORing out anything where there is no match.
Thanks
Best Answer
-
You first need a text/number column (called "Number" in this example) that has the numbers one through whatever manually entered. You will need to enter as many numbers as you think you will need, and I always suggest a little bit of a buffer.
Then you would use
=IFERROR(INDEX(COLLECT({Column To Pull Over}, {Status Column}, @cell = "status of choice"), Number@row), "")
Answers
-
As an addendum - I want each returned value in a separate row. I realize I could do it all in one cell using JOIN(COLLECT
I guess if I had to I could collect them all in one giant cell and then do some crazy parsing of the info into multiple cell, as each Item ID is of the form PSM####, so I could use LEFT or RIGHT functions to break each one out, but that is a really inelegant solution.
-
You first need a text/number column (called "Number" in this example) that has the numbers one through whatever manually entered. You will need to enter as many numbers as you think you will need, and I always suggest a little bit of a buffer.
Then you would use
=IFERROR(INDEX(COLLECT({Column To Pull Over}, {Status Column}, @cell = "status of choice"), Number@row), "")
-
Thank you. The @cell for the "status of choice" was the part that was missing in my head.
-
Happy to help. 👍️
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 64.4K Get Help
- 424 Global Discussions
- 221 Industry Talk
- 463 Announcements
- 4.8K Ideas & Feature Requests
- 143 Brandfolder
- 144 Just for fun
- 59 Community Job Board
- 463 Show & Tell
- 32 Member Spotlight
- 1 SmartStories
- 300 Events
- 39 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!