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!

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

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!

Find more posts tagged with

Accepted answers

jmo

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

All comments

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

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

Ok. Try this:

="Project " + LEFT(ID@row)

jmo

@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

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

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

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

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

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

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, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.