EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Login
Home Excel Excel Resources Lookup & Reference Functions in Excel IFERROR with VLOOKUP in Excel

IFERROR with VLOOKUP in Excel

Steffi Madari
Article bySteffi Madari
Madhuri Thakur
Reviewed byMadhuri Thakur

Updated July 12, 2023

IFERROR with VLOOKUP in Excel

VLOOKUP with IFERROR (Table of Contents)
  • How to Use IFERROR with VLOOKUP in Excel?
  • Example #1: Replace #N/A Error with Customized Text
  • Example #2: Replace #N/A Error with a Blank or Empty Cell
  • Example #3: Return a Specific Value if Nothing is Found
  • Example #4: Replace #N/A Error on a Fragmented Dataset

IFERROR with VLOOKUP in Excel

The IFERROR function combined with the VLOOKUP function in Excel allows you to return a specific customized text when the VLOOKUP encounters an error.

For example, you can replace the #N/A error with a customized text like “Data Not Found” or a blank cell. This powerful combination of IFERROR with VLOOKUP in Excel makes it easier to identify and handle the #N/A error when analyzing large amounts of data.

Start Your Free Excel Course

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

How to Use IFERROR with VLOOKUP in Excel?

Let’s say that you are looking for the city “Switzerland”, which is not present in the data, and you want to display “City Not Found.”

To use the VLOOKUP with the IFERROR function, follow these simple steps:

  1. In any cell, enter the Vlookup IFERROR formula =IFERROR(VLOOKUP(“Switzerland”, A2:B5, 2, FALSE), “City Not Found”).
  2. Press “Enter” to display the customized message, i.e., “City Not Found”.

You can also customize this formula for different data by replacing the following values:

  • Replace Switzerland” with either the specific value you want to search for or the cell reference that contains that value.
  • Replace “A2:B5” with the range that contains your data.
  • Replace “2” with the column number you want to retrieve information from.
  • You can use TRUE for an approximate match or FALSE for an exact match.
  • You can replace “City Not Found” with any other personalized message.

Let’s understand the use of the IFERROR VLOOKUP in Excel with some examples.

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

Example #1: Replace #N/A Error with Customized Text

Consider the following data on the basic pay of employees in a company. Now, let’s assume you want to find out the salary of an Emp_ID 5902.

Eg 1.1.1

Solution: 
First, let’s use the VLOOKUP function.
Step 1: Select Cell B14 and enter the formula:

=VLOOKUP(B13,A1:C11,3,0) 

Step 2: Press” Enter”. 

Eg 1.2

In the above table, we got a #N/A error.
It is because Emp_ID 5902 is not present in the table_array.
Therefore, to resolve this problem, we can combine the VLOOKUP function with the IFERROR function. We will replace the #N/A error with the customized text “Data Not Found”.

Step 3: To add customized text, select Cell B14 and enter the formula:

=IFERROR(VLOOKUP(B13,A1:C11,3,0),“Data Not Found”)

iferror with vlookup-Eg 1.3

Step 4: Press “Enter”.
Result: The formula displays “Data Not Found” for Emp_ID 5902 instead of an error.

Eg 1.4

Example #2: Replace #N/A with a Blank or Empty Cell

In this example, we will see how to use IFERROR with VLOOKUP in Excel to return a blank instead of a #N/A error.

Solution:
Step 1: Select Cell B14 and enter the formula:

=IFERROR(VLOOKUP(B13,A2:C11,3,FALSE),“”)

Note: Using the empty string (“”) in this formula will help achieve a blank cell.

iferror with vlookup-Eg 2.1 (1)

Step 2: Press “Enter”.
Result: Cell B14 is “Blank“.

Eg 2.2

Example #3: Display a Specific Value if Nothing is Found

Consider an e-commerce company that provides contact numbers on its website for different available services. However, if a user searches for a service that is not available, the default action is to display the customer care number.

So, let’s explore how we can display the customer care number when a user searches for “Order Assistance,” which is unavailable on the website. 

iferror with vlookup-Eg 3.1

Solution:
Step 1: Select Cell B9 and enter the formula:

=IFERROR(VLOOKUP(B8,A1:B6,2,0),VLOOKUP(“Customer Care”,A1:B6,2,0))

iferror with vlookup-Eg 3.2

Step 2: Press “Enter” to display the output in Cell B9.
Result: The formula displays the contact number of “Customer Care” for order assistance.

Eg 3.3

Note:

=IFERROR(VLOOKUP(B8,A1:B6,2,0),VLOOKUP(“Customer Care”,A1:B6,2,0))
The first VLOOKUP function failed to find “Order Assistance” in this formula. Therefore, the formula automatically moved to the second VLOOKUP function and retrieved the customer care contact number.

Example #4: How to use IFERROR with VLOOKUP on a Fragmented Dataset?

This method will teach you to use IFERROR with VLOOKUP in Excel for fragmented datasets. Fragmented datasets mean that the data is present in multiple tables (instead of a single table).

Consider the data below, where two tables are in the same worksheet. Here, you have to find out the basic pay for Emp_ID 5902.

iferror with vlookup-Eg 4.1

Solution:
Step 1: Select Cell B14 and enter the formula:

=IFERROR(VLOOKUP(B13,A1:C11,3,0),IFERROR(VLOOKUP(B13,E1:G11,3,0),”Data not Found))

iferror with vlookup-Eg 4.2

Explanation of the formula:
=IFERROR(VLOOKUP(B13,A1:C11,3,0),IFERROR(VLOOKUP(B13,E1:G11,3,0),”Data not Found))

The formula states that the VLOOKUP function will check the lookup_value (B13) in the first table (A1:C11), and if the value is not found, then it will check in the second table (E1:G11). If the lookup_value is not found in both the tables, then the formula should display “Data not Found”.

Step 2: Press “Enter”.
Result: The formula display “9310″ in Cell B14.

Eg 4.3

The formula fetched the result from the 2nd table, as shown below.

iferror with vlookup-Eg 4.4

Suppose you search for Emp_ID 5115, which is not present in the data. Here, the formula will display “Data Not Found”, as shown below.

Eg 4.5

Recommended Articles

This article is a step-by-step guide on how to use IFERROR with VLOOKUP in Excel. Here we discuss in detail how to use IFERROR with VLOOKUP in Excel to replace #N/A error with customized text, blank cells, etc., along with practical examples. You can also review our other suggested articles:

  1. IF VLOOKUP Formula in Excel
  2. VLOOKUP Examples in Excel
  3. Count Colored Cells in Excel
  4. Create Spreadsheet in Excel
ADVERTISEMENT
All in One Excel VBA Bundle
500+ Hours of HD Videos
15 Learning Paths
120+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
Financial Analyst Masters Training Program
2000+ Hours of HD Videos
43 Learning Paths
550+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Data Science Bundle
2000+ Hour of HD Videos
80 Learning Paths
400+ Courses
Verifiable Certificate of Completion
Lifetime Access
ADVERTISEMENT
All in One Software Development Bundle
5000+ Hours of HD Videos
149 Learning Paths
1050+ Courses
Verifiable Certificate of Completion
Lifetime Access
Primary Sidebar
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

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

EDUCBA

Download IFERROR with VLOOKUP Excel Template

Let’s Get Started

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

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 IFERROR with VLOOKUP Excel Template

EDUCBA

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

🚀 Extended Cyber Monday Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW