{"id":4418,"date":"2022-10-18T00:02:00","date_gmt":"2022-10-18T04:02:00","guid":{"rendered":"https:\/\/exceltheatre.com\/blog\/?p=4418"},"modified":"2022-10-11T11:13:01","modified_gmt":"2022-10-11T15:13:01","slug":"fix-excel-vlookup-error-problem-video-steps","status":"publish","type":"post","link":"https:\/\/exceltheatre.com\/blog\/archives\/2022\/10\/18\/fix-excel-vlookup-error-problem-video-steps\/","title":{"rendered":"Fix Excel VLOOKUP Error Problem Video Steps"},"content":{"rendered":"<p>Sometimes a VLOOKUP formula returns an #N\/A error, even though you can see the value you&#8217;re looking for in the lookup table. The most common cause for this is a text &#8220;number&#8221; in one place, and a real number in the other place. See how you can fix that VLOOKUP problem.<\/p>\n<p><!--more--><\/p>\n<h3>Text Numbers and Real Numbers<\/h3>\n<p>Even if two numbers on the worksheet look the same, Excel might see one as text, and the other as a number. And in Excel, those two things are not equal.<\/p>\n<p>For example, the lookup table may contain <b>&#8216;123<\/b> (text), and the value to look up is <b>123<\/b> (a real number). The video below shows how you can fix the problem, if that&#8217;s the situation in your workbook.<\/p>\n<ul>\n<li><strong>Note<\/strong>: If your workbook has a different type of VLOOKUP problem, take a look at other solutions and examples on <a href=\"https:\/\/www.contextures.com\/excelvlookupexamples.html\" target=\"_blank\" rel=\"noopener\">the VLOOKUP Examples page on my Contextures site<\/a>.<\/li>\n<\/ul>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/vlookupnumberproblem01.png\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"vlookup number problem text number\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/vlookupnumberproblem01_thumb.png\" alt=\"vlookup number problem text number\" width=\"400\" height=\"257\" border=\"0\" \/><\/a><\/p>\n<h3>Video: Fix VLOOKUP Text vs. Number Problem<\/h3>\n<p>This video shows the steps for fixing a VLOOKUP problem when the lookup table has text values, and the lookup value is a real number.<\/p>\n<p>The full transcript is below the video, and you can get the Excel sample workbook on <a href=\"https:\/\/www.contextures.com\/excelvlookupexamples.html\" target=\"_blank\" rel=\"noopener\">the VLOOKUP Examples page on my Contextures site<\/a>.<\/p>\n<p><strong>Video Timeline<\/strong><\/p>\n<ul>\n<li>00:00 VLOOKUP Error Problem<\/li>\n<li>00:44 Values Not Equal<\/li>\n<li>01:37 Fix Values Not Equal<\/li>\n<li>02:07 Change VLOOKUP Formula<\/li>\n<\/ul>\n<p><iframe loading=\"lazy\" src=\"https:\/\/www.youtube.com\/embed\/9l9F3P4HNY0?rel=0\" width=\"450\" height=\"283\" frameborder=\"0\" allowfullscreen=\"allowfullscreen\"><\/iframe><\/p>\n<h3>Transcript: Fix VLOOKUP Text vs. Number Problem<\/h3>\n<p>Here is the full transcript for the Numbers and Text Troubleshooting video shown above.<\/p>\n<p>&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;&#8212;-<\/p>\n<p>Usually the VLOOKUP formula in Excel works very well. We could enter product code, and the VLOOKUP formula would return the product name or price for that product code.<\/p>\n<p>But in this example, we&#8217;ve typed a code here.<\/p>\n<p>We can see that code in the table but the VLOOKUP is returning an N\/A error.<\/p>\n<p>In the formula bar, you can see that VLOOKUP formula.<\/p>\n<ul>\n<li>We&#8217;re referring to the value in B8<\/li>\n<li>looking up in the code column<\/li>\n<li>asking for the value in the second column<\/li>\n<\/ul>\n<p>So it should be giving us the product but it isn&#8217;t. So we&#8217;ll do a bit of troubleshooting, to see what the problem is, and how we can solve it<\/p>\n<h4>Values Not Equal<\/h4>\n<p>Sometimes the problem is, things that look the same on the worksheet, aren&#8217;t really a match.<\/p>\n<p>We&#8217;ll see if what we typed in B8 is really a match for what&#8217;s in B2.<\/p>\n<p>In this cell, I&#8217;m going to just do a simple test.<\/p>\n<ul>\n<li>I&#8217;ll click here<\/li>\n<li>type an equal sign<\/li>\n<li>then click on B2<\/li>\n<li>another equal sign<\/li>\n<li>and B8<\/li>\n<\/ul>\n<p>It&#8217;s coming back FALSE, so there&#8217;s something different about these values<\/p>\n<ul>\n<li>If I click on B2, I can see an apostrophe at the start of that number.\n<ul>\n<li>So it&#8217;s been entered as <strong>text<\/strong>. Maybe this was a download from another file, and it&#8217;s stored as text there.<\/li>\n<\/ul>\n<\/li>\n<li>If I click on B8, there&#8217;s no apostrophe.\n<ul>\n<li>So this is a <strong>number<\/strong>, and the number is not equal to the text, which would have a value of 0.<\/li>\n<\/ul>\n<\/li>\n<\/ul>\n<p>So how can we fix this?<\/p>\n<h4>Fix Values Not Equal<\/h4>\n<p>If we select all these cells, one way to fix it, would be to change all of these to numbers, so they match the values we&#8217;re going to type in here as numbers.<\/p>\n<p>To do that, I can:<\/p>\n<ul>\n<li>Go to the Ribbon<\/li>\n<li>On the Data tab, click Text to Columns<\/li>\n<li>When this comes up, just click Finish<\/li>\n<\/ul>\n<p>That&#8217;s automatically changed all of these to numbers, and you can see that our VLOOKUP is working correctly now.<\/p>\n<p>So it&#8217;s showing me that 123 is the product called Paper.<\/p>\n<h4>Change VLOOKUP Formula<\/h4>\n<p>In some cases, you can&#8217;t change your lookup table, so we can change our lookup formula.<\/p>\n<p>Here we have VLOOKUP(B7<\/p>\n<p>So, whatever is in B7, look up in this table. And it can&#8217;t find this number, because this is text.<\/p>\n<p>So we&#8217;ll change this lookup to text.<\/p>\n<ul>\n<li>I&#8217;ve clicked after the B7<\/li>\n<li>I&#8217;ll type an &amp; which on my keyboard is Shift + 7<\/li>\n<li>Then two double quotes, which is just an empty string<\/li>\n<\/ul>\n<p>So now this used to be a number. When we add an empty string, it&#8217;s going to automatically become text<\/p>\n<p>I&#8217;ll press Enter, and now what&#8217;s entered as a number here, it&#8217;s converted that to text, so it matches what&#8217;s in here.<\/p>\n<p>You can add an empty string in your VLOOKUP formula to convert numbers to text, so the lookups work correctly.<\/p>\n<h3>Get the Excel Workbook<\/h3>\n<p>You can see other solutions, and get the Excel sample workbook on <a href=\"https:\/\/www.contextures.com\/excelvlookupexamples.html\" target=\"_blank\" rel=\"noopener\">the VLOOKUP Examples page on my Contextures site<\/a>.<\/p>\n<p>The zipped file is in <strong>xlsx<\/strong> format, and does not contain any macros.<\/p>\n<p>__________________________<\/p>\n<h3>Fix Excel VLOOKUP Error Problem Video Steps<\/h3>\n<p><a href=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/vlookupfixnumbervideo01b.jpg\"><img loading=\"lazy\" decoding=\"async\" style=\"border: 0px currentcolor; display: inline; background-image: none;\" title=\"Fix Excel VLOOKUP Error Problem Video Steps\" src=\"https:\/\/exceltheatre.com\/blog\/wp-content\/uploads\/2022\/10\/vlookupfixnumbervideo01b_thumb.jpg\" alt=\"Fix Excel VLOOKUP Error Problem Video Steps\" width=\"450\" height=\"225\" border=\"0\" \/><\/a><\/p>\n<p>__________________________<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Sometimes a VLOOKUP formula returns an #N\/A error, even though you can see the value you&#8217;re looking for in the lookup table. The most common cause for this is a text &#8220;number&#8221; in one place, and a real number in the other place. See how you can fix that VLOOKUP problem.<\/p>\n","protected":false},"author":2,"featured_media":4416,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_kadence_starter_templates_imported_post":false,"footnotes":""},"categories":[6],"tags":[33,32],"class_list":["post-4418","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-excel-videos","tag-pivot-subtotals","tag-pivot-table"],"_links":{"self":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4418","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/comments?post=4418"}],"version-history":[{"count":3,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4418\/revisions"}],"predecessor-version":[{"id":4422,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/posts\/4418\/revisions\/4422"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media\/4416"}],"wp:attachment":[{"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/media?parent=4418"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/categories?post=4418"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/exceltheatre.com\/blog\/wp-json\/wp\/v2\/tags?post=4418"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}