Help converting an Excel function

Steve Cribbs

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!

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

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.

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