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!