If/Then Logic - Based on What Letter a Name Starts With

Options
ygoldgrab
ygoldgrab ✭✭✭✭✭
in Formulas and Functions

I am trying to create the following logic in Smartsheet

If Name starts with A through J, THEN Column A = Project Manager A

If Name starts with K through N, THEN Column A = Project Manager B.

Excel has this =CHOOSE(MATCH(UPPER(LEFT(A2)),{"A","K","O"}),"Project Manager A","Project Manager B","Project Manager C)


· Share on FacebookShare on Twitter

Best Answer

  • ygoldgrab
    ygoldgrab ✭✭✭✭✭
    Answer ✓
    Options

    Thanks @Genevieve P. , That would work but then I would have to hard code the whole ABC.

    I ended up using a match formula to reference another sheet where I had the ABC running down one column and referenced the range (in ABC List Range 1 from Letters A- J), if no match then I knew it would be Manager B. This worked great!

    =IFERROR(IF(MATCH(LEFT(fullname@row), {ABC List Range 1}, 0) > 0, "Manager A", ""), "Manager B")


    Thanks.

    · Share on FacebookShare on Twitter

Answers

  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Hi @ygoldgrab

    There may be a more succinct way of doing this, but what I would do is build out an IF statement that uses IF(OR to list all the possible options per-output.

    We can use LEFT(Name@row) to get the first letter, then check which one it is. So, if you were to look for A, B, and C, you could do this:

    =IF(OR(LEFT(Name@row) = "A", LEFT(Name@row) = "B", LEFT(Name@row) = "C"), "Project Manager A")


    You would then just need to list all the LEFT(Name@row) = for each of your options. Then you can move on to the next IF statement if it's none of those:


    =IF(OR(LEFT(Name@row) = "A", LEFT(Name@row) = "B", LEFT(Name@row) = "C"), "Project Manager A", IF(OR(LEFT(Name@row) = "K", LEFT(Name@row) = "L", LEFT(Name@row) = "M"), "Project Manager B"))

    Let me know if this makes sense!

    Cheers,

    Genevieve

    · Share on FacebookShare on Twitter
  • ygoldgrab
    ygoldgrab ✭✭✭✭✭
    Answer ✓
    Options

    Thanks @Genevieve P. , That would work but then I would have to hard code the whole ABC.

    I ended up using a match formula to reference another sheet where I had the ABC running down one column and referenced the range (in ABC List Range 1 from Letters A- J), if no match then I knew it would be Manager B. This worked great!

    =IFERROR(IF(MATCH(LEFT(fullname@row), {ABC List Range 1}, 0) > 0, "Manager A", ""), "Manager B")


    Thanks.

    · Share on FacebookShare on Twitter
  • Genevieve P.
    Genevieve P. Employee Admin
    Options

    Ah, that's a much cleaner way of doing it, great idea. Thanks for sharing!

    · Share on FacebookShare on Twitter

Help Article Resources

Create and edit formulas in Smartsheet

Formula combinations for cross sheet references

Smartsheet functions list

Categories

Want to practice working with formulas directly in Smartsheet?

Check out the Formula Handbook template!