IF/AND/HAS for 3 column references, 6 options

Based on the content above for reference only, the actual sheet screenshot below. I need a formula or help with a helper sheet/workflow, to bring back one of 6 results.

(above image) Tax Status Column is a dropdown with the 2 options shown, Bottling Location and Entity Columns are INDEX(COLLECT formulas (if it matters) pulling from another sheet.

(Actual Below) If Tax Status is Tax-Paid, Bottling LOCATION is Infinity Bottling, Entity is any of the 6, it enters the corresponding number from that entity into the TAX-PAID BOTTLING HOUSE PERMITS cell. I've created 2 reference formulas before, but this one is outside my ability. Appreciate any assistance.

Find more posts tagged with

Formulas

Solution Center

Workflows in Smartsheet

Accepted answers

Nick Korna

HI @MMMueffelman,

Is something like this what you're after?

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1))

If you just wanted the number rather than whole string, you can use a RIGHT function in addition if the strings are uniform in length for the permit number. For example:

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), RIGHT(INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1), 5))

Would display the 5 digit number only.

Example output showing each formula in action:

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

All comments

Nick Korna

HI @MMMueffelman,

Is something like this what you're after?

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1))

If you just wanted the number rather than whole string, you can use a RIGHT function in addition if the strings are uniform in length for the permit number. For example:

=IF(AND([Tax Status]@row = "Tax-Paid", [Bottling Location]@row = "Infinity Bottling"), RIGHT(INDEX(COLLECT({Formula result}, {Entity}, Entity@row), 1), 5))

Would display the 5 digit number only.

Example output showing each formula in action:

Hope this helps, but if I've misunderstood something or you've any problems/questions then just post! 🙂

MMMueffelman

Perfect, thank you! Nesting formulas get me every time. 😁

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.