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)

Find more posts tagged with

Accepted answers

All comments

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?

Sign in to join the conversation!

Quick Links

Component react.discussion.discussions had an error.

ref must be one of: categoryID, siteSectionID, category, category/categoryID, category/name, category/description, category/url, category/allowedDiscussionTypes, locale, siteSection, siteSection/basePath, siteSection/contentLocale, siteSection/sectionGroup, siteSection/sectionID, siteSection/name, siteSection/description, siteSection/apps, siteSection/attributes, layoutViewType, discussionID, commentID, page, latest, sort, sortType, lookupSort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.