EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Certification Courses
  • 120+ Courses All in One Bundle
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel Fixing VLOOKUP Errors
Secondary Sidebar
Excel Functions
  • Lookup and Reference Functions in Excel
    • VLOOKUP Function in EXCEL
    • VLOOKUP True
    • VLOOKUP Error
    • How to Match Data in Excel
    • Excel Match Function
    • Excel Lookup Function
    • ROWS Function in Excel
    • Excel INDEX Function
    • VLOOKUP Table Array
    • Excel OFFSET Formula
    • VLOOKUP For Text
    • IF VLOOKUP Formula in Excel
    • Mixed Reference in Excel
    • CHOOSE Formula in Excel
    • Excel COLUMN to Number
    • Excel Alternatives to VLOOKUP
    • HLOOKUP Examples
    • Excel VLOOKUP From Another Sheet
    • VLOOKUP with Sum
    • Fixing VLOOKUP Errors
    • Excel ROW Function
    • HYPERLINK in Excel
    • Address Excel Function
    • Excel COLUMNS Function
    • Excel REPLACE Function
    • OFFSET Excel Function
    • Excel GETPIVOTDATA Function
    • MATCH Function in Excel
    • VLOOKUP Function in Excel
    • HLOOKUP Function in Excel
    • LOOKUP in Excel
    • CHOOSE Function in Excel
    • TRANSPOSE in Excel
    • COLUMN Function in Excel
    • INDIRECT Function in Excel
  • Excel Functions (12+)
  • Excel Tools (114+)
  • Financial Functions in Excel (17+)
  • Logical Functions in Excel (14+)
  • Maths Function in Excel (47+)
  • TEXT and String Functions in Excel (26+)
  • Date and Time Function in Excel (24+)
  • Statistical Functions in Excel (59+)
  • Information Functions in Excel (4+)
  • Excel Charts (56+)
  • Excel Tips (222+)
  • Workplace Productivity (4+)
  • Microsoft Office Tools (15+)
  • Excel Formula and Functions (22+)
  • MS Excel Shortcuts (4+)
Excel Functions Courses
  • Online EXCEL COURSE
  • Online EXCEL ADVANCED Training

Fixing VLOOKUP Errors

By Madhuri ThakurMadhuri Thakur

Errors in VLOOKUP

Excel VLOOKUP Errors (Table of Contents)

  • VLOOKUP Errors
  • #1 – Fixing VLOOKUP #N/A Error
  • #2 – Fixing #VALUE Error in VLOOKUP formula
  • #3- Fixing #NAME Error in VLOOKUP formula
  • #4- Fixing VLOOKUP not working (problems, limitations, and solutions)

Introduction to VLOOKUP Errors

While we use the Vlookup function, there are some instances when we may get an error like #N/A. #Value and #Name, which commonly occur in Vlookup. To avoid such errors, there are 2 ways. The first is to use the properly formatted data where there are no blank cells, 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 returns anything in place of error, whatever we place for error.

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:

Start Your Free Excel Course

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

All in One Excel VBA Bundle(120+ Courses, 30+ Projects)
Basic ExcelAdvanced ExcelVBA & MacrosExcel Dashboards
Pivot TablesStatistical FunctionsFinancial FunctionsCharts & Graphs
Price
View Courses
120+ Online Courses | 30+ Projects | 500+ Hours | Verifiable Certificates | Lifetime Access
4.9 (64,813 ratings)
  • 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 you are 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.

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 that 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 is looking for the lookup value in Column A, not in 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, then 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 have to conclude the full path to that file. It will include the workbook’s name (with extension) in square brackets [], and then specify the sheet’s name followed by the exclamation mark. Use apostrophes around all this in case either 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 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 in reference to 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. In this section, we will discuss a few common scenarios when the VLOOKUP function fails.

  • VLOOKUP is case-insensitive

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

  • A Column has been inserted or removed from the table

If you are reusing the VLOOKUP formula and you made some changes in the dataset. Like inserted a new column or deleted any column, it will leave an impact on VLOOKUP function results, and it won’t work at that moment.

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 to lock 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 along with 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
Popular Course in this category
Excel Training (23 Courses, 9+ Projects)
  23 Online Courses |  9 Hands-on Projects |  110+ Hours |  Verifiable Certificate of Completion
4.9
Price

View Course

Related Courses

Excel Advanced Training (16 Courses, 23+ Projects)4.9
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • 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

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

EDUCBA
Free Excel Course

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

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

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

EDUCBA Login

Forgot Password?

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

EDUCBA
Free Excel Course

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

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

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

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

Let’s Get Started

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

EDUCBA

Download VLOOKUP Errors Excel Template

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