Index Match not working with multiple options

I am trying to get a formula to work with multiple options selected in the reference sheet. The reference sheet has a Unit column and multiple contact columns. The contact columns are based off the roles within the unit. I have several formulas based off the Unit column. Some units have the same contact list, so I have multiple selected, (from a drop-down list) in a row on the reference sheet. The formula works for Unit rows that only have one unit selected.

=INDEX({St Gab Organization Superintendent}, MATCH(Unit@row, {St Gab Organization Unit}, 0))

I am trying to figure out how to get this to work without having to break it down by every single unit. For instance, if unit 1A, 1B, 1C, and 1D have the same unit contacts, I want to have them listed on the same row, in the reference sheet. However, only one unit will be listed on the sheet with the formula.

I am sorry if I did not explain this every well. Can someone help me get this to work?


Best Answers


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!