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

Suzanne

=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

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

Suzanne

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.

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

Cheers,

Genevieve