JOIN(INDEX cross sheet with multi select criteria
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
Best Answer
-
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:
Does that work for you?
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
Answers
-
Are you able to provide some screenshots for context? Sample data is fine if needed.
-
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.
-
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:
Does that work for you?
Georgie
Need more information? 👀 | Help and Learning Center
こんにちは (Konnichiwa), Hallo, Hola, Bonjour, Olá, Ciao! 👋 | Global Discussions
-
@Georgie That worked, thank you! I had tried including HAS previously, but I think I used the wrong syntax.
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66.5K Get Help
- 432 Global Discussions
- 152 Industry Talk
- 494 Announcements
- 5.3K Ideas & Feature Requests
- 85 Brandfolder
- 155 Just for fun
- 76 Community Job Board
- 504 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 307 Events
- 37 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!