Index and Match function is not working
Answers
-
Hi Jeff,
I copied and pasted the formula its prompting with the error #UNPARSEABLE
=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @Cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")),1)
ADMIN ONLY Intake Master Range 1 - Project Name
ADMIN ONLY Intake Master Range 3 - Project ID
ADMIN ONLY Intake Master Range 5 - Ideation Phase
ADMIN ONLY Intake Master Range 4 - Project Phase
Note: I even tried adding IFERROR and got same error #UNPARSEABLE
=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @Cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")),1)," ")
Thank you,
Hanuman Veda
-
@Hanuman Veda I recommend studying some basics of formula writing in Smartsheet. That way you can figure out some of the obvious issues when troubleshooting.
In this case, there's an extra end parentheses that I put in - an easy mistake to make when not writing the formula directly in Smartsheet, where you have the system color-coding them for you. This helps you figure out the placement, if you're missing one or have an extra one. I also recommend a space after every comma.
I bet that in this formula, your parentheses are the following colors:
From left: blue, pink, green, green, pink, blue, black
Each color should have a matching opposite parentheses. In this case, you have a black close parentheses at the end with no black open parentheses. So there's an extra one in there. It's right here:
That extra one is closing off the INDEX function too early, causing the unparseable error. Remove one of the two and the colors will all line up, with just blue, pink, green, green, pink, blue. Also, add a space after the comma at
"Not Started"), 1)
=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @Cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1)
Here's my test version of your formula working fine:
My first two rows meet the criteria, the third does not.
Also, notice each open parentheses has a matching close parentheses.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
I copied and paste the below formula but still prompting with same error.
=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started")), 1)
-
@Hanuman Veda Details matter with formulas. A syntax mistake like an extra parentheses will mess the whole thing up. I walked you through it in my last post! You left the extra parentheses in there, the very one I indicated need to be removed.
I'm trying to both give you the formula that will work and show you how the formula works and how to troubleshoot. I don't know how else to help you. Select the text of the formula below, then hit Ctrl-C, and then Ctrl-V over your existing formula . Try one more time:
=INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @Cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1)
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Thank you for the support !!
I just added IFERROR at the start of the formula and ,"") at the end. Now the formula is working with the expected results. Thank you a lot.
=IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, OR(@cell = "New", @cell = "Triage", @cell = "Portfolio Review"), {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), "")
Thank you ,
Hanuman Veda
-
Hi Jeff,
Can we adjust the formula for this view -that would help reduce the white-space showing on the page and better visibility on the screen. ( I mean we need to see only the projects which meets the criteria formula in sequence).
Formula: =IFERROR(INDEX(COLLECT({ADMIN ONLY Intake Master Range 1}, {ADMIN ONLY Intake Master Range 3}, [Project ID]@row, {ADMIN ONLY Intake Master Range 5}, "Approved", {ADMIN ONLY Intake Master Range 6}, "Approved", {ADMIN ONLY Intake Master Range 4}, "Not Started"), 1), "")
Expecting this view
Thank You,
Hanuman Veda
-
@Hanuman Veda Are you trying to group all the row's values together in one cell? I can't really tell what you want there.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Please is it possible to setup a quick meeting? It will be easy to explain and to fix my issue.
Thank you,
Hanuman Veda
-
I'm sorry, I work in an office, with other duties. It would not be appropriate.
There are plenty of help articles, community posts, etc. In fact, moving this new issue to a new question so that others see it would be the best bet. Just be as descriptive as you about what you're looking to do.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Hi Jeff,
Am sharing the source file in excel format. We have overall 225 projects out of which I need only those projects names to be populated which meets the criteria.
- We have 14 categories, under Primary Business Product Portfolio and 58 Execution projects under Project Phase Column in source file.
- Only these 58 projects names should be populated under Project Phase Execution column from Source file in Report View. And if I select Clinical System Operations from dropdown it should populate only those project names which are associated with Clinical System Operations in the next column (Project Phase Execution) out of 58 Projects and similarly, if I select other Primary Business Product Portfolio from dropdown it should show the projects associated with that category.
Thank you,
Thank you,
Hanuman Veda
-
@Hanuman Veda I suggest opening a new discussion thread for this question. It seems quite complex and I just don't have the time to dig into it. Too much to do at my actual job.
Regards,
Jeff Reisman
Link: Smartsheet Functions Help Pages Link: Smartsheet Formula Error Messages
If my answer helped solve your issue, please mark it as accepted so that other users can find it later. Thanks!
-
Okay Jeff
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 63K Get Help
- 380 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
- 305 Events
- 34 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!