This post will guide you how to VLookup and return zero instead of #N/A in Excel. How do I use VLookup function and return zero instead of #N/A if not found in Excel. How to display zero instead of #N/A when using VLOOKUP in Excel. How to replace #N/A with Zero when VLOOKUP.
Table of Contents
The VLOOKUP function is one of the most useful function to find data in a given range of cells in Excel. And if the VLOOKUP function cannot find the result that it is looking for, and it will display a #N/A error. And if you would like it to display a “0” instead of #N/A. How to achieve it.
Assuming that you have a list of data in range B1:C7 which contain the product names and sales data, and you want to use Vlookup function to lookup the product “outlook” in range B1:C7, and the return the sales value in sales column.
Type the following formula in a blank cell and then press Enter key in your keyboard.
From the returned result, you can know that the error message #N/A has been replaced with number 0.
Let’s try to look the product “excel ” in range B1:C7, type the following formula in a blank cell:
The sales value for product excel has been extracted from the sales column.
This video will show you how to modify the VLOOKUP formula to return zero instead of #NA in Excel.
3. Related Functions
- Excel VLOOKUP function
The Excel VLOOKUP function lookup a value in the first column of the table and return the value in the same row based on index_num position.The syntax of the VLOOKUP function is as below:= VLOOKUP (lookup_value, table_array, column_index_num,[range_lookup])….
- Excel IFERROR function
The Excel IFERROR function returns an alternate value you specify if a formula results in an error, or returns the result of the formula.The syntax of the IFERROR function is as below:= IFERROR (value, value_if_error)….