Trying to apply conditional assignments to a column based on either a formula or VLOOKUP
There must be something I'm missing. t seems like either of the following approaches should work but don't.
I'm trying to auto assign a value within a specified column ("Power User") based on the values captured in two other columns ("Process Area" and "System") within the same row. Here's a screenshot of the validation list I'm working from.
First, I tried the following series of nested IF statements along the following lines...
=IF(System@row = "SM", "Eric",
IF(AND([Process Area]@row = "Customer Service", System@row = "ABC"), "Amanda", "N/A"),
IF(AND([Process Area]@row = "Customer Service", System@row = "GHX"), "Kristin", "N/A"),
I can get the first two lines of the formula to work, but whenever I try to include more than one IF(AND clause in the formula, a #UNPARSABLE error is returned.
My next attempt involved creating a lookup table in which all Process Area and System combinations are concatenated in the first column, as shown in the screenshot below:
I created the following formula, which seems to be reference the lookup table appropriately, but again Smartsheet returns an #UNPARSABLE error.
=VLOOKUP(CONCATENATE([Process Area]@row,"-",System@row), {GCC Power User Lookup}, [Power User]@row, true)
Answers
-
Hey @MrRighty !
Your first attempt is not working as you're closing your IF statement too early - each subsequent entry of a nested IF statement must either be part of the True result of the statements, or the False result of the statements-
i.e. - delete this part from your second statement: "N/A"),
This will cause your conditions to check for something to be assigned to Kristin to run if the checks for Eric & Amanda were false.
So your formula must look like this:
=IF(System@row = "SM", "Eric", IF(AND([Process Area]@row = "Customer Service", System@row = "ABC"), "Amanda", IF(AND([Process Area]@row = "Customer Service", System@row = "GHX"), "Kristin", "N/A")))
Let me know if that fixes your issue, or if you need any additional assistance!
-Jon Mark
-
Your second attempt solution is this:
Smartsheet doesn't use CONCATENATE - merely + the items together:
=VLOOKUP(([Process Area]@row + "-" + System@row), {GCC Power User Lookup}, 3, false)
(I put 3 for the column reference #, but make sure it matches the Column count number of Power User, counting from the "Business Process Area, System" column.
Also, I highly recommend always using "false" in VLOOKUPS - to get an exact match)
-
Thanks for the quick response. Using VLOOKUP seems like the most practical solution, but I'm still getting an #UNPARSABLE error despite applying suggested changes to the formula (revised version captured below):
=VLOOKUP([Process Area]@row +": " + [Primary System Impacted]@row), {GCC Power User Lookup}, 4, false)
Is there a way to get more insight into what aspects of the formula or results returned are problematic?
Help Article Resources
Categories
- All Categories
- 14 Welcome to the Community
- Smartsheet Customer Resources
- 62.9K Get Help
- 379 Global Discussions
- 210 Industry Talk
- 441 Announcements
- 4.5K Ideas & Feature Requests
- 139 Brandfolder
- 129 Just for fun
- 130 Community Job Board
- 449 Show & Tell
- 30 Member Spotlight
- 1 SmartStories
- 300 Events
- 33 Webinars
- 7.3K Forum Archives
Check out the Formula Handbook template!