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.

    What am I doing wrong in this formula?

    ✭
    edited 04/17/24 in Formulas and Functions

    =IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),[{Unit/Manager List WI Range 4}))

    My goal with this formula is: if SITE and DEPARTMENT Name on Sheet1 matches SITE NAME and DEPARTMENT NAME on sheet 2 then pull the Manager Email from sheet 2 into Manager in sheet 1

    AW Placement Request is sheet 1 and Unit/Manger List WI is sheet 2

    Sheet 1.PNG
    Sheet 2.PNG


    Tags:
    · Share on FacebookShare on Twitter

    Sign in
    Try Smartsheet

    Best Answer

    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Casie

      All good. Now let's have a look at the formula.

      According to this article, we want the formula to have the following syntax:

      =INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

      Ignoring the IFERROR for a moment, we'll need to edit your formula to something like (note the '1' at the end):

      =INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1)

      Now to add the IFERROR function

      =IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1), {Unit/Manager List WI Range 4})

      Lets see how that goes...

      Hope this helps and that you have a great day,

      Jason Albrecht MBA, MBus(AppFin), DipFinMgt

      LinkedIn profile - Open to work

      · Share on FacebookShare on Twitter

    Answers

    • ✭✭✭✭✭✭

      Hi @Casie

      I have not looked at the formula in detail yet. But, what I can see is a mismatch of brackets.

      image.png

      There seems to be an open square bracket "[" (red arrow), which doesn't seem necessary?

      And, while there's 3 open brackets "(", there are 2 closed brackets ")"?

      I suspect if the square bracket is deleted and an extra closed bracket included at the end, you may get your answer?

      Let us know how you go.

      Hope this helps and that you have a great day,

      Jason Albrecht MBA, MBus(AppFin), DipFinMgt

      LinkedIn profile - Open to work

      · Share on FacebookShare on Twitter
    • ✭
      Capture.PNG

      I get the #incorrect argument error.

      Thank you I've been looking at this for hours

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭
      Answer ✓

      Hi @Casie

      All good. Now let's have a look at the formula.

      According to this article, we want the formula to have the following syntax:

      =INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1)

      Ignoring the IFERROR for a moment, we'll need to edit your formula to something like (note the '1' at the end):

      =INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1)

      Now to add the IFERROR function

      =IFERROR(INDEX(COLLECT({Master Unit/Manager List WI Range 1}, {Master Unit/Manager List WI Range 2}, [Site]@row, {Master Unit/Manager List WI Range 3}, [Department Name]@row),1), {Unit/Manager List WI Range 4})

      Lets see how that goes...

      Hope this helps and that you have a great day,

      Jason Albrecht MBA, MBus(AppFin), DipFinMgt

      LinkedIn profile - Open to work

      · Share on FacebookShare on Twitter
    • ✭

      @Jason Albrecht when looking at =INDEX(COLLECT({Column to return}, {Column 1 with value to match}, "Value 1", {Column 2 with value to match}, "Value 2"), 1) I found that I had things backwards. I ended up using =INDEX(COLLECT({Unit/Manager List WI Range 1}, {Unit/Manager List WI Range 2}, Site@row, {Unit/Manager List WI Range 3}, [Department Name]@row), 1) and it worked. Minus I am getting #INVALID VALUE when there is no match

      · Share on FacebookShare on Twitter
    • ✭✭✭✭✭✭

      Awesome @Casie

      Thank you for letting us know.

      Glad to be of service.

      Hope this helps and that you have a great day,

      Jason Albrecht MBA, MBus(AppFin), DipFinMgt

      LinkedIn profile - Open to work

      · 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

    • Formula Help

      I'm trying to create a SUMIF formula that looks at the salesperson name in a column and adds up or totals their $ sales in another column. To ultimately show in Dashboard of Totals Sales by Salesperso…
      User: "Allan Z"
      Answered ✓
      Formulas and Functions
      9
      2

    • Unparseable Error

      Good day Smartsheet Team, Getting an unparseable error on this formula: =IF($Name@row <> "",(SUMIFS({Expense}, {Period},1, {Type}, OR(@cell = "RES602782", @cell = "RES602497")),"") Trying to pull in a…
      User: "stratman"
      Answered ✓
      Formulas and Functions
      15
      2

    • Formula to return the non-blank cell

      I have a sheet that compiles all the responses from a form. The sheet has multiple start and end date columns, but only one start and one end date cell is NOT blank depending on the activity selected …
      User: "m_anderson"
      Answered ✓
      Formulas and Functions
      13
      2