VLOOKUP returning #INVALID REF

I am receiving the #INVALID REF error in smartsheet and I am not sure how to correct it.

=VLOOKUP([PART NUMBER]1, [PN]1:[PART DESCRIPTION]1800, {2,3,4,5}, false)

Below is the excel sheet formula that does work but does not flow over to the smartsheet.

=VLOOKUP($Q6,$A4:$D25014,{2,3,4,5},FALSE)

If you look at the excel spreadsheet screen shot below, what I want is a formula entered into the PART NUMBER column where if you enter a number from the PN column into the COLUMN A column it will return the value of the OTHER PN column.

I hope that makes some sense, any help is always very much appreciated. Thank you!

Find more posts tagged with

Formulas

Importing and Exporting

Accepted answers

Paul Newcome

There are 2 problems with your first one.

First: @row is case sensitive and needs to be all lower case.

Second: When your column name contains a space, special character, or number, it needs to be wrapped in square brackets [ ] the same way you have [Clapper Part Number].

All comments

Paul Newcome

Try something along the lines of...

=INDEX([Other PN]:[Other PN], MATCH([Column A]@row, PN:PN, 0))

Camie Keuck

Hmm, I also had tried to use the index and match but it still doesn't seem to be working for me with that formula either. Now the error I get is #UNPARSEABLE

Paul Newcome

Can you copy/paste the exact formula directly from the sheet to here?

Camie Keuck

The screenshot is a "dummy" sheet but here are my actual formulas:

#UNPARSEABLE

=INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@ROW, CLAPPER PN:CLAPPER PN, 0))

#INVALID REF

=VLOOKUP([CLAPPER PART NUMBER]4, [CLAPPER PN]4:[EPICOR PART DESCRIPTION]1800, {2,3,4,5}, false)

Paul Newcome

There are 2 problems with your first one.

First: @row is case sensitive and needs to be all lower case.

Second: When your column name contains a space, special character, or number, it needs to be wrapped in square brackets [ ] the same way you have [Clapper Part Number].

Camie Keuck

@Paul Newcome , wow thank you so very much it works perfectly! I hadn't realized @row was case sensitive, I did question the brackets at the end but wanted to copy your example. I really cannot thank you enough for your help 🤩

Paul Newcome

Happy to help. 👍️

The reason I did not use square brackets in my example is because I was using the column descriptors in your screenshot. PN as a column name would not need square brackets because it does not have any of the previously mentioned reasons for them.

Camie Keuck

May I piggyback and ask where I could input IFERROR? Some of the cells are blank...sorry should have thought of that first!

Camie Keuck

And yes that makes sense as to why you did not use brackets. I am learning so much here so thank you again!

Paul Newcome

Wrap the whole thing in the first section of the IFERROR.

=IFERROR(VLOOKUP(.....), "")

Camie Keuck

Hi again Paul

I wrapped the formula but now it comes back with #INCORRECT ARGUMENT SET, do you see any errors with my new formula?

=IFERROR(INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0)))

Paul Newcome

Yes. You forgot the output for the IFERROR statement. Notice in my previous post that there is a comma and a double set of quotes between the end of the VLOOKUP and the end of the IFERROR? This outputs a blank if there is an error with the VLOOKUP. If you wanted specific text or something else, you would just put it there in that second portion of the IFERROR, but you do need to have at lease something there.

Camie Keuck

@Paul Newcome Thank you for pointing that out! The formula I am using now is this.

=IFERROR(INDEX([DUPPS PN]:[DUPPS PN], "NA"), MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0))

From what I understand from your explanation the value should return NA, correct? The value comes over as the row number, i.e. the first error was found on row 428, so the value returned is 428.

Paul Newcome

No. You want something more like this...

=IFERROR(original_formula, "NA")

=IFERROR(INDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0)), "NA")

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, sort, discussion, discussion/name, tags, breadcrumbs, discussionApiParams, serverDraftID, serverDraft.