Formula help to match multi select values

I have two multi select columns. Those columns have similar lists, but not exact.

The multiselect data has associations with other data that we already know. (Washington = West, Iowa = Central, etc.)

We’d like to get a formula that helps us identify when the West selections are input from the multiselect fields.

For an example, I dummied up some Client data with Drivers and Deliveries (the multi select fields) and the result columns I am trying to solve for (Is this in the West? Is this in Central?)

I am trying to figure out the best way to answer the question if a value in either Column “Drivers” or Column “Deliveries” matches a criteria (West)

Example, if either the Driver column or Deliveries column contains Idaho or Washington, then in the column “Is this in the West?”, I would like it to say “West” or “No”.

I am not sure the best formula structure to use here.

Or will I need a helper column with the states that are in the west to reference?

Goal: Client, Drivers, Deliveries are inputs and “Is this in the West” or “Is this in Central” are the result of the formula.

Find more posts tagged with

Accepted answers

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359724#Comment_359724

@Michael McS I was afraid of that. You're going to have to use the first formula, where you use a HAS for each state value. It's unfortunately trying to match every value in the field to the lookup list.

This one will work though:

=IF(OR(HAS(Drivers@row, "Washington"), HAS(Drivers@row, "Idaho"), HAS(Deliveries@row, "Washington"), HAS(Deliveries@row, "Idaho")), "West", "No")

All comments

Jeff Reisman

@Michael McS

I would recommend using IF with OR and HAS functions.:

=IF(OR(HAS(Drivers@row, "Washington"), HAS(Drivers@row, "Idaho"), HAS(Deliveries@row, "Washington"), HAS(Deliveries@row, "Idaho")), "West", "No")

~~Comparing multi-select values to a lookup column is a more complex task.~~

Editing after a quick test - not so complex after all. Call your helper column "WestStates":

=IF(OR(HAS(WestStates:WestStates, Drivers@row), HAS(WestStates:WestStates, Deliveries@row)), "West", "No")

Mike TV

@Michael McS

Is this in the West formula:

=IF(OR(CONTAINS("Washington", Drivers@row), CONTAINS("Washington", Deliveries@row), CONTAINS("Idaho", Drivers@row), CONTAINS("Idaho", Deliveries@row)), "West", "No")

Is this in Central formula:

=IF(OR(CONTAINS("Iowa", Drivers@row), CONTAINS("Missouri", Drivers@row), CONTAINS("Iowa", Deliveries@row), CONTAINS("Missouri", Deliveries@row)), "Central", "No")

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359693#Comment_359693

Yes, those should work as well.

I prefer to use HAS when working with multi-select columns, just because it's more precise. There's a lot of confusion with regards to HAS and CONTAINS, so much so that I re-wrote part of the HAS function help page to make it more clear. (Previously, the HAS page was just a hastily copied version of the CONTAINS page.)

The HAS function is designed to work with multi-select dropdown and multi-contact columns. It won’t cause an error if used with other column types, but it will only return true if the criterion exactly matches the contents of the cell or a distinct value within a multi-select or multi-contact cell.
Sample usage: =IF(HAS([Day of Week]: [Day of Week], "Monday"), "yes", “no”)
In the example above, if [Day of Week]1 is in a Text/Number column, HAS will return “true” if [Day of Week]1 = “Monday” but will return “false” if [Day of Week]1 = “Monday Tuesday.” If [Day of Week]1 is in a multi-select column, HAS will return “true” if [Day of Week]1 = “Monday” and will also return true if [Day of Week]1 contains values of “Monday” and “Tuesday”. It will return “false” if the value in [Day of Week]1 = “Monday Tuesday”.
The HAS function is distinct from CONTAINS because it searches for specific values where CONTAINS searches for strings or characters containing the criteria.
For example, using HAS to search for “apples” will only return true if “apples” is by itself in a text/number column or is a distinct value within a multi-select column. Using CONTAINS to search for “apples”, on the other hand, will return true if it finds “apples” in any part of a text/number cell, or within any distinct value inside a multi-select cell.

Michael McS

@Jeff Reisman Thanks.

Interesting. It did not work in all scenarios. Added the Fx in column6. It worked for Coke, Pepsi, Bud, Miller but NOT for McDonald's. I would expect it to, since Idaho and Washington show up in both the Drivers and Deliveries column here.

thoughts on why it may have worked on the first four entries but not the fifth?

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359724#Comment_359724

This one will work though:

=IF(OR(HAS(Drivers@row, "Washington"), HAS(Drivers@row, "Idaho"), HAS(Deliveries@row, "Washington"), HAS(Deliveries@row, "Idaho")), "West", "No")

Michael McS

@Jeff Reisman I was afraid of that too. I'll build out with our real environment (which has just a bit more selections that the example data) and report back.

Thanks for checking it out.

Michael McS

@Jeff Reisman The revision is working in the production environment. Appreciate the help!

Jeff Reisman

https://community.smartsheet.com/discussion/comment/359867#Comment_359867

Excellent, glad to hear it. Happy it HAS worked, lol. * rimshot *

Michael McS

That was terrible. :)

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.