{"id":138,"date":"2018-12-27T18:14:07","date_gmt":"2018-12-27T18:14:07","guid":{"rendered":"https:\/\/www.howtoexcel.info\/Excel\/?p=138"},"modified":"2021-09-29T06:01:41","modified_gmt":"2021-09-29T06:01:41","slug":"vlookup-problem","status":"publish","type":"post","link":"https:\/\/www.howtoexcel.info\/Excel\/vlookup-problem\/","title":{"rendered":"How to correct #NA error in vlookup function &#8211; MS Excel"},"content":{"rendered":"\r\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\">This article aims to provide solution for one of the most common problems with vlookup. Vlookup does not work properly when source and destination data both occur in the same workbook or worksheet. This means, for vlookup to work properly, you should use two different workbooks (excel files) for\u00a0 source and destination. Let&#8217;s see why.<\/span><\/p>\r\n<p><span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.howtoexcel.info\/vlookup.xlsx\"> Download sample vlookup file<\/a><\/span><\/p>\r\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400; color: #000000;\">Following example illustrates #NA error due to the above mentioned issue (refer rows 7 and 9)<\/span><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.howtoexcel.info\/Images\/vlookup\/vlookup01.jpg\" alt=\"\" \/><\/figure>\r\n\r\n\r\n\r\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\"> The problem here is <span lang=\"en-gb\">that<\/span> the selected range<span lang=\"en-gb\"> of source cells keep c<\/span>hanging when you drag\u00a0the formula\u00a0down<span lang=\"en-gb\"> (while trying\u00a0<\/span>to copy it to\u00a0other cells\u00a0in the column<span lang=\"en-gb\">).<\/span> <span lang=\"en-gb\">Check the following<\/span> image <span lang=\"en-gb\">which illustrates that<\/span> the range will be different for each cell in column D.<\/span><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.howtoexcel.info\/Images\/vlookup\/vlookup02.jpg\" alt=\"\" \/><\/figure>\r\n\r\n\r\n\r\n<p style=\"text-align: justify;\"><span style=\"color: #000000;\"> To overcome this, you just need to make the range to a constant. It can be done by adding $ sign before and after the cell reference letter (here B and D in this example).<\/span><\/p>\r\n\r\n\r\n\r\n<figure class=\"wp-block-image\"><img decoding=\"async\" src=\"https:\/\/www.howtoexcel.info\/Images\/vlookup\/vlookup03.jpg\" alt=\"\" \/><\/figure>\r\n\r\n\r\n\r\n<p style=\"text-align: justify;\"><span style=\"font-weight: 400; color: #000000;\">You can also download the excel sheet for your reference by clicking <span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.howtoexcel.info\/vlookup.xlsx\">here<\/a><\/span>. If you could not find a solution to the problem, we recommend that you go through the following websites for a solution.<\/span><\/p>\r\n<p><span style=\"color: #000000;\"><strong>External links<\/strong><\/span><\/p>\r\n<p><span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/support.office.com\/en-ie\/article\/How-to-correct-a-N-A-error-in-the-VLOOKUP-function-e037d763-ffc3-4fae-a909-89c482d389b2?ui=en-US&amp;rs=en-IE&amp;ad=IE\"> How to correct a #N\/A error in the VLOOKUP function <\/a><\/span><\/p>\r\n<p><span style=\"color: #0000ff;\"><a style=\"color: #0000ff;\" href=\"https:\/\/www.ablebits.com\/office-addins-blog\/2014\/08\/27\/excel-vlookup-not-working\/\"> Excel VLOOKUP not working &#8211; solutions for N\/A, NAME and VALUE errors<\/a><\/span><\/p>\r\n<p><a href=\"https:\/\/officetricks.com\/remove-na-excel-error-vlookup\/\"><span style=\"color: #0000ff;\"> Remove #N\/A Excel Error \u2013 Replace #N\/A in VLOOKUP with Valid Value<\/span><\/a><\/p>\r\n","protected":false},"excerpt":{"rendered":"","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":[],"categories":[1],"tags":[],"_links":{"self":[{"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/posts\/138"}],"collection":[{"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/comments?post=138"}],"version-history":[{"count":12,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/posts\/138\/revisions"}],"predecessor-version":[{"id":264,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/posts\/138\/revisions\/264"}],"wp:attachment":[{"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/media?parent=138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/categories?post=138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.howtoexcel.info\/Excel\/wp-json\/wp\/v2\/tags?post=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}