Help with Join collect multi criteria error

I need help with my formula and am not getting anywhere and keep receiving an error in the test. The formula should find the cell needed in another sheet (cross sheet reference) that has the matching ID at row (and ID row of the 2nd Sheet) but also has either "Year End Planning" or " Year- End Planning" in a separate column on the 2nd sheet. My current formula working only gives me the cells when it has
"Year End Planning" but does not give me anything if it has "Year-End Planning" the details needed of this:

=IFERROR(INDEX(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, "Year End Planning"), 1), " ")

(Also send sheet has data uploaded through automation daily so I cannot change the Year-End to Year End as a solution.)

This is the closest I have gotten to the solution but still getting an error message.

=IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, [Client Number]@row, {Sheet - QER Client ID FX}, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")

or

=IFERROR(JOIN(COLLECT

({Sheet - QER Assembly}, [Client Number]@row, {Sheet - QER Client ID FX}, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning"))), " ")

Thanks in advance to anyone that can offer some help or guidance.

Best Answer

  • DKazatsky2
    DKazatsky2 Community Champion
    Answer ✓

    Hi @NCookBT83,

    I think your syntax is off. It is correct in the formula you say is working, but when you added the second criteria, it looks like you reversed things in the COLLECT portion.

    See if this works.

    =IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")

    *Untested

    Hope this helps,

    Dave

Answers

  • DKazatsky2
    DKazatsky2 Community Champion
    Answer ✓

    Hi @NCookBT83,

    I think your syntax is off. It is correct in the formula you say is working, but when you added the second criteria, it looks like you reversed things in the COLLECT portion.

    See if this works.

    =IFERROR(JOIN(COLLECT({Sheet - QER Assembly}, {Sheet - QER Client ID FX}, [Client Number]@row, {Sheet - QER Workflow Des}, OR(@cell = "Year End Planning", @cell = "Year-End Planning")), ", "), " ")

    *Untested

    Hope this helps,

    Dave

  • NCookBT83
    NCookBT83 ✭✭✭

    That worked!!! Thank you so much for catching that!

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!