How can you set a default value when using the
VLOOKUP throws an
#N/A error when the searched item cannot be found in the first column range, wrap the
VLOOKUP function in an
IFERROR formula and set the value to the default sought.
For example like this:
=IFERROR(VLOOKUP(search, range, column_idx, match_type), default_value)
default_value is the placeholder where you would the data inserted into your spreadsheet if the item is not found in the first column of the range.
The approach above is a more succinct version of another means of creating a default value when
VLOOKUP does not work:
=IF(ISNA(VLOOKUP(search, range, column_idx, match_type), default_value, VLOOKUP(search, range, column_idx, match_type))
But as you can see the second method repeats the
VLOOKUP function twice which can lead to causing errors if there are incorrect values inserted into all the parameters.
ISERROR is a much cleaner approach that prevents any unnecessary errors.
Here’s a quick demonstration of this concept. Suppose you have a data set of employees with their ID, names and salary like so:
As you can see, there are two tables of information: a list of employees, their corresponding position and subsequent salary; and a list of positions with their corresponding salary.
VLOOKUP formula is contained in the Employee’s table, where it searches for the position in the Position’s table and fetches the corresponding salary. The
VLOOKUP formula in the Employee’s table is as follows:
As you can see from inspecting the formulas contained in the spreadsheet above the
VLOOKUP function searches the employee’s position in the Position table (located in cells
E:F) and returns the second column. The formula has no problem fetching the salary for positions that are
Officer, but as there is no salary for
Manager this will return an
By wrapping the
VLOOKUP function in
ISERROR the Manager row will return the second parameter of the
ISERROR formula and in my case this is the value of
To return a default value when using the
VLOOKUP formula wrap the
VLOOKUP function inside an
ISERROR formula as the first parameter and then set the default value if the
VLOOKUP function errors in the second parameter of the
Here is how your formula should look when combining the
=ISERROR(VLOOKUP(search_for, in_range, return_column_idx, match_type), default_value)
This method is preferred over wrapping the
ISNA as the
VLOOKUP function will need to be entered twice and this can cause issues if both
VLOOKUP formulas are manually entered.