Skip to content
Free Excel Tutorials
  • Home
  • Excel For Beginners
  • Excel Intermediate
  • Advanced Excel For Experts

Data Analysis

  • Conditional Formatting Data bars Examples in Excel
  • How to Create Thermometer Chart in Excel
  • What-If Analysis: Scenarios and Goal Seek in Excel
  • Excel Bar Chart
  • Get column index in Excel Table

References

  • VLOOKUP function: Description, Usage, Syntax, Examples and Explanation
  • Perform case-sensitive Lookup in Excel
  • How to retrieve first match between two ranges in Excel
  • How to get first column number in range in Excel
  • LOOKUP function: Description, Usage, Syntax, Examples and Explanation

Data Validations

  • How To Create Drop-down List in Excel
  • Excel Data validation no punctuation
  • Excel Data validation whole percentage only
  • Excel Data validation must contain specific text
  • Excel Data validation unique values only

VLOOKUP without #N/A error in Excel

by

This tutorial shows how to calculate VLOOKUP without #N/A error in Excel using the example below;

Formula

=IFERROR(VLOOKUP(value,table,2,FALSE),"")

Explanation

To hide the #N/A error that VLOOKUP throws when it can’t find a value, you can use the IFERROR function to catch the error and return any value you like.

How the formula works

When VLOOKUP can’t find a value in a lookup table, it returns the #N/A error. The IFERROR function allows you to catch errors and return your own custom value when there is an error. If VLOOKUP returns a value normally, there is no error and the looked up value is returned. If VLOOKUP returns the #N/A error, IFERROR takes over and returns the value you supply.

Worked Example:   Get first text value in a list in Excel

If you have a lookup value in cell A1 and lookup values in a range named table, and you want a cell to be blank if no lookup is found, you can use:

=IFERROR(VLOOKUP(A1,table,2,FALSE),"")

If you want to return the message “Not found” when no match is found, use:

=IFERROR(VLOOKUP(A1,table,2,FALSE),"Not found")

IFNA option

In Excel 2013, the IFNA function is available to trap and handle #N/A errors specifically. The usage syntax is the same as with IFERROR:

=IFERROR(VLOOKUP(A1,table,2,FALSE),"Not found")
=IFNA(VLOOKUP(A1,table,2,FALSE),"Not found")

Older versions of Excel

In earlier versions of Excel that lack the IFERROR function, you’ll need to repeat the VLOOKUP inside an IF function that catches an error with ISNA or ISERROR. For example:

=IF(ISNA(VLOOKUP(A1,table,2,FALSE)),"",VLOOKUP(A1,table,2,FALSE))

Post navigation

Previous Post:

Weighted average in Excel

Next Post:

Win loss points calculation in Excel

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

Learn Basic Excel

Ribbon
Workbook
Worksheets
Format Cells
Find & Select
Sort & Filter
Templates
Print
Share
Protect
Keyboard Shortcuts

Categories

  • Charts
  • Data Analysis
  • Data Validation
  • Excel Functions
    • Cube Functions
    • Database Functions
    • Date and Time Functions
    • Engineering Functions
    • Financial Functions
    • Information Functions
    • Logical Functions
    • Lookup and Reference Functions
    • Math and Trig Functions
    • Statistical Functions
    • Text Functions
    • Web Functions
  • Excel VBA
  • Excel Video Tutorials
  • Formatting
  • Grouping
  • Others

Logical Functions

  • Check multiple cells are equal in Excel
  • Not Equal To ‘<>‘ operator in Excel
  • IF, AND, OR and NOT Functions Examples in Excel
  • Invoice status with nested if in Excel
  • How to use Excel OR Function

Date Time

  • Add days exclude certain days of week in Excel
  • Extract time from a date and time in Excel
  • YEAR function: Description, Usage, Syntax, Examples and Explanation
  • Convert text date dd/mm/yy to mm/dd/yy in Excel
  • Calculate date overlap in days in Excel

Grouping

  • Categorize text with keywords in Excel
  • Map text to numbers in Excel
  • Map inputs to arbitrary values in Excel
  • If cell contains one of many things in Excel
  • Group arbitrary text values in Excel

General

  • How to calculate profit margin percentage in Excel
  • Check if multiple cells have same value in Excel
  • How to calculate percent of students absent in Excel
  • How to increase by percentage in Excel
  • Index and match on multiple columns in Excel
© 2023 xlsoffice . All Right Reserved. | Teal Smiles | Abbreviations And Their Meaning