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([email protected] = "SM", "Eric", 

IF(AND([Process Area]@row = "Customer Service", [email protected] = "ABC"), "Amanda", "N/A"),

IF(AND([Process Area]@row = "Customer Service", [email protected] = "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,"-",[email protected]), {GCC Power User Lookup}, [Power User]@row, true)

Answers

  • Jon Mark H
    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([email protected] = "SM", "Eric", IF(AND([Process Area]@row = "Customer Service", [email protected] = "ABC"), "Amanda", IF(AND([Process Area]@row = "Customer Service", [email protected] = "GHX"), "Kristin", "N/A")))

    Let me know if that fixes your issue, or if you need any additional assistance!


    -Jon Mark

  • Jon Mark H
    Jon Mark H ✭✭✭✭

    Your second attempt solution is this:

    Smartsheet doesn't use CONCATENATE - merely + the items together:

    =VLOOKUP(([Process Area]@row + "-" + [email protected]), {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
    MrRighty
    edited 05/02/22

    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?