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!
Best Answer
-
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].
Answers
-
Try something along the lines of...
=INDEX([Other PN]:[Other PN], MATCH([Column A]@row, PN:PN, 0))
-
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
-
Can you copy/paste the exact formula directly from the sheet to here?
-
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)
-
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].
-
@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 🤩
-
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.
-
May I piggyback and ask where I could input IFERROR? Some of the cells are blank...sorry should have thought of that first!
-
And yes that makes sense as to why you did not use brackets. I am learning so much here so thank you again!
-
Wrap the whole thing in the first section of the IFERROR.
=IFERROR(VLOOKUP(.....), "")
-
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)))
-
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.
-
@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.
-
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")
Help Article Resources
Categories
Check out the Formula Handbook template!