Formula to add multiple names to one cell

Rick Girard

I need to create a lookup to select multiple names from 1 sheet and add those names to 1 cell in another sheet:

Sheet 1: (names in multiple cells)

Name - Type

Rick - A

Jane - B

Bob - A

Sheet 2: (put qualified names in 1 cell)

Lookup names = 'Type A' and copy them into 1 cell.

I know VLOOKUP or an INDES + MATCH can find a name and place it in a cell, but i want to place multiple names in the same cell. can that be done ?

Rick Girard

Additional data to clarify my request:

I am looking to create a formula that uses the IMPACTED REGION and IMPACTED FUCTIONAL AREAS to result in a Name to be populated in the IMPACTED & RESPONSIBLE RISK OWNER column. the 1st 2 columns (REGION + AREAS) are multiple selectable entries.

Example:

Regions column: US LATAM EMEA APAJ

Areas column: HR - Marketing - Operations - etc...

Owners column: Name 1, 2, 3 etc

example:

Region = US

Area = HR & Operations

Name = Rick (desired name to populate the Owner cell) etc..

(if the IMPACT Region listed US and IMPACT Area listed either HR or Operations, then Owner Name 'Rick' would populate the Owner cell)

What is the proper formula string to create an IF AND OR selection to then select a Name for the Owner?

*I have run into issues where only the top name in the menu only gets selected (ie. if Rick were 1st on top with Jane and Joe following as 2nd and 3rd, those names don't get picked up in a formula string).

thanks for your assistance!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326819#Comment_326819

Since you're using multi-select cells, using the HAS function is key. HAS will treat each standalone value inside a multi-select cell as a distinct value. For example, if I wrote this IF on Row 1 of your screenshot, it would equal "Failure":

=IF([::IMPACTED REGIONS(S)]@row = "US", "Success", "Failure")

But if I use HAS for my logical statement, I would get "Success":

=IF(HAS([::IMPACTED REGIONS(S)]@row, "US"), "Success", "Failure")

Using this, let's get your IF/AND/OR/HAS syntax right:

=IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, "US"), HAS([::IMPACTED REGION(S)]@row, "CAD")), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Insurance")), "NoName", "")

Just to help you see exactly how the pieces come together, we're placing an OR inside the AND, along with the other criteria. The yellow is the AND statement, with the yellow comma and yellow parentheses at the end showing you the parts of the AND. The blue is the embedded OR, the green is the rest of the criteria for the AND, though this could be another OR as well. Taken together, the entire AND and its parts make up the logical statement portion of the IF:

The result of this formula for Row 2 would be "NoName".

Here is the above in use with two OR statements embedded in the AND, from my test sheet. The data matches one of the two options in each OR statement.

Change one of the multi-select cells to remove all of the criteria that would match, then I get "Fail":

Rick Girard

Thanks Jeff and Andrea, yes i was thinking along those lines as well, some column names are long and the menu options are as well, will be working on reducing those to see if i can make it fit. I may also look to offload the match outputs to another column and x-ref them but it may be a wash in terms of char count. thanks again !

Rick Girard

Additional data to clarify my request:

I am looking to create a formula that uses the IMPACTED REGION and IMPACTED FUCTIONAL AREAS to result in a Name to be populated in the IMPACTED & RESPONSIBLE RISK OWNER column. the 1st 2 columns (REGION + AREAS) are multiple selectable entries.

Example:

Regions column: US LATAM EMEA APAJ

Areas column: HR - Marketing - Operations - etc...

Owners column: Name 1, 2, 3 etc

example:

Region = US

Area = HR & Operations

Name = Rick (desired name to populate the Owner cell) etc..

(if the IMPACT Region listed US and IMPACT Area listed either HR or Operations, then Owner Name 'Rick' would populate the Owner cell)

What is the proper formula string to create an IF AND OR selection to then select a Name for the Owner?

*I have run into issues where only the top name in the menu only gets selected (ie. if Rick were 1st on top with Jane and Joe following as 2nd and 3rd, those names don't get picked up in a formula string).

thanks for your assistance!

Jeff Reisman

@Rick Girard

Off the top of my head, you might employ a lookup sheet for this. You'd need a RowID column or something you could use to match values between the Inventory sheet and the lookup sheet. (You could also do it on the inventory sheet itself if you want to employ a bunch of hidden columns.)

In the lookup sheet, create a multi-select column for each owner name. Use IF with AND (or OR), HAS, and INDEX/MATCH to look for that owner's values in your Inventory sheet's Regions and Areas columns:

=IF(AND(HAS(INDEX({Inventory Regions}, MATCH(RowID@row, {Inventory RowID}, 0)), "CAD"), HAS(INDEX({Inventory Areas}, MATCH(RowID@row, {Inventory RowID}, 0)), "Insurance")), "Anup", "")

and it if finds any of them, place the owner's name in their column. Do this for each owner, then in one last Multi-select column, simply add the owner columns together like so:

=OwnerNameA@row + OwnerNameB@row + OwnerNameC@row

Since they are multi-select values and you're adding them together in a multi-select column, the result is each distinct value in the multi-select.

Then you just use an INDEX/MATCH from the inventory sheet to pull the resulting cell value from the lookup sheet into the Owners column.

*If you want to do this all on the inventory sheet with hidden columns, replace the INDEX/MATCH portion with the column name you're evaluating together with @row, like this: [::IMPACTED FUNCTIONAL AREA(S)]@ROW

Rick Girard

Thanks Jeff, I have part of this figured out using just a plain OR statement to get part of this going:

=IF(OR([::IMPACTED REGION(S)]@row = "US", [::IMPACTED REGION(S)]@row = "CAD", [::IMPACTED FUNCTIONAL AREA(S)]@row = "Insurance"), [=IMPACTED & RESPONSIBLE RISK OWNER(S)]@row, "NoName")

I can now use the OR statement for Region of 'US' OR 'CAD' plus the OR for the Area of 'Insurance' and I get a Name response. But i now need to string Regions by OR statement AND the Areas by OR statement to get a Name to come out.

My issue is using a nested AND and OR correctly. So I can string Regions (ex. US OR CAD OR EMEA) AND Areas plus the OR (Insurance OR Marketing OR Operations..) i could get a cell 'Name'.

Appreciate guidance on proper AND OR positioning, getting frustrated with errors right now.

thanks!

Rick

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326819#Comment_326819

Since you're using multi-select cells, using the HAS function is key. HAS will treat each standalone value inside a multi-select cell as a distinct value. For example, if I wrote this IF on Row 1 of your screenshot, it would equal "Failure":

=IF([::IMPACTED REGIONS(S)]@row = "US", "Success", "Failure")

But if I use HAS for my logical statement, I would get "Success":

=IF(HAS([::IMPACTED REGIONS(S)]@row, "US"), "Success", "Failure")

Using this, let's get your IF/AND/OR/HAS syntax right:

=IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, "US"), HAS([::IMPACTED REGION(S)]@row, "CAD")), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Insurance")), "NoName", "")

Just to help you see exactly how the pieces come together, we're placing an OR inside the AND, along with the other criteria. The yellow is the AND statement, with the yellow comma and yellow parentheses at the end showing you the parts of the AND. The blue is the embedded OR, the green is the rest of the criteria for the AND, though this could be another OR as well. Taken together, the entire AND and its parts make up the logical statement portion of the IF:

The result of this formula for Row 2 would be "NoName".

Here is the above in use with two OR statements embedded in the AND, from my test sheet. The data matches one of the two options in each OR statement.

Change one of the multi-select cells to remove all of the criteria that would match, then I get "Fail":

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326892#Comment_326892

So taking all this together to solve your entire issue:

If you can identify all the "rules" that determine whose name(s) should be selected for the [=IMPACTED & RESPONSIBLE RISK OWNER(S)] column, you could create each one's formula, and then add each one together as one long formula in that column:

=IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, "US"), HAS([::IMPACTED REGION(S)]@row, "CAD")), OR(HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Insurance"), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Partnerships/Alliances"))), "Amar Maletira", "") +

IF(AND(OR(HAS([::IMPACTED REGION(S)]@row, "US"), HAS([::IMPACTED REGION(S)]@row, "LATAM"), HAS([::IMPACTED REGION(S)]@row, "EMEA")), OR(HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Customer Success"), HAS([::IMPACTED FUNCTIONAL AREA(S)]@row, "Partnerships/Alliances"))), "Greg Hrncir", "") +

IF(AND...keep going...

(Just to avoid this kind of mess, I would do these as hidden columns, one for each owner name, and then just add all the hidden columns together in the [=IMPACTED & RESPONSIBLE RISK OWNER(S)] column.)

Rick Girard

Thank you so much Jeff, you've been more than generous with your time, i'll be reviewing this and putting to use and will let you know the outcomes!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/326895#Comment_326895

Just a note - after a little fiddling, I realized that combining values this way won't work. You have to combine values taken from individual cells in another multi-select column. So, using the hidden columns is your best bet, over trying to combine all the formulas into one cell. In other words, in a multi-select cell, ="Jeff" + "Jill" would result in "JeffJill", whereas =[Name 1 Column]@row + [Name 2 Column]@row would result in "Jeff" and "Jill" as separate multi-select values.

Rick Girard

yes thanks i did note some discrepency, there are too many variables to deal with and decided to use only one column (Area) as the match criteria instead of 2 columns (Area & Region).

Now i need to do an OR string to include the 10+ Area names and when a match hits for any of those apply a Name or Names (could be multiple) to the cell...

Rick Girard

Yup, actually the linkage using + worked with little tweaking and I was able to create the entire formula and after testing it looks like it working which is awesome. Thank you very much for this help , i certainly wouldnt have gotten this on my own!

My only issue is the string is so long that the cell cannot accept the whole formula , it cuts off about the last 4 -5 lines of the formula, but what i tested after adjusting that, it works.

Can you tell me is there a character count limit in each cell?

Jeff Reisman

@Rick Girard

There's a limit of 4000 characters in a cell. That's a doozy of formula!

Andrée Starå

Hi @Rick Girard

I hope you're well and safe!

Here's a possible workaround or workarounds for the character limit.

• Shorten the Column Names / Ranges.

Would that work/help?

I hope that helps!

Be safe and have a fantastic weekend!

Best,

Andrée Starå | Workflow Consultant / CEO @ WORK BOLD

✅Did my post(s) help or answer your question or solve your problem? Please support the Community by marking it Insightful/Vote Up or/and as the accepted answer. It will make it easier for others to find a solution or help to answer!

Rick Girard

Thanks Jeff and Andrea, yes i was thinking along those lines as well, some column names are long and the menu options are as well, will be working on reducing those to see if i can make it fit. I may also look to offload the match outputs to another column and x-ref them but it may be a wash in terms of char count. thanks again !

Rick Girard

I was able to tidy up the format to get the whole formula in the cell (subset of that below).

I have a remaining issue, since the output of the Match is a Name, I want to enable that column as a Contact column, but i am having issues parsing out the "names" in quotes to be recognized by the contact link (outlook). It will not convert Holly Jones or any of the others to an outlook address.

Any guidance on how i can get those names recognized as Contact Names?

thanks!

Rick Girard

=IF(AND(OR(HAS([REGION]@row, "US"), HAS([REGION]@row, "CAD"), HAS([REGION]@row, "LATAM"), HAS([REGION]@row, "APJ"), HAS([REGION]@row, "EMEA")), OR(HAS([AREA]@row, "HR"),HAS([AREA]@row, "Legal, Regulatory, Compliance & Government"),HAS([AREA]@row, "Insurance"))), "Holly Jones; Rich Sands; Joe Glass;", "")

Jeff Reisman

https://community.smartsheet.com/discussion/comment/327135#Comment_327135

Good job tidying up the formula!

I was just messing with Contact columns yesterday and gave up in frustration. You'd think that a multi-select Contact column could act just like a regular multi-select column, but it can't. There's an application layer over the field to present the underlying data as a nicely formatted name with a little initial badge or profile pic, with a bunch of data such as email address residing underneath the application layer. Smartsheet developers will need to do some more robust coding to the Smartsheet app to be able to treat a Contact column value as one unique thing that can function in formulas like a multi-select text/number value.

Rick Girard

Possible to do a lookup against those names in the cell when they are in "quotes" perhaps if i make each name its own "quote" ? i could create a xref sheet to match on the name and output the email address.. Would like to get your input before i dive into that...

thanks jeff!

Rick Girard