Trying to apply conditional assignments to a column based on either a formula or VLOOKUP

MrRighty

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)

Jon Mark H

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

Jon Mark H

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)

MrRighty

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?