EDUCBA

EDUCBA

MENUMENU
  • Blog
  • All Courses
  • All in One Bundle
  • Login
Home Excel Excel Resources Excel Functions VLOOKUP Error

VLOOKUP Error

VLOOKUP Error

Introduction to VLOOKUP Error

We all know how to apply and application of Vlookup function. And we have seen various ways to use the Vlookup function in previous articles. We are all also somewhere and sometimes see different error messages which could and couldn’t be able to resolve it. Vlookup Errors are errors that may happen for different reasons. The basic and most common reasons are incorrect range, no match, and special characters in the lookup range as well in the lookup cell. This article will show the different types of Vlookup Errors and the logic behind those errors.

How to Use VLOOKUP Error?

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

Below are the most commonly occurring Vlookup Errors.

Start Your Free Excel Course

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

  1. #N/A Error
  2. #Value! Error
  3. #Name? Error
  4. #REF! Error

MS Excel has a standard way to prompt the errors it is happening in the cells while applying any of the Excel functions, which is by putting the HASH sign (“#”) before that. We all have seen this; we rarely have noticed. Above mentioned errors are some of them. Every error has a different meaning and reason to occur, which we will see in upcoming examples.

Examples of VLOOKUP Error

Lets us discuss the examples of VLOOKUP Error.

Example #1

In this example, we will see the first and most common type of error, #N/A. For this, we have some sample data, as shown below. As we can see, we have 2 tables. The first table has the Sales data for some electronic products like laptops, desktops, notebooks, and tablets, and the second table is the table where we will be looking up the value from the first table. We have created a common key by combining OWNER and PRODUCT columns, separated by an underscore (“_”).

VLOOKUP Error 1-1

Let’s apply the Vlookup in cell I3. Apply the vlookup using the range and lookup cell as shown below. As shown below, the Lookup cell is F3, our Key of Table2, the lookup range selected from Table1.

vlookup error 3

vlookup error 4

Once we drag the applied function, we will see that for cell I5, and we got an error as #N/A.

vlookup error 5

To find the reason behind this, there are 2 possibilities. We may have some space or special characters in lookup cell F4 or the first column of the Lookup range. To find this, we can check the length of both the cell of the same row using the LEN formula. We have used the LEN function by selecting the cell of row 5. Below is row 13.

vlookup error 6

After using LEN Formula in cell A13, the output is shown below:

vlookup error 7

LEN Formula use in cell F13.

vlookup error 8

After using the above formula, the output is shown below:

example 1

If we Clear or Delete that extra space or special character from cell A5, we will get the Quantity sold for Ben_Notebook for Table 2, as shown below.

example 1-1

Example #2

In this example, we will see the Vlookup error #VALUE! This happens due to incorrect syntax or missing syntax values while applying Vlookup. We will be using the same data which we have seen in example-1. Let’s apply the vlookup at cell I3 and not choose any syntax value. Let’s say it is Col_Index_num, as shown below.

example 2

And if we press enter to exit from the syntax, we will see that cell I3 will have an error as #VALUE!

example 2-1

To rectify this, choose the missing Column Index Number in the syntax as 4 and Enter.

example 2

We will see that now the values are looked up properly.

example 2-3

Example #3

In this example, we will see the Vlookup error type #REF!, which happens due to the selection incorrect Column index number, exceeding the limit beyond the selection. In cell I3, we have applied the Vlookup function choosing everything as we have seen in the above example, but the column index number is now selected as 5, as circled below.

vlookup error 3-1

Once we press Enter key to exit from the syntax, we will get the error message #REF!

vlookup error 3-2

If we correct the column index number from the 5th to the 4th position, we will get the right value looked up.

example 3-3

Once we exit, we will notice the value there.

example 3-4

Example #4

Vlookup error #NAME? We get this when we select all the right positions and cells in syntax but mistakenly type the wrong name of the syntax. As per my experience, I mostly type the function CLEAN instead of VLOOKUP. We will use the same data and select a different function with the syntax of Vlookup. Here intentionally chose BLOOKUP instead of VLOOKUP with the same syntax.

example 4-1

And once exit from the syntax, we will get #NAME? Error as shown below.

example 4-2

To rectify this error, we must select the right function with the right syntax, as we have selected BLOOKUP instead of VLOOKUP.

example 4-3

Pros of Vlookup Error

  • It is very important to know the different errors available in Excel for the Vlookup function.
  • Vlookup Errors are not limited to the above-shown variations.

Things to Remember

  • #N/A error occurs due to the absence of a lookup value.
  • #NAME? The error occurs due to incorrect function selection but keeping the right syntax.
  • #REF! error occurs due to incorrect selection of column index number.
  • #Value! The error occurs when we miss any of the syntax sequences.
  • We must know all possible types of errors that happen in the VLOOKUP function so that we would be able to rectify them whenever it is required.
  • We can calculate the formula using Evaluate Formula option available Formulas menu ribbon under the Formula Auditing section.

Recommended Articles

This has been a guide to VLOOKUP Error. Here we discuss How to Use VLOOKUP Error, practical examples, and a downloadable Excel template. You can also go through our other suggested articles –

  1. VLOOKUP True
  2. VLOOKUP Names
  3. Vlookup vs Index Match
  4. VLOOKUP Table Array
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
1000+ Hours of HD Videos
43 Learning Paths
250+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Data Science Bundle
1500+ Hour of HD Videos
80 Learning Paths
360+ Courses
Verifiable Certificate of Completion
Lifetime Access
All in One Software Development Bundle
3000+ Hours of HD Videos
149 Learning Paths
600+ 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 Program1000+ Hours of HD Videos | 43 Learning Paths | 250+ Courses | Verifiable Certificate of Completion | Lifetime Access
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

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

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
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

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

Download VLOOKUP Error Excel Template

EDUCBA Login

Forgot Password?

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

EDUCBA

Download VLOOKUP Error Excel Template

EDUCBA

डाउनलोड VLOOKUP Error 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