VLOOKUP returning #INVALID REF
我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)
我f 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.
我hope that makes some sense, any help is always very much appreciated. Thank you!
Best Answer
-
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].
Answers
-
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哇,非常感谢它完美地!我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.
=我FERROR(VLOOKUP(.....), "")
-
Camie Keuck ✭✭✭
Hi again Paul
我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 NewcomeThank 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...
=我FERROR(original_formula, "NA")
=我FERROR(我NDEX([DUPPS PN]:[DUPPS PN], MATCH([CLAPPER PART NUMBER]@row, [CLAPPER PN]:[CLAPPER PN], 0)), "NA")
Help Article Resources
Categories
Check out theFormula Handbook template!
Instead of applying the formula to \"Multiselect Text String\" row, did you tried with \"Multiselect Values\" row?<\/p>
=IF(HAS([Multiselect Values]@row, [Component ID]@row), \"MATCH\", \"NO MATCH\")<\/p>
Thank you,<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[]},{"discussionID":109493,"type":"question","name":"I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","excerpt":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5 different statuses in a separate column. So using the screenshot I've provided as an example (although it doesn't have 5 different statuses in the…","snippet":"Hello, I am attempting to come up with a sheet summary formula that counts cells if they meet at least one of 3 different statuses in the same column, AND also meet one of 5…","categoryID":322,"dateInserted":"2023-08-25T20:03:21+00:00","dateUpdated":null,"dateLastComment":"2023-08-26T00:34:49+00:00","insertUserID":165710,"insertUser":{"userID":165710,"name":"SmarsheetNewb","url":"https:\/\/community.smartsheet.com\/profile\/SmarsheetNewb","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/defaultavatar\/nWRMFRX6I99I6.jpg","dateLastActive":"2023-08-26T00:33:27+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":null,"lastUserID":161714,"lastUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3386005690,"url":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109493\/i-am-having-trouble-using-and-or-countif-s-to-build-a-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109493,"commentID":392692,"name":"Re: I am having trouble using \"And\", \"OR\" & \"Countif(s)\" to build a formula.","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392692#Comment_392692","dateInserted":"2023-08-26T00:34:49+00:00","insertUserID":161714,"insertUser":{"userID":161714,"name":"Carson Penticuff","url":"https:\/\/community.smartsheet.com\/profile\/Carson%20Penticuff","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/B0Q390EZX8XK\/nBGT0U1689CN6.jpg","dateLastActive":"2023-08-26T01:04:51+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-26T00:33:25+00:00","dateAnswered":"2023-08-25T20:44:12+00:00","acceptedAnswers":[{"commentID":392662,"body":"
Try this:<\/p>
=COUNTIFS([Item Number]:[Item Number], OR(@cell = \"C001\", @cell = \"COO2\", @cell = \"COO3\", @cell = \"COO4\"), [Status]:[Status], OR(@cell = \"Green\", @cell = \"Yellow\", @cell = \"Red\"))<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":322,"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions","allowedDiscussionTypes":[]},"reactions":[{"tagID":3,"urlcode":"Promote","name":"Promote","class":"Positive","hasReacted":false,"reactionValue":5,"count":0},{"tagID":5,"urlcode":"Insightful","name":"Insightful","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":11,"urlcode":"Up","name":"Vote Up","class":"Positive","hasReacted":false,"reactionValue":1,"count":0},{"tagID":13,"urlcode":"Awesome","name":"Awesome","class":"Positive","hasReacted":false,"reactionValue":1,"count":0}],"tags":[{"tagID":254,"urlcode":"formulas","name":"Formulas"}]},{"discussionID":109474,"type":"question","name":"Help with date calculation formula","excerpt":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the resolution date is blank I want it to use the current date in the calculation to see how long this issue has gone unresolved. Any help is much…","snippet":"Hello, I'm trying to find a formula that will help me calculate how long an intake took to resolve. The rows I need to be calculated are Date Reported & Resolution Date. If the…","categoryID":322,"dateInserted":"2023-08-25T16:29:39+00:00","dateUpdated":"2023-08-25T16:29:59+00:00","dateLastComment":"2023-08-25T23:01:30+00:00","insertUserID":165688,"insertUser":{"userID":165688,"name":"Nwest","title":"Systems Analyst","url":"https:\/\/community.smartsheet.com\/profile\/Nwest","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!ukHVZ18ImX4!BcjWAe8S9SY!l7iQo_PZHOx","dateLastActive":"2023-08-25T17:22:30+00:00","banned":0,"punished":0,"private":false,"label":"✭"},"updateUserID":165688,"lastUserID":8888,"lastUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":3,"countViews":23,"score":null,"hot":3385987269,"url":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109474\/help-with-date-calculation-formula","format":"Rich","tagIDs":[254],"lastPost":{"discussionID":109474,"commentID":392687,"name":"Re: Help with date calculation formula","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/392687#Comment_392687","dateInserted":"2023-08-25T23:01:30+00:00","insertUserID":8888,"insertUser":{"userID":8888,"name":"Andrée Starå","title":"Smartsheet Expert Consultant & Partner | Workflow Consultant \/ CEO @ WORK BOLD","url":"https:\/\/community.smartsheet.com\/profile\/Andr%C3%A9e%20Star%C3%A5","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/0PAU3GBYQLBT\/nXWM7QXGD6464.jpg","dateLastActive":"2023-08-26T17:06:33+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Formulas and Functions","url":"https:\/\/community.smartsheet.com\/categories\/formulas-and-functions"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-25T17:04:22+00:00","dateAnswered":"2023-08-25T16:36:59+00:00","acceptedAnswers":[{"commentID":392622,"body":"