I am trying to use the below formula to collect LOB based on the Functional unit and Managing office provided. I am referencing another sheet which contains a matrix of Functional Units and Managing offices that fall under certain LOB

=IFERROR(INDEX(COLLECT({LOB}, {FU}, CONTAINS([Functional Unit (Dept ID)]@row, @cell, [Managing Office (Estab ID)]@row, @cell)),1)"No Result")

Best Answer

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭
    Answer ✓

    Ok. Change your reference table back to the second screenshot where you used the multi-select columns. From there your formula would look something like this...

    =INDEX(COLLECT({Reference Sheet LOB Column}, {Reference Sheet FU Column}, HAS(@cell, FU@row), Reference Sheet MO Column}, HAS(@cell, MO@row)), 1)


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!