IF (Contains... returning first matched reference

Options
AnemonePoppy
AnemonePoppy ✭✭✭
edited 09/20/22 in Formulas and Functions

I have a formula to help apply a specific date to a column when a specific field is chosen (Block 1 = 1/27/23, Block 2 = 2/24/23...) this works just fine until I get to Block 10, 11 and 12; it is only returning the value of Block 1 Example Block 10 is giving me 1/27/23 when it should be 10/20/23

=IF(CONTAINS("Block 1", [TRAUMA Blocks]@row), "01/27/2023",

IF(CONTAINS("Block 2", [TRAUMA Blocks]@row), "02/24/2023",

IF(CONTAINS("Block 3", [TRAUMA Blocks]@row), "03/24/2023",

.

.

IF(CONTAINS("Block 10", [TRAUMA Blocks]@row), "10/20/2023",

IF(CONTAINS("Block 11", [TRAUMA Blocks]@row), "11/17/2023",

IF(CONTAINS("Block 12", [TRAUMA Blocks]@row), "12/23/2023","")))))


EDIT FIXED!: I learned about HAS. I edited it to be specific of all Text in Block 1 field so it looks for specific text and not just references.

IF(HAS("Block 1 - January 2- January 27", [TRAUMA Blocks]@row), "01/27/2023",

Answers

  • Kelly Moore
    Kelly Moore ✭✭✭✭✭✭
    Options

    Hey @AnemonePoppy

    Yes, you are right in the difference between HAS and CONTAINS. These functions are used when you are trying to find your term within a longer text string or multi select dropdown column. As a build, if your [Trauma Blocks] only had the word Block 1, Block 2, etc in the cell (like in a single select dropdown) you wouldn't need either CONTAINS or HAS.

    =IF([TRAUMA Blocks]@row="Block 1", "01/27/2023", IF([TRAUMA Blocks]@row="Block 2", "02/24/2023", IF([TRAUMA Blocks]@row="Block 3", ...

    cheers

    Kelly

Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!