EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel Fixing VLOOKUP Errors

Fixing VLOOKUP Errors

Madhuri Thakur
Article byMadhuri Thakur

Updated August 10, 2023

Errors in VLOOKUP

Introduction to VLOOKUP Errors

While we use the Vlookup function, there are some instances when we may get an error(vlookup errors) like #N/A. #Value and #Name, which commonly occur in Vlookup. This to avoid such errors, there are 2 ways. First, use the properly formatted data with no blank cells and values in incorrect format and remove the formulas. And another way is by using the IFERROR Function before VLookup. THE IFERROR function ignores the error and returns anything in place of the error, whatever we place for the error.

Start Your Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

The common errors when VLOOKUP doesn’t work are:

  • VLOOKUP #N/A error
  • #VALUE error in VLOOKUP formulas
  • VLOOKUP #NAME error
  • VLOOKUP not working (problems, limitations, and solutions)

#1 – Fixing VLOOKUP #N/A Error

This #N/A error means Not Available. This error comes with the following reasons:

  • Due to misspelled a lookup value argument in the function

We should always first check the most obvious thing. Misprint or type errors occur when working with large datasets or when a lookup value is typed directly in the formula.

  • The lookup column is not the first column in the table range

One constraint of the VLOOKUP is that it can only look for values on the leftmost column in the table array. If your lookup value is not in the first column of the array, it will display the #N/A error.

Examples of Vlookup errors

Example #1

Let’s take an example to understand this problem.

You can download this VLOOKUP Errors Excel Template here – VLOOKUP Errors Excel Template

We have given the product details.

Example 1

Let’s assume we want to retrieve the number of units sold for Bitterguard.

VLOOKUP Errors Example 1-2

Now we will apply the VLOOKUP formula for this as shown below:

VLOOKUP Errors Example 1-3

And it will return the #N/A error in the result.

VLOOKUP Errors Example 1-4

Because the lookup value “Bitterguard” appears in the second column (Product) of the table_array range A4:C13. In this condition, a formula looks for the lookup value in Column A, not Column B.

Solution For VLOOKUP #N/A Error

We can fix this problem by adjusting the VLOOKUP to reference the correct column. If that’s not possible, try to move the columns so that the lookup column is the leftmost column in the table_array.

#2 – Fixing #VALUE Error in VLOOKUP formula

The VLOOKUP formula displays the #VALUE error if a value used in the formula is of the wrong data type. There can be two reasons for this #VALUE error:

The lookup value should not be more than 255 characters. If it exceeds this limit, this will end up with the #VALUE error.

Solution For VLOOKUP #VALUE Error

By using INDEX/MATCH functions instead of the VLOOKUP function, we can overcome this problem.

  • The correct path is not passed as the second argument

If you want to pick the records from another workbook, you must conclude the full path to that file. It will include the workbook’s name (with extension) in square brackets [], then specify the sheet’s name followed by the exclamation mark. Use apostrophes around all this in case a workbook or Excel sheet name contains spaces.

The syntax of the complete formula to do a VLOOKUP from another workbook:

=VLOOKUP(lookup_value, ‘[workbook name] sheet name’ !table_array, col_index_num, FALSE)

If anything is missing or any part of the formula is missing, the VLOOKUP formula won’t work, and it will return the #VALUE error in the result.

#3 – Fixing #NAME Error in the VLOOKUP formula

This problem occurs when you have accidentally misspelled the function’s name or argument.

Example #2

Let’s again take the product table details. We need to find out the number of units sold for the product.

Example 2

As we can see that we have misspelled the spelling of FALSE. We type “fa” in place of false. It will return the #NAME error as a result.

Name Error

Solution For VLOOKUP #NAME Error

Check the spelling of the formula before hitting enter.

#4 – Fixing VLOOKUP not Working (Problems, Limitations, and Solutions)

VLOOKUP formula has more limitations than any other Excel function. Because of these limitations, it might often return results different from what you expect. This section will discuss a few common scenarios when the VLOOKUP function fails.

  • VLOOKUP is case-insensitive

If your data contains several entries in the UPPER and LOWER letter case, then the VLOOKUP function works the same for both cases.

  • A Column has been inserted or removed from the table

If you were reusing the VLOOKUP formula and made some changes in the dataset. Like inserting a new column or deleting any column, it will impact the VLOOKUP function results and won’t work now.

Whenever you add or delete any column in the dataset, it impacts the arguments table_array and col_index_num.

  • While copying the formula, it can lead to an error

Always use absolute cell references with the $ sign in table_array. This you can use by pressing the F4 key. That means locking the table reference so that while copying the formula to another cell, it won’t create a problem.

Things to Remember about VLOOKUP Errors

  • In the table, cells with numbers should be formatted as numbers, not text.
  • If your data contains spaces, it can also lead to an error because we can not spot those extra spaces available in the dataset, especially when working with a large amount of data. Hence you can use the TRIM function by wrapping the Lookup_value argument.

Recommended Articles

This has been a guide to VLOOKUP Errors. Here we discuss how to fix the VLOOKUP errors, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VLOOKUP with Different Sheets
  2. ISERROR in Excel
  3. IFERROR Function in Excel
  4. Errors in Excel
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
All in One Excel VBA Bundle500+ Hours of HD Videos | 15 Learning Paths | 120+ Courses | Verifiable Certificate of Completion | Lifetime Access
Financial Analyst Masters Training Program2000+ Hours of HD Videos | 43 Learning Paths | 550+ Courses | Verifiable Certificate of Completion | Lifetime Access
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Excel Charts
  • Excel Tips
  • All Tutorials
Certification Courses
  • All Courses
  • Excel VBA Course - All in One Bundle
  • VBA Course
  • Excel Data Analysis Course
  • Excel for Marketing Course
  • Excel for Finance Course
  • Excel for HR Training

ISO 10004:2018 & ISO 9001:2015 Certified

© 2023 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA

Download VLOOKUP Errors Excel Template

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA
Free Excel Course

Excel functions, formula, charts, formatting creating excel dashboard & others

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you
EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

Download VLOOKUP Errors Excel Template

EDUCBA

डाउनलोड VLOOKUP Errors Excel Template

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more