{"id":660,"date":"2025-02-07T08:06:18","date_gmt":"2025-02-07T07:06:18","guid":{"rendered":"https:\/\/moodytech.uk\/kb\/?post_type=knowledgebase&#038;p=660"},"modified":"2025-02-07T08:24:30","modified_gmt":"2025-02-07T07:24:30","slug":"vlookup-returns-n-a-error-due-to-formatting-issues","status":"publish","type":"knowledgebase","link":"https:\/\/moodytech.uk\/kb\/index\/excel\/vlookup-returns-n-a-error-due-to-formatting-issues\/","title":{"rendered":"VLOOKUP Returns #N\/A Error Due to Formatting Issues"},"content":{"rendered":"Formatting in Excel price lists sent by suppliers often causes VLOOKUP errors.<br\/><br \/>\r\nEditing each of the reference cells fixes this (F2 then Enter) but with multiple lines, this may not be practicable.<br\/><br \/>\r\n<a href=\"https:\/\/moodytech.uk\/kb\/wp-content\/uploads\/2025\/02\/2025-02-07-07_02_49-Settings.png\"><img loading=\"lazy\" decoding=\"async\" src=\"https:\/\/moodytech.uk\/kb\/wp-content\/uploads\/2025\/02\/2025-02-07-07_02_49-Settings.png\" alt=\"\" width=\"910\" height=\"100\" class=\"alignleft size-full wp-image-664\" srcset=\"https:\/\/moodytech.uk\/kb\/wp-content\/uploads\/2025\/02\/2025-02-07-07_02_49-Settings.png 910w, https:\/\/moodytech.uk\/kb\/wp-content\/uploads\/2025\/02\/2025-02-07-07_02_49-Settings-300x33.png 300w, https:\/\/moodytech.uk\/kb\/wp-content\/uploads\/2025\/02\/2025-02-07-07_02_49-Settings-768x84.png 768w\" sizes=\"auto, (max-width: 910px) 100vw, 910px\" \/><\/a><br\/><br \/>\r\n<strong>Example Formula Resulting in #N\/A Error<\/strong><br\/>\r\n=VLOOKUP(A2,SEITALS!A:I,2,FALSE)<br\/><br \/>\r\n<strong>Example Formula Returning the Correct Result<\/strong><br \/>\r\n=VLOOKUP(SUBSTITUTE(A2,CHAR(10),&#8221;&#8221;),SEITALS!$A:$I,2,FALSE)\r\n&nbsp;\r\n<hr \/>\r\nSource: <strong><a href=\"https:\/\/superuser.com\/questions\/1685147\/vlookup-wont-work-unless-text-is-retyped-manually-even-after-cleaning-and-trimm\" target=\"_blank\">https:\/\/superuser.com\/questions\/1685147\/vlookup-wont-work-unless-text-is-retyped-manually-even-after-cleaning-and-trimm<\/a><\/strong><br \/>\r\n<hr \/>\r\n<div class=\"noPrint align wp-block-ppb-print-page\" id='ppbPrintPage-1' data-attributes='{&quot;cId&quot;:&quot;fef71a47-5&quot;,&quot;alignment&quot;:&quot;left&quot;,&quot;isPrintSection&quot;:true,&quot;sectionSelector&quot;:&quot;.bpress-content-area&quot;,&quot;icon&quot;:{&quot;type&quot;:&quot;default&quot;,&quot;default&quot;:&quot;fiPrinter&quot;,&quot;url&quot;:&quot;&quot;},&quot;btnTypo&quot;:{&quot;fontSize&quot;:{&quot;desktop&quot;:16,&quot;tablet&quot;:20,&quot;mobile&quot;:18}},&quot;btnColors&quot;:{&quot;color&quot;:&quot;rgba(25, 91, 150, 1)&quot;,&quot;bg&quot;:&quot;#ffffff&quot;},&quot;btnBorder&quot;:{&quot;radius&quot;:&quot;5px&quot;,&quot;width&quot;:&quot;&quot;,&quot;color&quot;:&quot;#0693e3&quot;},&quot;align&quot;:&quot;&quot;,&quot;isModal&quot;:false,&quot;isIcon&quot;:true,&quot;isText&quot;:true,&quot;btnText&quot;:&quot;Print&quot;,&quot;btnPadding&quot;:{&quot;vertical&quot;:&quot;5px&quot;,&quot;horizontal&quot;:&quot;15px&quot;},&quot;btnShadow&quot;:[]}'>\r\n\t<style>\r\n\t\t#ppbPrintPage-1 .ppbPrintPage button{ font-weight: 400;font-size: 16px;font-style: normal;text-transform: none;text-decoration: auto;line-height: 135%;letter-spacing: 0px; } @media (max-width: 768px) { #ppbPrintPage-1 .ppbPrintPage button{ font-size: 20px; } } @media (max-width: 576px) { #ppbPrintPage-1 .ppbPrintPage button{ font-size: 18px; } }\r\n\t#ppbPrintPage-1 .ppbPrintPage{\r\n\t\ttext-align: left;\r\n\t}\r\n\t#ppbPrintPage-1 .ppbPrintPage button{\r\n\t\tcolor: rgba(25, 91, 150, 1);background: #ffffff;\r\n\t\tpadding: 5px 15px;\r\n\t\tborder-radius: 5px;\r\n\t\tbox-shadow: 0px 0px 0px 0px #7090b0 ;\r\n\t}\r\n\t<\/style>\r\n\r\n\t<div class='ppbPrintPage'>\r\n\t\t<button>\r\n\t\t\t<svg xmlns='http:\/\/www.w3.org\/2000\/svg' width='30' height='30' viewBox='0 0 24 24' strokeWidth='2' strokeLinejoin='round' stroke='currentColor' style='fill: none;'>\r\n\t\t\t\t<polyline points='6 9 6 2 18 2 18 9' \/>\r\n\t\t\t\t<path d='M6 18H4a2 2 0 0 1-2-2v-5a2 2 0 0 1 2-2h16a2 2 0 0 1 2 2v5a2 2 0 0 1-2 2h-2' \/>\r\n\t\t\t\t<rect x='6' y='14' width='12' height='8' \/>\r\n\t\t\t<\/svg>\t\r\n\r\n\t\t\t<span>Print<\/span>\t\t<\/button>\r\n\t<\/div>\r\n<\/div>","protected":false},"excerpt":{"rendered":"<p>Formatting in Excel price lists sent by suppliers often causes VLOOKUP errors. Editing each of the reference cells fixes this (F2 then Enter) but with multiple lines, this may not be practicable. Example Formula Resulting in #N\/A Error =VLOOKUP(A2,SEITALS!A:I,2,FALSE) Example<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","knowledgebase_cat":[45],"knowledgebase_tag":[200,112],"class_list":["post-660","knowledgebase","type-knowledgebase","status-publish","hentry","knowledgebase_cat-excel","knowledgebase_tag-n-a","knowledgebase_tag-vlookup"],"_links":{"self":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/660","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase"}],"about":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/types\/knowledgebase"}],"author":[{"embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/comments?post=660"}],"version-history":[{"count":10,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/660\/revisions"}],"predecessor-version":[{"id":673,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/660\/revisions\/673"}],"wp:attachment":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/media?parent=660"}],"wp:term":[{"taxonomy":"knowledgebase_cat","embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase_cat?post=660"},{"taxonomy":"knowledgebase_tag","embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase_tag?post=660"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}