Index and Match function is not working

Hanuman Veda

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

Jeff Reisman

@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.

Hanuman Veda

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)

Jeff Reisman

@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)

Hanuman Veda

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

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

Jeff Reisman

@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.

Hanuman Veda

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

Jeff Reisman

https://community.smartsheet.com/discussion/comment/368279#Comment_368279

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.

Hanuman Veda

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.

1. We have 14 categories, under Primary Business Product Portfolio and 58 Execution projects under Project Phase Column in source file.
2. 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,

Source File.xlsx

Report (Expected) View.xlsx

Thank you,

Hanuman Veda

Jeff Reisman

@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.

Hanuman Veda

Okay Jeff