WallStreetMojo

WallStreetMojo

WallStreetMojo

MENUMENU
  • Free Tutorials
  • Certification Courses
  • Excel VBA All in One Bundle
  • Login
Home » Excel, VBA & Power BI » Excel Tutorials » Fixing VLOOKUP Errors

Fixing VLOOKUP Errors

Top 4 Errors in VLOOKUP and How to Fix Them?

VLOOKUP simply cannot gives you errors because of data mismatch; it will return the errors.

  1. #N/A Error
  2. #NAME? Error
  3. #REF! Error
  4. #VALUE! Error 

Let us discuss each of the error in detail with an example –

You can download this Fix Errors in VLOOKUP Excel template here – Fix Errors in VLOOKUP Excel template

#1 Fixing #N/A Error in VLOOKUP

This error usually comes due to any one of the many reasons. #N/A means simply Not Available is the result of the VLOOKUP formula if the formula is not able to find the required value.

Before head into fixing this problem, we need to know why it is giving error as #N/A. This error is due to data entry mistake, due to approximate match criteria, due to wrong table references, wrong column reference number, data not in vertical form, etc.

I have a simple sales report table in Table 1. In Table 2, I have applied the VLOOKUP formula and trying to extract the values from Table 1.

Fixing VLOOKUP Error Example 1

In the cell F4 and F9, I got errors as #N/A. The value in the cell E4 looks as exactly as the value in the cell A4, but still, I got an error a #N/A. Now you must be thinking why VLOOKUP has returned the result as #N/A. Nothing to worry; follow the below steps to rectify the error.

  • Step 1: Apply the LEN excel formula and find how many characters are there in the cell A4 & E4.

Fixing VLOOKUP Error Example 1-1

In cell C4, I have applied the LEN function to check how many characters are there in the cell A4, and similarly, I have applied the LEN function in the cell D4 to find how many characters there in the cell E4.

In A4 cell, I have 11 characters, but in cell E4, I have 12 characters. One extra character is there in the cell E4 when we compare to the cell A4.

Popular Course in this category
Sale
All in One Excel VBA Bundle (35 Courses with Projects)
4.9 (1,353 ratings)
35+ Courses | 120+ Hours | Full Lifetime Access | Certificate of Completion
View Course

By looking at the outset, both look similar to each other. However, one extra character is there, and it must be a trailing space.

We can just edit the cell E4 and delete the space. If we delete this extra space, we will get the result.

Fixing VLOOKUP Error Example 1-2

But that is not the correct way to solve the issue.

  • Step – 2: We can remove trailing spaces by using the TRIM function in excel. By applying VLOOKUP along with the TRIM function, we can automatically delete the spaces.

Fixing VLOOKUP Error Example 1-3

The TRIM function removes the extra unwanted space.

Fixing VLOOKUP Error Example 1-4

#2 Fixing #VALUE! Error in VLOOKUP

This error is due to missing any one of the parameters in the function. Look at the below table, for example.

Fixing Value

VLOOKUP starts with LOOKUP value, then table range, followed by a column index number and match type. If you look at the above image, formula parameters are not in perfect order. In the place of lookup value table range is there, in the table range place we have column index number and so on.

We can simply need to mention the formula correctly to remove this error.

Fixing Value

#3 Fixing VLOOKUP #REF Error

This error is due to the wrong reference number. When we are applying or mentioning the column index number, we need to mention the exact column number from which column we are looking at the required result. If we mention the column index number that is out of the selection range, then this will return #REF! error.

Fixing REF

The lookup value is perfect; the table range is perfect, but the column index number is not perfect here. I have selected the table range from A3 to B8, i.e., only table range from A3 to B8, i.e., only two columns I have selected.

In the column index number, I have mentioned 3, which is out of range of the table range, so VLOOKUP returns the #REF! error result.

Mention the correct column index number to rectify this error.

Fixing REF

#4 Fixing VLOOKUP #NAME Error

We get this VLOOKUP #NAME Error due to the wrong formula mention. In my personal experience, I usually type CLOOKUP instead of VLOOKUP.

Fixing #NAME

There is no formula called Clookup in excel so returned the values as #NAME? error type.

Solution: The solution is straightforward; we just need to check the spelling of the formula.

Fixing #NAME

Things to Remember Here

  • #N/A error due to data mismatch.
  • #NAME error due to the wrong formula type.
  • #REF error is due to a wrong column index number.
  • #VALUE! The error is due to missing or wrong parameter supply.

Recommended Articles

This has been a guide to VLOOKUP Errors in Excel. Here we discuss how to fix the four most common errors #N/A, #VALUE! #NAME? & REF! in VLOOKUP along with excel example and downloadable excel templates. You may also look at these useful functions in excel –

  • Vlookup to Left
  • VLookup with IF Function
  • Multiple Criteria of VLOOKUP
  • IFERROR with VLOOKUP in Excel
0 Shares
Share
Tweet
Share
All in One Excel VBA Bundle (35 Courses with Projects)
  • 35+ Courses
  • 120+ Hours
  • Full Lifetime Access
  • Certificate of Completion
LEARN MORE >>
Primary Sidebar
Footer
COMPANY
About
Reviews
Contact
Privacy
Terms of Service
RESOURCES
Blog
Free Courses
Free Tutorials
Investment Banking Tutorials
Financial Modeling Tutorials
Excel Tutorials
Accounting Tutorials
Financial Statement Analysis
COURSES
All Courses
Financial Analyst All in One Course
Investment Banking Course
Financial Modeling Course
Private Equity Course
Venture Capital Course
Excel All in One Course

Copyright © 2021. CFA Institute Does Not Endorse, Promote, Or Warrant The Accuracy Or Quality Of WallStreetMojo. CFA® And Chartered Financial Analyst® Are Registered Trademarks Owned By CFA Institute.
Return to top

WallStreetMojo

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

Book Your One Instructor : One Learner Free Class
Let’s Get Started
Please select the batch
Saturday - Sunday 9 am IST to 5 pm IST
Saturday - Sunday 9 am IST to 5 pm IST

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

WallStreetMojo

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

Login

Forgot Password?

WallStreetMojo

Download Fix Errors in VLOOKUP Excel template

New Year Offer - All in One Excel VBA Bundle (35 Courses with Projects) View More