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!
Best 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
-
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"?
-
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.
-
Ok. Try this:
="Project " + LEFT(ID@row)
-
@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.
-
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.
-
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"))))
-
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.
-
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.
-
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
Categories
- All Categories
- 14 Welcome to the Community
- Customer Resources
- 66K Get Help
- 429 Global Discussions
- 149 Industry Talk
- 488 Announcements
- 5.2K Ideas & Feature Requests
- 85 Brandfolder
- 152 Just for fun
- 74 Community Job Board
- 499 Show & Tell
- 33 Member Spotlight
- 2 SmartStories
- 305 Events
- 36 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!