Table of Contents
VLOOKUP is a useful function in Excel that allows users to search for a specific value in a table and return a corresponding value from a different column. However, when the searched value is not found, VLOOKUP returns the error #N/A. To resolve this issue and display a more meaningful value, users can use the IFERROR function in combination with VLOOKUP to return 0 instead of #N/A. This will provide a more accurate and user-friendly result in cases where the searched value is not present in the table. By using this method, users can ensure that their data and calculations are not affected by the presence of #N/A errors.
Excel: Return 0 Instead of #N/A with VLOOKUP
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)
Notice that a value of 0 is now returned in the row where we searched for “Kings” instead of #N/A.
The following tutorials explain how to perform other common operations in Excel:
Cite this article
stats writer (2024). How can I use VLOOKUP to return 0 instead of #N/A in Excel?. PSYCHOLOGICAL SCALES. Retrieved from https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-0-instead-of-n-a-in-excel/
stats writer. "How can I use VLOOKUP to return 0 instead of #N/A in Excel?." PSYCHOLOGICAL SCALES, 23 Jun. 2024, https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-0-instead-of-n-a-in-excel/.
stats writer. "How can I use VLOOKUP to return 0 instead of #N/A in Excel?." PSYCHOLOGICAL SCALES, 2024. https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-0-instead-of-n-a-in-excel/.
stats writer (2024) 'How can I use VLOOKUP to return 0 instead of #N/A in Excel?', PSYCHOLOGICAL SCALES. Available at: https://scales.arabpsychology.com/stats/how-can-i-use-vlookup-to-return-0-instead-of-n-a-in-excel/.
[1] stats writer, "How can I use VLOOKUP to return 0 instead of #N/A in Excel?," PSYCHOLOGICAL SCALES, vol. X, no. Y, ص Z-Z, June, 2024.
stats writer. How can I use VLOOKUP to return 0 instead of #N/A in Excel?. PSYCHOLOGICAL SCALES. 2024;vol(issue):pages.