Skip to content
  • Try Smartsheet

    • Formulas and Functions

    Formulas and Functions

    Ask for help with your formula and find examples of how others use formulas and functions to solve a similar problem.

    Nesting JOIN?, IF and INDEX(MATCH

    ✭
    in Formulas and Functions

    Hello!

    Longtime lurker, first time poster. Trying to create a formula that will cross-check data in a cell then check a series of IFs to pull certain data from another sheet into a comma-limited cell. I've managed to get individual IF statements to pull correctly from the other sheet, checking appropriate information. Here's that section:

    =IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0))

    However, I'm not sure how to achieve the joining of multiple cells for this. As in, I would like the formula to check if the ref cell has more than one Priority (there are 5), and return the ones it fines, like (Priority 1, Priority 3, Priority 5). I can make it work by use IF + IF, but it won't delimit (that I can figure out, anyway) and ends up a bash of words.

    Is this possible with JOIN? I tried using this:

    =JOIN(IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0)), IF(CONTAINS("Priority 2", [Priorities]@row), INDEX({INFO Priorities 2}, MATCH([Name]@row, {INFO Name}), 0)), ",")

    But it returned Incorrect Argument.

    Thank you in advance for any advice!

    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Answers

    • Community Champion

      So…. this works, but hopefully someone else will come along with a more elegant solution. The only way I know to make this work is to manually build up the joins instead of using the JOIN() function.

      =IF(CONTAINS("Priority 1", [Priorities]@row), INDEX({INFO Priorities 1}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row)), ", ",
      "") + IF(CONTAINS("Priority 2", [Priorities]@row), INDEX({INFO Priorities 2}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row)), CONTAINS("Priority 3", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 3", [Priorities]@row), INDEX({INFO Priorities 3}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row), CONTAINS("Priority 3", [Priorities]@row)), CONTAINS("Priority 4", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 4", [Priorities]@row), INDEX({INFO Priorities 4}, MATCH([Name]@row, {INFO Name}), 0), "") + IF(AND(OR(CONTAINS("Priority 1", [Priorities]@row), CONTAINS("Priority 2", [Priorities]@row), CONTAINS("Priority 3", [Priorities]@row), CONTAINS("Priority 4", [Priorities]@row)), CONTAINS("Priority 5", [Priorities]@row)), ", ", "") + IF(CONTAINS("Priority 5", [Priorities]@row), INDEX({INFO Priorities 5}, MATCH([Name]@row, {INFO Name}), 0), "")

      · Share on FacebookShare on Twitter
    • ✭

      This does indeed work, but holy cannoli it's about a half page long. Hahaha. Thank you! I do wonder if there's a more elegant solution as well.

      · Share on FacebookShare on Twitter
    • Community Champion

      Happy to help. I am going to mark this thread and come back to it later to see if I can come up with something better.

      · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Help Article Resources

    Create and edit formulas in Smartsheet

    Formula combinations for cross sheet references

    Smartsheet functions list

    Quick Links

    Categories

    Want to practice working with formulas directly in Smartsheet?

    Check out the Formula Handbook template!

    Trending in Formulas and Functions