IF/VLOOK Formula

I have the formula below and it return me with #UNPARSEABLE error. Need some guide to see what is the issue with the formula. Thanks

Originally formula as below but it return with #NOMATCH value if the requirement is not been found

=IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false))

So I change as below but get #UNPARSEABLE to change #NOMATCH to blank

=IFERROR(IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false), false))), " ")

Find more posts tagged with

Accepted answers

All comments

KPH

Short answer

You added some extra text.

This was your formula

=IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false))

As per our earlier conversations, you meant to add this to the start

IFERROR(

and this to the end

, " ")

But you added this part in bold as well

=IFERROR(IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false), false))), " ")

Long explanation - this will help you write and troubleshoot your own formula

If you look at the formula you will see you have more closing parenthesis than opening parenthesis. In smartsheets you can see the color coding of the pairs. Those two black ones at the end indicate a problem. There are no black opening parentheses.

As soon as you start removing them, the smartsheet helper box pops up to tell you where in the formula you are.

I deleted the blue and black one after the second false and this then appeared.

So now I know my cursor (you can see it in pink, above) is in the "value if there is no error" section of the formula. When I move my cursor to the right of the comma (it is there in black, below) this changes. So now I know I am in the section of the formula where the value if there is an error is defined. This is good.

The formula still isn't correct though. The error message is now INVALID COLUMN VALUE. The helper boxes can help you identify which value is a problem.

If I place my cursor on the last false, like this:

I can see this is the value to return if the IF statement is false. That value therefore needs to be in quotation marks.

This formula then works

=IFERROR(IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false), "false"), " ")

However, knowing what you were trying to do, I don't think you want to return "false" if the Category Manager Approval Required cell is not YES and probably want this to be blank, so I removed the false value entirely.

This is the resulting formula

=IFERROR(IF([Category Manager Approval Required]@row = "YES", VLOOKUP(Category@row, {Category - Commodity}, 9, false)), " ")

HZAR

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.