Help converting an Excel function

Hello,

I'm currently using the following Excel function(s) to return a True/False value to determine if any of the alphabetic values listed in the AF column are present in the H2 cell.

I'm having trouble using both the FIND and CONTAIN functions to include a range of values to search_for.

=SUMPRODUCT(--ISNUMBER(SEARCH($AF$6:$AF$18, $H2)))>0

Appreciate the help!

Find more posts tagged with

Formulas

Accepted answers

Steve Cribbs

I ended up having to use nested if(contains statements to accomplish this. Not clean but it works.

=IF(CONTAINS("AAA", Destination@row), "Yes", IF(CONTAINS("BBB", Destination@row), "Yes", IF(CONTAINS("CCC", Destination@row), "Yes", IF(CONTAINS("DDD", Destination@row), "Yes", IF(CONTAINS("EEE", Destination@row), "Yes", IF(CONTAINS("FFF", Destination@row), "Yes", IF(CONTAINS("GGG", Destination@row), "Yes", IF(CONTAINS("HHH", Destination@row), "Yes", IF(CONTAINS("III", Destination@row), "Yes", IF(CONTAINS("JJJ", Destination@row), "Yes", IF(CONTAINS("KKK", Destination@row), "Yes", IF(CONTAINS("LLL", Destination@row), "Yes", "No"))))))))))))

All comments

Heather Duff

@Steve Cribbs

I'm not 100% sure I follow exactly what you want, but I think I may be headed in the right direction (it's just way different from what your formula looks like:

=IF(COUNTIF($AF$6:$AF$18,CONTAINS(@cell,$H2))>0,"True","False")

This basically says to count how many cells in AF6 through AF18 contain the text in cell H2, and if the count is more than zero, show True; otherwise (if the count is zero), show False.

Hope this helps. Let me know if it works!

Best,

Heather

Steve Cribbs

Hi @Heather Duff,

Thanks for this recommendation. When I populate this into my SmartSheet and amend it for the column names it comes back as unparseable.

=IF(COUNTIF($column30$6:$column30$17,CONTAINS(@cell,Destination1))>0,"True","False")

Heather Duff

@Steve Cribbs This may not fix it, but try putting your [column30] in square brackets.

Steve Cribbs

I ended up having to use nested if(contains statements to accomplish this. Not clean but it works.

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.