How can you set a default value when using the
VLOOKUP
function?
As
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)
Where
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.
Therefore, using
ISERROR
is a much cleaner approach that prevents any unnecessary errors.
VLOOKUP
Example
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.
The
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:
A | B | C | |
---|---|---|---|
1 | Employee | Position | Salary |
2 | John Smith | Admin |
=ISERROR(VLOOKUP(B2,E:F,2,FALSE),0)
120,000 |
3 | Jane Doe | Officer |
=ISERROR(VLOOKUP(B3,E:F,2,FALSE),0)
100,000 |
4 | Kelly Smith | Manager |
=ISERROR(VLOOKUP(B4,E:F,2,FALSE),0)
0 |
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
Admin
and
Officer
, but as there is no salary for
Manager
this will return an
#N/A
error.
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
0
.
Summary
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
ISERROR
formula.
Here is how your formula should look when combining the
ISERROR
with
VLOOKUP
:
=ISERROR(VLOOKUP(search_for, in_range, return_column_idx, match_type), default_value)
This method is preferred over wrapping the
VLOOKUP
in
ISNA
as the
VLOOKUP
function will need to be entered twice and this can cause issues if both
VLOOKUP
formulas are manually entered.