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

Find more posts tagged with

Accepted answers

Paul Newcome

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")

Paul Newcome

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).

All comments

Paul Newcome

The reason your formula is returning blank if there are multiple trues is because you have the formula as a nested IF which works from left to right and stops on the first true value. To be able to populate multiple outputs based on multiple true values, you would have to "add" your IF statements together instead of nesting them.

=IF(Column@row = "A", "A") + IF(Column@row = "B", "B") + ...............

Having said that, there is unfortunately no way to use a formula to output multiple useable contacts into a single contact cell even if the columns set to allow multiple contacts.

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.