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:
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:
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.
Click here to make a copy of the VLOOKUP spreadsheet above
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:
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.