MATCH / INDEX to return a value
Apologies what may be a basic question. I just finished training and tried to figure this out from the Formula Handbook, but struggling with the learning curve and understanding the equations.
From my snapshot above, I'm trying to search the "Milestone Status" Column for a specific value and return the "Task" name. In the snap shot above, the result should be "Discovery".
It looks like I need a combination of INDEX and MATCH, but keep getting an UNPARESABLE error.
Best Answer
-
Try this:
=INDEX(Tasks:Tasks, MATCH("In Progress", [Milestone Status]:[Milestone Status], 0))
Answers
-
I think you'll want to use join/collect here.
=JOIN(COLLECT({Task}, {Milestone Status}, "Specific Value Here"), CHAR(10))
You will want to put this in a separate sheet in it's own cell (not column formula unless you are using @row for the "Specific Value Here" and update the references. Then, change the cell to wrap text and it will populate all tasks which meet the criteria.
Sincerely,
Jacob Stey
-
SteyJ
Thanks; if nothing else, learned about the COLLECT function. I'm not wanting to join diff columns, tho.
I'm wanting to search the "Milestone Status" column for the row that has the value "In Progress" and return the value of what the "Task" column value is. The "In Process" will be assigned at the PARENT level. We're designing our process so we'll only have one PARENT row labeled "In Process".
Using Excel terminology, I want to do a VLOOKUP on the "Milestone Status" column, searching for the value "In Progress", and return the value found under the "Task" column for that row.
Finally, I want the formula to be in my Sheet Summary so that I can report on that value.
-
Try this:
=INDEX(Tasks:Tasks, MATCH("In Progress", [Milestone Status]:[Milestone Status], 0))
-
BINGO!!! Thanks Paul!
-
Happy to help. 👍️
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