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

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)

Find more posts tagged with

Accepted answers

ygoldgrab

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.

All comments

Genevieve P.

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

ygoldgrab

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

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

Thanks.

Genevieve P.

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

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.