JOIN(INDEX cross sheet with multi select criteria

Mad Ed

I am attempting to match multi select criteria in one sheet to the corresponding contact list in another sheet. The multi select column has over 100 possible criteria, though the average cell has 3-4 selected. There are some circumstances that the formula will need to return two or more names. I can't make JOIN(COLLECT work; it will only return one name when the multi select column only has one criteria selected:

=IF(NOT(ISBLANK([Area of Expertise]@row)), JOIN(COLLECT({ARM}, {Discipline}, [Area of Expertise]@row), ", "))

Column references:

{ARM}: contact list on sheet 2

{Discipline}: list of all multi select criteria on sheet 2

[Area of Expertise]: multi select column on sheet 1

Georgie

Hi @Mad Ed,

Try this:

• =IF(NOT(ISBLANK([Area of Expertise]@row)), JOIN(COLLECT({ARM}, {Discipline}, HAS([Area of Expertise]@row, @cell)), ", "))

This worked in my test sheets to return the relevant multiple names where the Area of Expertise column has multiple values. 

Take a look at these resources for more information on the HAS function and using @cell:

• HAS Function
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

Paul Newcome

Are you able to provide some screenshots for context? Sample data is fine if needed.

Mad Ed

As you can see, the AR Manager column above didn't populate anything in the second row shown, but it didn't correctly populate the AR Manager for the third row.

Georgie

Hi @Mad Ed,

Try this:

• =IF(NOT(ISBLANK([Area of Expertise]@row)), JOIN(COLLECT({ARM}, {Discipline}, HAS([Area of Expertise]@row, @cell)), ", "))

This worked in my test sheets to return the relevant multiple names where the Area of Expertise column has multiple values. 

Take a look at these resources for more information on the HAS function and using @cell:

• HAS Function
• Create efficient formulas with @cell and @row

Does that work for you?

Georgie

Mad Ed

@Georgie That worked, thank you! I had tried including HAS previously, but I think I used the wrong syntax.