{"id":277,"date":"2021-06-01T11:32:24","date_gmt":"2021-06-01T10:32:24","guid":{"rendered":"https:\/\/moodytech.uk\/kb\/?post_type=knowledgebase&#038;p=277"},"modified":"2024-08-08T14:00:53","modified_gmt":"2024-08-08T13:00:53","slug":"vlookup-errors-with-numerical-and-alphanumerical-data","status":"publish","type":"knowledgebase","link":"https:\/\/moodytech.uk\/kb\/index\/excel\/vlookup-errors-with-numerical-and-alphanumerical-data\/","title":{"rendered":"VLOOKUP Errors with Numerical and Alphanumerical Data"},"content":{"rendered":"=VLOOKUP(lookup_value,table_arrycol_index_num,range_lookup)<br><br>\r\n\r\nIf the <strong>lookup_value<\/strong> data contains a mix of numeric and alphanumeric values, the formula is likely to give a <strong>#N\/A<\/strong> result.<br><br>\r\n\r\nIf this is the case, use Excel&#8217;s <strong>Text to Columns<\/strong> (Data tab) function, leaving the delimiter TAB option enabled.<br><br>\r\n&nbsp;\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>=VLOOKUP(lookup_value,table_arrycol_index_num,range_lookup) If the lookup_value data contains a mix of numeric and alphanumeric values, the formula is likely to give a #N\/A result. If this is the case, use Excel&#8217;s Text to Columns (Data tab) function, leaving the delimiter TAB option<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"closed","ping_status":"closed","template":"","knowledgebase_cat":[45],"knowledgebase_tag":[],"class_list":["post-277","knowledgebase","type-knowledgebase","status-publish","hentry","knowledgebase_cat-excel"],"_links":{"self":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/277","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\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/comments?post=277"}],"version-history":[{"count":3,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/277\/revisions"}],"predecessor-version":[{"id":444,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase\/277\/revisions\/444"}],"wp:attachment":[{"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/media?parent=277"}],"wp:term":[{"taxonomy":"knowledgebase_cat","embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase_cat?post=277"},{"taxonomy":"knowledgebase_tag","embeddable":true,"href":"https:\/\/moodytech.uk\/kb\/wp-json\/wp\/v2\/knowledgebase_tag?post=277"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}