Trouble creating exact match formulas to list tasks associated with specific departments.

I'm working on a set of formulas that will find the tasks and list them for the exact matches of the below Department names (among many others). I' have a formula that will work for a few of them but not all of them. As you can see they have similar letters (EDI and IT) and the results they return overlap each other, or double-pull some values.

Credit
EDI
IT
Premedia

This is the formula I have used and works well for all of the other department names in the list, however, it does not work for the 4 listed above. (Also, the source list is a multi-select dropdown.)

=JOIN(COLLECT({Task}, {Dept.}, CONTAINS("Finance", @cell)), CHAR(10))

Find more posts tagged with

Sheets

Formulas

Accepted answers

All comments

Paul Newcome

Is the {Dept.} referencing a multi-select column?

Seatora

Hi Paul! Yes - Dept. is a multi-select column.

Seatora

I'm also wondering if it may just be better to make those problematic values more unique. (Ex. EDI would become E.D.I., and IT would become I.T.) I tried tackling this using a number of different iterations of the formula I provided, without any luck.

Paul Newcome

Since it is multi-select, you can use a HAS function instead to look for the exact selection.

=JOIN(COLLECT({Task}, {Dept.}, HAS(@cell, "Finance")), CHAR(10))

Seatora

Thank you! This is great. It looks like this formula is also working for any dept. I sub for "Finance." Just for me - do you mind explaining why FIND and CONTAINS don't work in this scenario?

Paul Newcome

CONTAINS looks for a match (not case sensitive) anywhere within the cell.

FIND might have worked as it is case sensitive, but it still searches the entire cell for the indicated string. So FIND("IT", @cell) would not have triggered if the cell had something like "Credit" in it because it is not the same case, but… You can't use FIND as a stand-alone function like you can with HAS or CONTAINS. HAS and CONTAINS both put out true/false values. FIND however outputs a number that indicates where within the string the match was found. To get FIND to work, you need an additional argument to say where FIND's output is greater than zero.

FIND("IT", @cell) > 0

It is still less reliable because it does search the entire cell plus you have to remember to put that extra argument in.

When dealing with multi-select dropdown or contact type columns, I always try to use HAS first because it evaluates each cell's selections as individual data points as opposed to the entire cell as one long string.

Seatora

One more question - I'm using a COUNTIF formula on an accompanying sheet, and I took what you gave me and set up a HAS version (to pull the same values from the same multi-select dropdown list). In the screenshot, it works for _Mission Critical, but not for Catalog Ops. It returns that there are 0 instances of "Catalog Ops.", but there should be 3.

What am I doing wrong?

Seatora

UPDATE: Apologies. I was mistaken, it actually doesn't work for _Mission Critical or Catalog Ops. in this case. Will HAS work with the COUNTIFS formula I have here?

Paul Newcome

It should. Try copy/pasting the string from the dropdown column properties to ensure it is spelled exactly the same in both places.

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.