VLOOKUP #笔名atch has me completely stumped
I have a vlookup formula in my sheet (Project Plan) that references another sheet (Phone List). Despite triple checking formulas, spelling, spaces, etc. I could not get the formula to return anything by #No Match. I copied and pasted the original sheet (Phone List) that's referenced into a new sheet (Copy of Phone List) and now the formula works. I really don't want to have two separate sheets with the exact same information. Please help me figure out why the formula in Project Plan doesn't work when I reference Phone List! Thank you! (The only thing I can think of is I only have View permission on the original phone list.)
Answers
-
coby ✭✭✭✭Have you tried setting the 'match_type' to FALSE? often when I'm getting issues like that, setting the match-type to false fixes the issue.
-
Thank you for the suggestion! The match type is already set to false.
-
Paul Newcome ✭✭✭✭✭✭Double check that your ranges are the same for both formulas.
-
I double checked and they are the same width (include same number of columns) and reference the same column for the return value.
-
Paul Newcome ✭✭✭✭✭✭
If everything is completely identical including ranges and whatnot and it is still not working properly, I would suggest reaching out to support.
-
Genevieve P. Employee AdminIt looks like your formula is working when the content you're matching is in thePrimary Column. Can you adjust your first sheet to have the Primary Column be the "Position & Division" column?
-
Thanks, everyone, for your help! I just figured it out. The formula used to create the reference call in the master phone list joins a text column with a dropdown column whereas the copy of the phone list joins a text column with a text column (as does the sheet with the vlookup). Although the result of the formula is text, it still caused issues. So strange! I don't understand why that's the case. I did a copy/paste values from the Phone List and copy/paste values from the Copy Phone List and then did an if formula to see if the pasted values are the same and they are. Despite that, the problem occurred from the Phone List formula joining a dropdown field rather than text field.
Categories
Glad to hear that it worked for your present use-case. <\/p>
✅Remember! <\/strong>Did my post help or answer your question or solve your problem? Please help the Community by <\/em>marking it as the accepted answer\/helpful<\/em><\/strong>. It will make it easier for others to find a solution or help to answer!<\/em><\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"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":109291,"type":"question","name":"How do I make a global sheet\/report\/dashboard name change","excerpt":"I use Control Center to provision new projects. How do I globally change sheet\/report\/dashboard names' suffix after a project has been provisioned and is underway? For example, if the suffix was \"Enterprise Business Unit\", and now I want it to be \"Enterprise BU\".","snippet":"I use Control Center to provision new projects. How do I globally change sheet\/report\/dashboard names' suffix after a project has been provisioned and is underway? For example, if…","categoryID":343,"dateInserted":"2023-08-22T18:03:46+00:00","dateUpdated":null,"dateLastComment":"2023-08-22T19:36:01+00:00","insertUserID":149249,"insertUser":{"userID":149249,"name":"ShannonTozier","url":"https:\/\/community.smartsheet.com\/profile\/ShannonTozier","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!pxvQ6TiroDw!HCDQ32y7iGY!YRwAmRkBQEG","dateLastActive":"2023-08-22T19:51:10+00:00","banned":0,"punished":0,"private":false,"label":"✭✭"},"updateUserID":null,"lastUserID":45516,"lastUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-23T21:34:21+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭✭✭✭"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":1,"countViews":24,"score":null,"hot":3385460987,"url":"https:\/\/community.smartsheet.com\/discussion\/109291\/how-do-i-make-a-global-sheet-report-dashboard-name-change","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109291\/how-do-i-make-a-global-sheet-report-dashboard-name-change","format":"Rich","tagIDs":[466,582],"lastPost":{"discussionID":109291,"commentID":391983,"name":"Re: How do I make a global sheet\/report\/dashboard name change","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391983#Comment_391983","dateInserted":"2023-08-22T19:36:01+00:00","insertUserID":45516,"insertUser":{"userID":45516,"name":"Paul Newcome","title":"","url":"https:\/\/community.smartsheet.com\/profile\/Paul%20Newcome","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/082\/nQPUTVFKKWDJ2.jpg","dateLastActive":"2023-08-23T21:34:21+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":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-22T19:51:29+00:00","dateAnswered":"2023-08-22T19:36:01+00:00","acceptedAnswers":[{"commentID":391983,"body":" There is no way to do that through Global Updates. You will need to either do it manually or through the API.<\/p>"}]}},"status":{"statusID":3,"name":"Accepted","state":"closed","recordType":"discussion","recordSubType":"question"},"bookmarked":false,"unread":false,"category":{"categoryID":343,"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations","allowedDiscussionTypes":["discussion","question"]},"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":466,"urlcode":"control-center","name":"Control Center"},{"tagID":582,"urlcode":"global-updates","name":"global updates"}]},{"discussionID":109130,"type":"question","name":"//www.santa-greenland.com/community/discussion/77503/\"Get Sheet\" in Bridge not working","excerpt":"Ok I scrubbed the forum and can't find an answer. I have Bridge workflow that calls \"Get Sheet\" twice. She image. The second is not providing an output, but the Run complete successfully. I specified the SheetID, I am the owner of that sheet.","snippet":"Ok I scrubbed the forum and can't find an answer. I have Bridge workflow that calls \"Get Sheet\" twice. She image. The second is not providing an output, but the Run complete…","categoryID":343,"dateInserted":"2023-08-18T02:50:31+00:00","dateUpdated":null,"dateLastComment":"2023-08-21T08:21:37+00:00","insertUserID":148386,"insertUser":{"userID":148386,"name":"dsmitts.jay","url":"https:\/\/community.smartsheet.com\/profile\/dsmitts.jay","photoUrl":"https:\/\/aws.smartsheet.com\/storageProxy\/image\/images\/u!1!Asq033UutIg!DAu0A77Sx_w!NYd7SSBcGwv","dateLastActive":"2023-08-20T18:41:52+00:00","banned":0,"punished":0,"private":false,"label":"✭✭✭"},"updateUserID":null,"lastUserID":91566,"lastUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-23T18:16:42+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"},"pinned":false,"pinLocation":null,"closed":false,"sink":false,"countComments":5,"countViews":49,"score":null,"hot":3384936128,"url":"https:\/\/community.smartsheet.com\/discussion\/109130\/get-sheet-in-bridge-not-working","canonicalUrl":"https:\/\/community.smartsheet.com\/discussion\/109130\/get-sheet-in-bridge-not-working","format":"Rich","lastPost":{"discussionID":109130,"commentID":391563,"name":"Re: \"Get Sheet\" in Bridge not working","url":"https:\/\/community.smartsheet.com\/discussion\/comment\/391563#Comment_391563","dateInserted":"2023-08-21T08:21:37+00:00","insertUserID":91566,"insertUser":{"userID":91566,"name":"Genevieve P.","title":"Community Manager","url":"https:\/\/community.smartsheet.com\/profile\/Genevieve%20P.","photoUrl":"https:\/\/us.v-cdn.net\/6031209\/uploads\/userpics\/KHY4Y67W0VRX\/nF76D5N9MFB28.png","dateLastActive":"2023-08-23T18:16:42+00:00","banned":0,"punished":0,"private":false,"label":"Employee Admin"}},"breadcrumbs":[{"name":"Home","url":"https:\/\/community.smartsheet.com\/"},{"name":"Get Help","url":"https:\/\/community.smartsheet.com\/categories\/get-help"},{"name":"Add Ons and Integrations","url":"https:\/\/community.smartsheet.com\/categories\/apps-and-integrations"}],"groupID":null,"statusID":3,"image":{"url":"https:\/\/us.v-cdn.net\/6031209\/uploads\/2USQVMXJ1B6S\/image.png","urlSrcSet":{"10":"","300":"","800":"","1200":"","1600":""},"alt":"image.png"},"attributes":{"question":{"status":"accepted","dateAccepted":"2023-08-21T08:21:28+00:00","dateAnswered":"2023-08-18T16:40:11+00:00","acceptedAnswers":[{"commentID":391426,"body":"
