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!
Best Answer

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

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

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

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

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"))))))))))))
Help Article Resources
Categories
Check out the Formula Handbook template!