Index Match not working with multiple options

tsetto2

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?

DKazatsky2

Hi @tsetto2,

You were on the right track when you looking at INDEX/COLLECT. Try the following.

=INDEX(COLLECT({St Gab Organization Superintendent}, {St Gab Organization Unit}, HAS(@cell, Unit@row)), 1)

Hope this helps,

Dave

Paul Newcome

@tsetto2 Give this a go:

=IFERROR(INDEX(COLLECT({St Gab Organization Superintendent}, {St Gab Organization Unit}, HAS(@cell, Unit@row)), 1), "")

Paul Newcome

Are you able to provide screenshots (with sample data if needed) for context?

tsetto2

Hey Paul,

I have a reference sheet created that has the management team associated with a unit (see picture 1). If I only have one unit in the row (rows 1-4), my formula will correctly pull emails into another sheet. If I have multiple units selected in the reference row (row 5), my formula will only work if I have those same units in the sheet collecting the information.

I get #NO MATCH each time I try to put the unit individually, in the sheet with the formula (see picture 2, unit A1). I have tried it with Index Collect and could not get it to work.

The formula I use is listed below. I want my reference sheet to have multiple units in the row. However, I want the sheet using the formula to only have one unit associated with each row.

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

DKazatsky2

Hi @tsetto2,

You were on the right track when you looking at INDEX/COLLECT. Try the following.

=INDEX(COLLECT({St Gab Organization Superintendent}, {St Gab Organization Unit}, HAS(@cell, Unit@row)), 1)

Hope this helps,

Dave

Paul Newcome

@tsetto2 Give this a go:

=IFERROR(INDEX(COLLECT({St Gab Organization Superintendent}, {St Gab Organization Unit}, HAS(@cell, Unit@row)), 1), "")

tsetto2

@Paul Newcome and @DKazatsky2 Thank you both!!! The formulas work!

Paul Newcome

Happy to help. 👍️