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!

Tags:

Best Answer

  • Steve Cribbs
    Steve Cribbs ✭✭
    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

  • Heather Duff
    Heather Duff ✭✭✭✭✭✭
    edited 02/10/22

    @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

  • 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
    Heather Duff ✭✭✭✭✭✭

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

  • Steve Cribbs
    Steve Cribbs ✭✭
    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"))))))))))))

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!