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
Check out the Formula Handbook template!