ExcelCSE.com

Menu
  • Excel 101
  • Excel Functions
  • Excel Formula
  • Contact Us
How to Use Excel > Excel Formula > VLOOKUP NA Error – Causes and Solutions

VLOOKUP NA Error – Causes and Solutions

VLOOKUP NA Error - Causes and Solutions
Table of contents :
  • VLOOKUP NA error because of the wrong table_array range
  • VLOOKUP NA error because the data is missing from the table_array
  • VLOOKUP NA error because the lookup_value looks for nonexisting value
  • VLOOKUP NA error because there is a blank space
  • VLOOKUP NA error because numbers are stored as text

VLOOKUP NA Error

VLOOKUP NA error is the most common error when using the VLOOKUP function. This error occurs because the lookup_value is not in the first column of table_array for several reasons.

There are eight possible errors when using the VLOOKUP function. Read the following article for a more detailed explanation of the other VLOOKUP errors

  • VLOOKUP Error – Frequently Error and Solutions

VLOOKUP NA error because of the wrong table_array range

There are two possibilities for the wrong table_array range. First, the table_array range shifts because of the process of copying a formula. This error has been explained in detail with the solution in “The Wrong Cell Reference” article.

Second, the table_array range point to the wrong location. If this happens, you must fix the formula and point the table_array range to the correct place.

VLOOKUP NA error because the data is missing from the table_array

This error occurs because something is missing in table_array because it was deleted.

VLOOKUP NA Error

The picture above explains the formula in cell H5 is correct, the same as the formula in the other cell in the same column, but the word “Caffe Latte” disappears from the price table, this is what causes #N/A error, even though there is a price in the right column. The VLOOKUP function cannot retrieve the price value if there is no data in the first column of table_array.

VLOOKUP NA error because the lookup_value looks for nonexisting value

This error occurs because lookup_value is not in the first column of table_array. The image below explains no “Hot Brewed Coffee” in the table prices, this is what causes #N/A error, although the formula is correct.

VLOOKUP NA Error

Some people want this #N/A error not to occur, because it affects to the other formula. #N/A error appears in total bill (cell H10) due to #N/A error in cell H8.

You can use the solution below to avoid #N/A error from appearing because of nonexisting lookup_value in the first column of table_array.

  • IFERROR VLOOKUP – Solution for VLOOKUP NA Error

The other alternatives, you can use the AGGREGATE function or array formula. The following article discusses how to prevent divide by zero errors. You can use the same solution to avoid #N/A error in the total bill.

  • Excel Divide by Zero Error

VLOOKUP NA error because there is a blank space

Blank space is hard to detect error. Blank space could be in the lookup_value or in the first column of table_array. If a blank space in the front, it is easier to find out, but if a blank space in the back it will be not visible

VLOOKUP NA Error

Look at the image above; there are two #N/A errors in cell H5 and cell H7. #N/A error in cell H7 because of a blank space in front of the word “Salted Caramel Mocha”. Pay attention to cell F7, the word “Salted Caramel Mocha” is a bit indented, that’s where the blank space is.

#N/A error in cell H5 because there is a blank space behind the word “Caffe Latte”. A cell containing blank space is cell A2, but the appearance is not visible, it must be checked by editing the cell, of course, this will take time if there are many data.

Use excel TRIM function to remove spaces either in front or behind quickly

VLOOKUP NA error because numbers are stored as text

The first column of table_array and the lookup_value must have the same data type. If it is text, then all must be text, if it is number, then all must be number.

The problem arises because numbers can be stored as text by adding single quotes in front of the number. An appearance is a number, but Excel acknowledges it as a text.

VLOOKUP NA Error

Look at the image above. The VLOOKUP function looks for the price based on the item code. As a result, all formulas return #N/A errors.

Look again, the lookup_value is a number (column G), and the first column of table_array (column A) is a number too. Then why the VLOOKUP function returns a #N/A error?

Look more closely. All item code in column A (table_array) is right aligned, while all item code in column G (lookup_value) is left aligned.

Numbers by default is right-aligned, and text by default is left aligned, this is the problem. lookup value is text while the first column of table_array is a number.

Another way to detect numbers stored as text is by the appearance of small green triangles on the top left of each cell. Excel will notify if there are numbers stored as text, whether to convert to numbers or to be ignored.

The Solution

The solution for removing #N/A error is to convert all item code in the first column of table_array to text or to convert all item code in the lookup_value to the number.

For example, the chosen solution is to convert all item code in the lookup_value to numbers. See the image below for how to do it.

VLOOKUP NA Error

You got the prices after conversion completed.

Another way to convert text to numbers is to use the excel NUMBERVALUE function, whereas to convert numbers to text using the excel TEXT function.

Related Function

Function used in this article
  • Excel NUMBERVALUE Function
  • Excel TEXT Function
  • Excel TRIM Function

Related Article

Another article related to this article
  • Excel Divide by Zero Error
  • IFERROR VLOOKUP – Solution for VLOOKUP NA Error
Usefull links
  • How to Correct #N/A Errors
Share
Tweet
Google+
Email
Prev Article
Next Article

Related Articles

Excel Divide by Zero
Pay attention to the data below. A store sells five …

Excel Divide by Zero Error

Calculate Number of Days, Weeks, Months and Years between Two Dates
Calculate Number of Days Between Two Dates Excel has a …

Calculate Number of Days, Weeks, Months and Years Between Two Dates in Excel

Add time in excel
Excel stores date value in integer number and time value …

How to Add Seconds, Minutes and Hours to a Time in Excel

VLOOKUP Error
The Wrong Cell Reference The wrong cell reference is the …

VLOOKUP Error – Frequently Error and Solution

How to Subtract Multiple Cells from One Cell in Excel
For example, there are data such as the image below. …

How to Subtract Multiple Cells from One Cell in Excel

Tags:Excel N A Error NA Error VLOOKUP NA Error

Categories

  • Excel 101
  • Excel Formula
  • Excel Functions

Recent Posts

  • Get the Original Price from Percentage Increase/Decrease
  • How to Add/Subtract Percentage in Excel
  • How to Extract Day, Month and Year from Date in Excel
  • How to Add Seconds, Minutes and Hours to a Time in Excel
  • How to Add Days, Weeks, Months and Years to a Date in Excel
  • How to Calculate Time Difference in Excel
  • Calculate Number of Days, Weeks, Months and Years Between Two Dates in Excel
  • Flash Fill Excel
  • Paste Special Excel
  • Excel TEXTJOIN Function
  • Excel CONCAT Function
  • Excel CONCATENATE Function
  • Excel REPLACE Function
  • Excel SUBSTITUTE Function
  • Excel REPT Function
  • Excel FIND Function
  • Excel SEARCH Function
  • Excel MID Function
  • Excel RIGHT Function
  • Excel LEFT Function

ExcelCSE.com

The Ultimate Guide How to Use Excel Better
Copyright © 2024 ExcelCSE.com
Contact Us - Privacy Policy - Sitemap | Theme by MyThemeShop.com

Ad Blocker Detected

Our website is made possible by displaying online advertisements to our visitors. Please consider supporting us by disabling your ad blocker.

Refresh