IF Function that produce multiple "value_if_true" outcomes
Hi everyone,
I posed a similar question early last week but It hasn't worked. I have two columns, "Department" and "Analyst". I use an IF function that says, if this department then that analyst, etc.
At times, the Department Row can list MULTIPLE departments as shown below. Is it possible to have an IF function that can recognize two departments and list TWO different individuals? I know I could create a longer IF function but that would be hundreds of different combinations.
I have tried to use a IF(CONTAINS( function, but it won't list two analysts.
TIA
Best Answers
-
If you are using a separate table to reference where each department is listed on its own row and the next column over contains the analyst assigned, you could use a formula such as...
=JOIN(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row), "delimiter")
If you could have the same analyst listed multiple times for different departments and want to only have them displayed once to keep things looking a little cleaner, we could work in a DISTINCT function.
=JOIN(DISTINCT(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row)), "delimiter")
-
Happy to help! 👍️
Just a quick note... If you want a line break in between the names so that it looks more like the multi-select dropdown when text wrapping is enabled, your delimiter would be CHAR(10).
Answers
-
How many different analysts do you have? I've solved something similar to this, but it involved having a helper index sheet with all the combinations of individuals. There were only 4 individuals on the team though, so it wasn't TOO big of an issue. The other thing you can do is have a separate column for each department, and have that column fill in the analyst with a unique IF statement for each column.
-
Hi @M Underbrink, we have 13 analysts in total, that's why I'm shying away from created an index sheet with the possible combinations...
There are also times where the analyst column may have up to five different departments participating, so the combinations would be a nightmare.
-
Is it one individual per department? Can you have a contact column for each department? What are you using the names for, an assignment, a notification? Need some more information on the requirements.
-
Yes, it is one analyst per department. The names are used for assignments and notifications. I was thinking of adding a contact column and in addition creating a secondary row consisting of the "2nd" department, however, because projects can have more than two departments it seems irrelevant to do so.
-
If you want to return multiple values in this case, I would suggest running it with a JOIN/COLLECT formula which is kinda dedicated to this.
I believe you already have another sheet that list all individuals and link them to every department available?
That would give us something like this:
=JOIN(COLLECT({Names Column}, {Department Column}, HAS(@cell, [Department Column]@row))
Not sure about the HAS formula here. Didn't try it, so this might not work, trick here is to test every department cell in this column and see if you find it in your department column).
If that doesn't work, could you please provide a bit more screenshot from both sheets with dummy data for a better understanding?
Hope it helped!
-
Hi @David Joyeuse, I don't have another sheet that links analysts to their departments, I have a huge nested IF formula such as: =IF([Lead Department]4 = "Education", "Name1", IF([Lead Department]4 = "Economic Development & Training", "Name2", IF([Lead Department]4 = "Finance", "Name3", IF([Lead Department]4 = "Civil Service Commission", "Name4", this formula continues to Name 13 (the real formula has the actual names).
Are you suggesting I create a separate sheet with a Department Column and then an Analyst column to match them up?
-
In @David Joyeuse 's comment, yes he's referring to having another sheet that matches the values to the names. You'll have to test this out to see how it works. The nice part is, it makes it easy to update if your names/people change - you just update your helper sheet, as opposed to your formulas.
-
I used the following formula and I am receiving a blank cell as shown.
Here is the picture of my dummy data.
-
OK, I just tried it and yes the HAS function doesn't work when there's more than 1 department in our case...
Still I was able to make it work, but that requires adding a lot of helper columns here.
In your case, according to your screenshot, that will be 14 helper columns. One for each department that you'll be able to hide right after.
In each of your department column have this formula:
=IF(HAS([Departments Column]@row, "Department Name"),INDEX({Analyst Range}, MATCH("Department Name", {Department Range}, 0)),"")
Adapt "Department Name" to each helper column. This will return the name for each department.
Then, use this formula in the column you want to display names:
=JOIN(COLLECT([First Department]@row:[Last Department]@row, [First Department]@row:[Last Department]@row, NOT(ISBLANK(@cell))), " ")
This will collect all names that are presents on the row.
It's a bit cumbersome to do and not really efficient, but so far that's all I have for this. Maybe @Paul Newcome or @Andrée Starå will have a better solution that I'd like to read about.
Hope it helped!
-
If you are using a separate table to reference where each department is listed on its own row and the next column over contains the analyst assigned, you could use a formula such as...
=JOIN(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row), "delimiter")
If you could have the same analyst listed multiple times for different departments and want to only have them displayed once to keep things looking a little cleaner, we could work in a DISTINCT function.
=JOIN(DISTINCT(COLLECT({Table Sheet Analyst Column}, {Table Sheet Department Column}, CONTAINS(@cell, [Departments Column]@row)), "delimiter")
-
Thank you both, this is a HUGE help. I went with @Paul Newcome's first suggestion.
-
Happy to help! 👍️
Just a quick note... If you want a line break in between the names so that it looks more like the multi-select dropdown when text wrapping is enabled, your delimiter would be CHAR(10).
-
Hi @Lila De Vera,
Glad to hear you were able to get a working solution for this and a huge thanks to @Paul Newcome for the continued formula support here in the Community!
Ben
-
@Ben G I'm happy to help. I have learned a lot from this Community, so I figure the least I can do is try to give back a little.
-
Hi @Paul Newcome I feel like this solution is what I need to use as well, but I can't figure it out!
Essentially, I created the 14+ columns and have sourced the data from a different active sheet. The formula below works, but will only populate a result if only ONE statement is true; if multiple statements are true, the result is blank. I am trying to figure out how to get multiple results in the same cell based on the formula, while maintaining the Contact-format.
Hoping you can help and thanks in advance!
=(IF([Responsible Dept]@row = "Sales", Sales@row, IF([Responsible Dept]@row = "Ops - GM", GM@row, IF([Responsible Dept]@row = "Ops - EVP", EVP@row, IF([Responsible Dept]@row = "Ops - SVP", SVP@row, IF([Responsible Dept]@row = "Ops - VP", VP@row, IF([Responsible Dept]@row = "Ops - DM", DM@row, IF([Responsible Dept]@row = "Finance", Finance@row, IF([Responsible Dept]@row = "HR", HR@row, IF([Responsible Dept]@row = "Culinary", Culinary@row, IF([Responsible Dept]@row = "Sales - Exec", [Sales - Exec]@row, IF([Responsible Dept]@row = "Project Manager", [Project Manager]@row, IF([Responsible Dept]@row = "Retention", Retention@row, IF([Responsible Dept]@row = "Graphic Design", [Graphic Designer]@row, IF([Responsible Dept]@row = "Marketing", Marketing@row, IF([Responsible Dept]@row = "Risk", Risk@row, IF([Responsible Dept]@row = "Sustainability", Sustainability@row, IF([Responsible Dept]@row = "Technology", Technology@row, IF([Responsible Dept]@row = "CRM", CRM@row, IF([Responsible Dept]@row = "HSG 1", [HSG 1]@row, IF([Responsible Dept]@row = "HSG 2", [HSG 2]@row, IF([Responsible Dept]@row = "HSG 3", [HSG 3]@row, IF([Responsible Dept]@row = "Legal", [Legal]@row)
*The formula above populates the "Assigned To" column; I duplicated the formula for the Resources and FYI columns, changing the reference to [Support Dept]@row or [FYI Dept]@row
**The data for the different columns is linked from a different sheet based on a VLOOKUP formula, i.e.: =VLOOKUP([Account Name]@row, {Sheet Name Range 1}, 41)
Help Article Resources
Categories
Check out the Formula Handbook template!
