formula for contact list to show multiple values from source sheet

ChristinaP

Hi, im hoping someone can help me.

I made a source sheet to List the Team Leaders (contact list) in one column and the teams in another column

i then did index match formula in my main sheet to pull up the Leader (contact list - multi select) depending on what teams they choose.

When they choose multiple, it would just say #no match

ive tried to look at different collect join formulas but im either not writing my formulas right or they wont work in my scenario.......

this is the original formula =INDEX({Ministries and Ministry Team Leaders Range 2}, MATCH(Ministry@row, {Ministries and Ministry Team Leaders Range 3}, 0))

does anyone know if there is a way for it to list all the team leaders for all the teams they chose?

Kind Regards

Christina Paton

Nick Korna

Hi @ChristinaP,

You can use INDEX & COLLECT for this:

=INDEX(COLLECT({Ministries and Ministry Team Leaders Contacts}, {Ministry}, [Ministry]@row), 1)

Hope this helps, but if you've any problems/questions then just post! 🙂

ChristinaP

Hi @Nick Korna

Thank you for your reply :)

I have tested it however seem to have the same issue where it works for one but when there is multiple ministries it says #Invalid Value instead of showing the multiple contacts.... because my sheet uses multi select dropdown list i made sure the source sheet was multi select dropdown list also but it didnt make a difference either....

Paul Newcome

Typically you would need the JOIN function instead of the INDEX function. INDEX only pulls one entry. JOIN joins together multiple entries.

The challenge though is this... We cannot currently use a formula to join together multiple usable contacts into a single cell even when the column is formatted to allow for multiple contacts. It will show all of the contacts listed out, but it will be stored as a text string and can't be used as contacts in things like automations.

You will have to leverage the API or the premium add-on Bridge to get multiple variable usable contacts into a single cell.

ChristinaP

Hi @Paul Newcome

Oh such a bummer. We are on the old team plan (and small NFP that can’t afford to upgrade). So many frustrations over these last 7 years with most tasks unable to do something.

Could you please show me the join function formula that would at least show as text (as I had unsuccessfully tried that in combinations also)…

Thank you so much for your time

Christina

Paul Newcome

No matter the level of your plan, no one is able to use a formula to output multiple usable contacts like this.

The JOIN/COLLECT would be similar to your existing INDEX/COLLECT except (of course) you would use the JOIN function in place of the INDEX, and you would put your delimiter in place of the 1 at the end. My suggestion would be CHAR(10) for the delimiter. That is a line break and keeps things looking a little cleaner when you use the wrap text function on the column.

The biggest difference is using a HAS function in place of Ministry@row. It would look more like this:

HAS(@cell, Ministry@row)

ChristinaP

Hi Paul,

I tried this:

=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 4}, HAS(@cell, Ministry@row), CHAR(10)))

but it said incorrect argument set

I dont know how to use the HAS(@cell corrrectly and tried a few different things but when i removed it/changed to this:

=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 4}, Ministry@row), CHAR(10))

it is blank when there is multiple :(

is it to do with the multiselect lists?

I changed the criteria to just be witten names and no contact cells but still wont work so thought maybe its the multiselect lists - cant change this as people need to select them in their form....

guessing i will just have to leave this one....

Paul Newcome

You have a misplaced closing parenthesis. You closed the HAS function but didn't close the COLLECT function before entering your delimiter.

Take one closing parenthesis from the very end and move it to immediately before that very last comma.

ChristinaP

ah thanks for clarifying - but yeah still doesnt work - just shows blank for multiple - guessing its to do with the multi-select dropdown list

ChristinaP

Oh my golly i got it to work by moving the has function around

=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 1}, HAS(Ministry@row, @cell)), "; ")

the char 10 didnt do a new line so i just did ; to seperate

ChristinaP

CHAR(10) now works :) my cells werent set to wrap so it just left them on the same line hahaha

=JOIN(COLLECT({Ministries and Ministry Team Leaders Range 2}, {Ministries and Ministry Team Leaders Range 1}, HAS(Ministry@row, @cell)), CHAR(10))

thanks for your help with everything