You can use the VLOOKUP function in Excel to look up some value in a range and return a corresponding value.
If the VLOOKUP function doesn’t find the value you’re searching for in a particular range, it will return #N/A as a result.
However, you can use the following syntax to instead return zero if no match is found:
=IFERROR(VLOOKUP(D2, $A$2:$B$10, 2, FALSE), 0)
This particular example attempts to look up the value in cell D2 in the range A2:B10 and return the corresponding value in the second column of the range.
If no match is found, then a zero is returned.
The following example shows how to use this syntax in practice.
Example: Return 0 Instead of #N/A in VLOOKUP
Suppose we have the following dataset in Excel that contains information about various basketball players:
Suppose we use the following formula with VLOOKUP to look up the team names in column D and return the corresponding value from the points column:
=VLOOKUP(D2, $A$2:$B$10, 2, FALSE)
The following screenshot shows how to use this formula in practice:
Notice that the VLOOKUP function returns #N/A in the row where we searched for “Kings” because this team name does not exist in the original dataset.
We can use the following formula to instead return a value of zero if not match is found when using VLOOKUP:
=IFERROR(VLOOKUP(D2, $A$2:$B$10, 2, FALSE), 0)
The following screenshot shows how to use this formula in practice:
Notice that a value of 0 is now returned in the row where we searched for “Kings” instead of #N/A.
Additional Resources
The following tutorials explain how to perform other common operations in Excel:
How to Compare Two Lists in Excel Using VLOOKUP
How to Find Unique Values from Multiple Columns in Excel
How to Filter Multiple Columns in Excel
