{"id":1658,"date":"2022-02-07T19:59:41","date_gmt":"2022-02-07T11:59:41","guid":{"rendered":"https:\/\/www.fyndpro.com\/?p=1658"},"modified":"2026-08-27T16:14:39","modified_gmt":"2026-08-27T08:14:39","slug":"vlookup-error-reasons","status":"publish","type":"post","link":"https:\/\/www.fyndpro.com\/en\/vlookup-error-reasons\/","title":{"rendered":"4 Common Causes of VLOOKUP Errors"},"content":{"rendered":"<p class=\"wp-block-paragraph\">VLOOKUP, one of Excel's most popular functions, is used frequently but often returns errors (even though a manual lookup would succeed). Why? Here we break down several common causes of VLOOKUP errors.<\/p>\n\n\n\n<p class=\"wp-block-paragraph\">\t\t\t\t\t\t<style>\r\n\t\t\t\t<style>\r\n#wpsm_accordion_1649 .wpsm_panel-heading{\r\n\tpadding:0px !important;\r\n}\r\n#wpsm_accordion_1649 .wpsm_panel-title {\r\n\tmargin:0px !important; \r\n\ttext-transform:none !important;\r\n\tline-height: 1 !important;\r\n}\r\n#wpsm_accordion_1649 .wpsm_panel-title a{\r\n\ttext-decoration:none;\r\n\toverflow:hidden;\r\n\tdisplay:block;\r\n\tpadding:0px;\r\n\tfont-size: 18px !important;\r\n\tfont-family: Open Sans !important;\r\n\tcolor:#000000 !important;\r\n\tborder-bottom:0px !important;\r\n}\r\n\r\n#wpsm_accordion_1649 .wpsm_panel-title a:focus {\r\noutline: 0px !important;\r\n}\r\n\r\n#wpsm_accordion_1649 .wpsm_panel-title a:hover, #wpsm_accordion_1649 .wpsm_panel-title a:focus {\r\n\tcolor:#000000 !important;\r\n}\r\n#wpsm_accordion_1649 .acc-a{\r\n\tcolor: #000000 !important;\r\n\tbackground-color:#e8e8e8 !important;\r\n\tborder-color: #ddd;\r\n}\r\n#wpsm_accordion_1649 .wpsm_panel-default > .wpsm_panel-heading{\r\n\tcolor: #000000 !important;\r\n\tbackground-color: #e8e8e8 !important;\r\n\tborder-color: #e8e8e8 !important;\r\n\tborder-top-left-radius: 0px;\r\n\tborder-top-right-radius: 0px;\r\n}\r\n#wpsm_accordion_1649 .wpsm_panel-default {\r\n\t\tborder:1px solid transparent !important;\r\n\t}\r\n#wpsm_accordion_1649 {\r\n\tmargin-bottom: 20px;\r\n\toverflow: hidden;\r\n\tfloat: none;\r\n\twidth: 100%;\r\n\tdisplay: block;\r\n}\r\n#wpsm_accordion_1649 .ac_title_class{\r\n\tdisplay: block;\r\n\tpadding-top: 12px;\r\n\tpadding-bottom: 12px;\r\n\tpadding-left: 15px;\r\n\tpadding-right: 15px;\r\n}\r\n#wpsm_accordion_1649  .wpsm_panel {\r\n\toverflow:hidden;\r\n\t-webkit-box-shadow: 0 0px 0px rgba(0, 0, 0, .05);\r\n\tbox-shadow: 0 0px 0px rgba(0, 0, 0, .05);\r\n\t\tborder-radius: 4px;\r\n\t}\r\n#wpsm_accordion_1649  .wpsm_panel + .wpsm_panel {\r\n\t\tmargin-top: 5px;\r\n\t}\r\n#wpsm_accordion_1649  .wpsm_panel-body{\r\n\tbackground-color:#ffffff !important;\r\n\tcolor:#000000 !important;\r\n\tborder-top-color: #e8e8e8 !important;\r\n\tfont-size:16px !important;\r\n\tfont-family: Open Sans !important;\r\n\toverflow: hidden;\r\n\t\tborder: 2px solid #e8e8e8 !important;\r\n\t}\r\n\r\n#wpsm_accordion_1649 .ac_open_cl_icon{\r\n\tbackground-color:#e8e8e8 !important;\r\n\tcolor: #000000 !important;\r\n\tfloat:right !important;\r\n\tpadding-top: 12px !important;\r\n\tpadding-bottom: 12px !important;\r\n\tline-height: 1.0 !important;\r\n\tpadding-left: 15px !important;\r\n\tpadding-right: 15px !important;\r\n\tdisplay: inline-block !important;\r\n}\r\n\r\n\t\t\t\r\n\t\t\t<\/style>\t\r\n\t\t\t<\/style>\r\n\t\t\t<div class=\"wpsm_panel-group\" id=\"wpsm_accordion_1649\" >\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1649 \" href=\"javascript:void(0)\" data-target=\"#ac_1649_collapse1\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-minus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tCause 1: Spaces within cells\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1649_collapse1\" class=\"wpsm_panel-collapse collapse in\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\tAs shown below, the formula in E2 is correct and the references are fine; you should check whether the lookup value or the cells in the lookup range contain spaces.\r\n\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1650\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=844%2C327&#038;ssl=1\" alt=\"\" width=\"844\" height=\"327\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?w=844&amp;ssl=1 844w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=300%2C116&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=150%2C58&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=768%2C298&amp;ssl=1 768w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=230%2C89&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=350%2C136&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A01.png?resize=480%2C186&amp;ssl=1 480w\" sizes=\"auto, (max-width: 844px) 100vw, 844px\" \/>\r\n\r\nSolution: press Ctrl+H \u00bb Replace. In 'Find what' enter a space, then click 'Replace All'.\r\n\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1651\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?resize=465%2C282&#038;ssl=1\" alt=\"\" width=\"465\" height=\"282\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?w=465&amp;ssl=1 465w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?resize=300%2C182&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?resize=150%2C91&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?resize=230%2C139&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A02.png?resize=350%2C212&amp;ssl=1 350w\" sizes=\"auto, (max-width: 465px) 100vw, 465px\" \/>\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"collapsed\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1649 \" href=\"javascript:void(0)\" data-target=\"#ac_1649_collapse2\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-minus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tCause 2: Invisible characters\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1649_collapse2\" class=\"wpsm_panel-collapse collapse in\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\tIf the data source is exported from a system, invisible characters may be present. The solution is:\r\n<ol>\r\n \t<li>Data \u00bb Text to Columns<\/li>\r\n \t<li>Delimiters<\/li>\r\n \t<li>Completed<\/li>\r\n<\/ol>\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1652\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=1069%2C848&#038;ssl=1\" alt=\"\" width=\"1069\" height=\"848\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?w=1069&amp;ssl=1 1069w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=300%2C238&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=1024%2C812&amp;ssl=1 1024w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=150%2C119&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=768%2C609&amp;ssl=1 768w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=920%2C730&amp;ssl=1 920w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=230%2C182&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=350%2C278&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A03.png?resize=480%2C381&amp;ssl=1 480w\" sizes=\"auto, (max-width: 1000px) 100vw, 1000px\" \/>\r\n<ul>\r\n \t<li>This method can remove most types of invisible characters.<\/li>\r\n<\/ul>\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"collapsed\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1649 \" href=\"javascript:void(0)\" data-target=\"#ac_1649_collapse3\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-minus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tCause 3: Incorrect lookup range selection\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1649_collapse3\" class=\"wpsm_panel-collapse collapse in\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\tAs shown below, the following formula is incorrect:\r\n\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1653\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=970%2C342&#038;ssl=1\" alt=\"\" width=\"970\" height=\"342\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?w=970&amp;ssl=1 970w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=300%2C106&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=150%2C53&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=768%2C271&amp;ssl=1 768w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=920%2C324&amp;ssl=1 920w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=230%2C81&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=350%2C123&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A04.png?resize=480%2C169&amp;ssl=1 480w\" sizes=\"auto, (max-width: 970px) 100vw, 970px\" \/>\r\n<ul>\r\n \t<li>This is because VLOOKUP requires the lookup value to be in the first column of the lookup table. In the data table on the left, 'Name' is in column B, so the lookup range must start at column B. But the formula was written starting at column A, so VLOOKUP only searched A1:A5.<\/li>\r\n<\/ul>\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t<!-- Inner panel Start -->\r\n\t\t\t\t\t<div class=\"wpsm_panel wpsm_panel-default\">\r\n\t\t\t\t\t\t<div class=\"wpsm_panel-heading\" role=\"tab\" >\r\n\t\t\t\t\t\t  <h4 class=\"wpsm_panel-title\">\r\n\t\t\t\t\t\t\t<a  class=\"collapsed\"  data-toggle=\"collapse\" data-parent=\"#wpsm_accordion_1649 \" href=\"javascript:void(0)\" data-target=\"#ac_1649_collapse4\" onclick=\"do_resize()\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span class=\"ac_open_cl_icon fa fa-minus\"><\/span>\r\n\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t \r\n\t\t\t\t\t\t\t\t<span class=\"ac_title_class\">\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<span style=\"margin-right:6px;\" class=\"fa fa-laptop\"><\/span>\r\n\t\t\t\t\t\t\t\t\tCause 4: Inconsistent number formats\t\t\t\t\t\t\t\t<\/span>\r\n\t\t\t\t\t\t\t<\/a>\r\n\t\t\t\t\t\t  <\/h4>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t\t<div id=\"ac_1649_collapse4\" class=\"wpsm_panel-collapse collapse in\"  >\r\n\t\t\t\t\t\t  <div class=\"wpsm_panel-body\">\r\n\t\t\t\t\t\t\t<ul>\r\n \t<li>This problem mainly occurs with lookups involving numeric types. Look at the formula in cell E2 below \u2014 it needs to look up the corresponding Number based on cell D2:\r\n<img data-recalc-dims=\"1\" loading=\"lazy\" decoding=\"async\" class=\"alignnone size-full wp-image-1656\" src=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=843%2C333&#038;ssl=1\" alt=\"\" width=\"843\" height=\"333\" srcset=\"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?w=843&amp;ssl=1 843w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=300%2C119&amp;ssl=1 300w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=150%2C59&amp;ssl=1 150w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=768%2C303&amp;ssl=1 768w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=230%2C91&amp;ssl=1 230w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=350%2C138&amp;ssl=1 350w, https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2022\/02\/VLOOKUP%E5%87%BD%E6%95%B8%E5%87%BA%E9%8C%AF%E7%9A%84%E5%B9%BE%E5%80%8B%E5%B8%B8%E8%A6%8B%E5%8E%9F%E5%9B%A05-1.png?resize=480%2C190&amp;ssl=1 480w\" sizes=\"auto, (max-width: 843px) 100vw, 843px\" \/><\/li>\r\n \t<li>The codes in column D are numbers stored as text, while the lookup codes in column A are numeric values in General format, so the lookup fails.<\/li>\r\n \t<li>The solution is to make the lookup range and the lookup value the same format.<\/li>\r\n \t<li>Modify the formula to convert the lookup value to a number by multiplying it by 1, then the lookup works: =VLOOKUP(D2*1,A:B,2,0)<\/li>\r\n \t<li>\u53cd\u4e4b\uff0c\u5982\u679c\u60f3\u628a\u6578\u503c\u578b\u7684\u67e5\u8a62\u503c\u8b8a\u6210\u6587\u672c\uff0c\u53ea\u8981\u5728\u67e5\u8a62\u503c\u5f8c\u9023\u63a5\u4e0a\u4e00\u500b\u7a7a\u6587\u672c&\"\"\u5c31\u597d\u4e86\uff1a=VLOOKUP(D2&\"\",A:B,2,0)<\/li>\r\n<\/ul>\t\t\t\t\t\t  <\/div>\r\n\t\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<\/div>\r\n\t\t\t\t\t<!-- Inner panel End -->\r\n\t\t\t\t\t\r\n\t\t\t\t\t\t\t<\/div>\r\n\t\t\t\r\n<script type=\"text\/javascript\">\r\n\t\r\n\t\tfunction do_resize(){\r\n\r\n\t\t\tvar width=jQuery( '.wpsm_panel .wpsm_panel-body iframe' ).width();\r\n\t\t\tvar height=jQuery( '.wpsm_panel .wpsm_panel-body iframe' ).height();\r\n\r\n\t\t\tvar toggleSize = true;\r\n\t\t\tjQuery('iframe').animate({\r\n\t\t\t    width: toggleSize ? width : 640,\r\n\t\t\t    height: toggleSize ? height : 360\r\n\t\t\t  }, 250);\r\n\r\n\t\t\t  toggleSize = !toggleSize;\r\n\t\t}\r\n\t\t\r\n<\/script>\t<\/p>","protected":false},"excerpt":{"rendered":"<p>VLOOKUP\u4f5c\u70baExcel\u4e2d\u6700\u53d7\u6b61\u8fce\u7684\u51fd\u6578\u4e4b\u4e00\uff0c\u6211\u5011\u90fd\u6703\u7d93\u5e38\u7528\u5230\uff0c\u4f46\u7528\u8d77\u4f86\u6642\u7e3d\u6703\u9047\u5230\u932f\u8aa4\uff08\u4f46\u660e\u660e\u81ea\u5df1\u4eba\u624b\u67e5\u627e [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":3790,"comment_status":"closed","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_feature_clip_id":0,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_post_was_ever_published":false},"categories":[20,8],"tags":[293,90,304,278],"class_list":["post-1658","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-onlinesupport","category-excel","tag-excel","tag-vlookup","tag-invisible-characters","tag-data-cleaning"],"jetpack_sharing_enabled":true,"jetpack_featured_media_url":"https:\/\/i0.wp.com\/www.fyndpro.com\/wp-content\/uploads\/2026\/08\/new_covers_batch_02_421_excel.jpg?fit=816%2C1088&ssl=1","_links":{"self":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1658","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/comments?post=1658"}],"version-history":[{"count":3,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1658\/revisions"}],"predecessor-version":[{"id":3288,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/posts\/1658\/revisions\/3288"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media\/3790"}],"wp:attachment":[{"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/media?parent=1658"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/categories?post=1658"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.fyndpro.com\/en\/wp-json\/wp\/v2\/tags?post=1658"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}