Index and Match function is not working

2»

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

  • Jeff Reisman
    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.

    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)


  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭
    Answer ✓

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

  • Hanuman Veda
    Hanuman Veda ✭✭
    Answer ✓

    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

  • Jeff Reisman
    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.

    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

  • Jeff Reisman
    Jeff Reisman ✭✭✭✭✭✭

    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.

    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,

    Thank you,

    Hanuman Veda

  • Jeff Reisman
    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.

    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!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!