Problems returning a cell from an external sheet using "Contains"

edited 11/02/21 in Formulas and Functions

=IF(CONTAINS([1]@row, {Shipping Range 4}), {Shipping Range 5}, "False")

We are trying to use contains to search within a column that has multiple entries within cells to find a number and then return a reference from another column on the same row if a match is found. It works if we only want 'true' or 'false' returned. But does not work if a column is refenced as the 'True' element.


  • Parker Oxford
    Parker Oxford ✭✭✭✭✭
    edited 11/02/21

    Your current formula is trying to return something from the "Shipping Range 5" column (i.e. it has no @row element) whenever your CONTAINS function returns "True".

    If you only want True or False to come from the formula, replace the {Shipping Range 5} text with "TRUE" (see below)

    =IF(CONTAINS([1]@row, {Shipping Range 4}@row), "TRUE", "FALSE")

  • Thank you for your reply. I don't want it to return True or False I can get it to do that. I want it to return another cell from the same row that it finds the match on from the Shipping sheet.

    I tried this but it returned #unparseable

    =IF(CONTAINS([1]@row, {Shipping Range 4}), {Shipping Range 5}@row, "False")

  • Genevieve P.
    Genevieve P. Employee Admin

    Hi @Suzanne

    It sounds like the formula you're looking for is actually a JOIN(COLLECT formula. The reason I chose this one is that in case your range has more than one cell that Contains [1]@row, it will bring all of them back.

    Try something like this:

    =JOIN(COLLECT({Shipping Range 5}, {Shipping Range 4}, CONTAINS([1]@row, @cell)), ", ")



Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!