There are many error-trapping functions like ISNA, IFNA, IFERROR, ISERROR which we can use in Excel. These help the formulae to run smoothly.
To hide the #NA error we can use the Vlookup and ISNA functions together.
First, let us understand the reasons why the #N/A error occurs:
- The data we want to lookup does not exist in our table.
- The table is not set in ascending order.
Vlookup looks up a value in the selected range of cells in the left-most column & returns the value in the same row in the index-number position.
Lets break down the arguments of the Vlookup function & understand each one.
- lookup_value: The value you want to look for
- table_array: The table of data contains information from which you want to return
the output. Table array should contain at least two columns of data. The first columncontains the lookup values. These values can be text, numbers, or logical values.
- col_index_num: It contains the data from which you want the information.
- range_lookup: Range lookup is a logical value which indicates to the Vlookup function to find an exact match or approximate. Table must be sorted in ascending order. The choices are true or false. True for Approximate match, False for Exact match
In a spreadsheet, we have Emp id, Sales Person & Units sold in column A, B & C respectively.
Sometimes we need to use the Vlookup function in a different sheet other than the current sheet.
In sheet2, we are applying a Vlookup where B3 contains the Lookup Value i.e. 110 which is not present in the sheet “Datasheet” column A. In this case, cell C3 will return the #NA error. Refer to the below screenshot.
We can use the following functions to hide the #NAerror:
Lets review each function one by one –
- =IFNA(VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0),”Not Exist”)
- =IFERROR(VLOOKUP($B$3,DataSheet!$A$4:$C$10,3,0),”Not Exist”)