WallStreetMojo

WallStreetMojo

WallStreetMojo

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

IFERROR with VLOOKUP in Excel

IFERROR with VLOOKUP to Get Rid of #NA Errors

As we know IFERROR is an error handling function and Vlookup is a referencing function, these functions are combined and used so that when Vlookup encounters an error while finding or matching the data the formula must know what to do when it encountered an error, Vlookup function is nested in the iferror function.

Examples

You can download this IFERROR with VLOOKUP Excel Template here – IFERROR with VLOOKUP Excel Template

Example #1

Table 1 is the main data source, and table 2 is the Vlookup table. In column F, I have applied a Vlookup formula to find the sales amount for laptop brands.

main data source

In the above table, I got an error for the brands’ Apple and Notepad. If you look at the main data table, there are no Apple and Notepad brands. That is why Vlookup has returned an error type as #N/A.

We can fix this issue by using IFERROR with the VLOOKUP function.

Apply IFEEROR before VLOOKUP in excel. We need to write the Vlookup formula inside the IFERROR formula.

IFEEROR before VLOOKUP

=IFERROR (VLOOKUP (E3, $A: $B, 2, 0),”Data Not Found”)

Firstly, IFERROR is trying to find the value for the VLOOKUP formula.

Secondly, If VLOOKUP does not find value, then it will return an error. Therefore, if there is an error, we will show the result as “Data Not Found.”

We have replaced all the #N/A values with the “Data Not Found” text. I think this will look better than the #N/A.

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

IFERROR with LOOKUP 3

Example #2

Not only can we use IFERROR with VLOOKUP in Excel. We can use this with any other formula too.

Look at the below example where I need to calculate the variance percentage. If the base value is, the missing calculation returns the error as #DIV/0!

error as #DIV/0!

So we can apply the IFERROR method here to get rid of ugly errors, i.e., #DIV/0!

#DIV/0 error

If any given calculation returns any kind of error, then IFERROR returns the result as 0%. If there is no error, then the normal calculation will happen.

Manual Method to Replace #N/A or any other Error Types

However, we can replace errors with the IFERROR formula there one manual method to do it, and that is found and replace method.

  • Step 1: Once the formula is applied and copy and paste only values.

copy and paste only values

Step 2: Press Ctrl + H to open replace the box and type #N/A If the error type is #N/A.

error type is #N/A

  • Step 3: Now write the replace with values as “Data Not Found.”

Data Not Found

  • Step 4: Click on replace all button.

replace all button

This would instantly replace all the #N/A values with Data Not Found.

replace all the #N/A values

Note: If you have applied filter, please choose the visible cells only method to replace.

Things to Remember

  • IFERROR can make your numerical reports beautiful by removing all kinds of errors.
  • If the data contains error type and if you apply pivot tables, then the same kind of error will occur in the Pivot table too.
  • Though we can use the IFNA formula, it is not flexible enough to give results for the errors other than #N/A.
  • In Excel 2007 and earlier version, the formula to get rid of #N/A error is ISERROR.

Recommended Articles

This has been a guide to on how to use IFERROR with VLOOKUP in Excel to remove #NA Errors values. Here we take practical excel examples along with downloadable excel templates. You may also look at these useful functions in excel –

  • VBA IIF
  • VLOOKUP Errors
  • Error Bars in Excel
  • Excel VBA IFERROR
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 IFERROR with VLOOKUP Excel Template

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