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.