Help w/IF function that puts a value in another cell

jmo
jmo ✭✭✭✭✭✭

Hi team - I have a sheet that has an column called ID that has specific alphanumeric entries:

I need an IF CONTAIN formula (maybe?) that looks to the first letter in the ID column and returns this value in a different column called Project:

  • IF A then Project A
  • IF B then Project B
  • IF C then Project C
  • IF D then Project D
  • and so on...........

I got 1 piece working as a test:

=IF(CONTAINS("B", ID:ID), "Project B")

But don't know how to string the other options together.


Any help would be greatly appreciated!

Best Answer

  • jmo
    jmo ✭✭✭✭✭✭
    Answer ✓

    I think we got the answer. This is what I’m using.


    =IF(CONTAINS("A", ID@row), "Project A", IF(CONTAINS("B", ID@row), "Project B", IF(CONTAINS("C", ID@row), "Project C", IF(CONTAINS("D", ID@row), "Project D"))))

Answers

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Are you wanting this output on a row by row basis, or are you wanting to (for example) string the projects together based on everything in the sheet such as "Project A, Project B, Project C"?

  • jmo
    jmo ✭✭✭✭✭✭

    On a row by row basis, @Paul Newcome. So if row 1 has B12 in the ID column then the Project column for that row needs to have Project B in it.

    And so on for any A, B, C, n references.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Ok. Try this:

    ="Project " + LEFT(ID@row)

  • jmo
    jmo ✭✭✭✭✭✭
    edited 04/04/22

    @Paul Newcome I tried to generalize the output but the real output needs to be the true name of the project. So B12 in the ID column would result in Improved Resiliency in the Project column. Each of the ABCn letters have discrete project names.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Will you always need to grab the text leading all the way up to (but not including) the first number digit, or are you actually typing in "A" and then wanting to reference a table?


    If the second and you have quite a few that can be ever expanding, I definitely recommend setting up a reference table and pulling from that.

  • jmo
    jmo ✭✭✭✭✭✭
    Answer ✓

    I think we got the answer. This is what I’m using.


    =IF(CONTAINS("A", ID@row), "Project A", IF(CONTAINS("B", ID@row), "Project B", IF(CONTAINS("C", ID@row), "Project C", IF(CONTAINS("D", ID@row), "Project D"))))

  • jmo
    jmo ✭✭✭✭✭✭
    edited 04/05/22

    Ok @Paul Newcome - I could use a bit of oversight, not sure what I am doing wrong at this point.

    This formula works for the first 4 letters (A-D):

    =IF(CONTAINS("A", ID@row), "Project A", IF(CONTAINS("B", ID@row), "Project B", IF(CONTAINS("C", ID@row), "Project C", IF(CONTAINS("D", ID@row), "Project D"))))

    When I try to add the other letters, I get the INCORRECT ARGUMENT SET error:

    =IF(CONTAINS("A", ID@row), "Project A", IF(CONTAINS("B", ID@row), "Project B", IF(CONTAINS("C", ID@row), "Project C", IF(CONTAINS("D", ID@row), "Project D"), =IF(CONTAINS("E", ID@row), "Project E", IF(CONTAINS("F", ID@row), "Project F", IF(CONTAINS("G", ID@row), "Project G", IF(CONTAINS("H", ID@row), "Project H")))))))

    Note: my first 2 rows (which have B in the ID column) return Project B - the other rows return the INCORRECT ARGUMENT SET error.

  • Paul Newcome
    Paul Newcome ✭✭✭✭✭✭

    Try removing the = from before the IF immediately following your "D" portion.


    This nested IF will provide results but can become rather bulky and difficult to maintain if there are ever any changes/additions/deletions. If it ever becomes unwieldy, feel free to revisit this thread and let me know or look through the Community posts for an INDEX/MATCH solution for referencing a table.

  • jmo
    jmo ✭✭✭✭✭✭

    This is what finally worked:

    =IF(CONTAINS("A", ID@row), "DD Calendaring", IF(CONTAINS("PPID", ID@row), "Optimize Internal Resources", IF(CONTAINS("B", ID@row), "DD Consortium", IF(CONTAINS("C", ID@row), "DD Standardization", IF(CONTAINS("D", ID@row), "E2E Process Redesign", IF(CONTAINS("E", ID@row), "RA Redesign", IF(CONTAINS("F", ID@row), "Tech Strategy", IF(CONTAINS("G", ID@row), "Op Model"))))))))


Help Article Resources

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!