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.

1. VLOOKUP Returns zero instead of #N/A

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.

=IFERROR(VLOOKUP("outlook",$B$1:$C$7,2,0),0)

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:

=IFERROR(VLOOKUP("excel",$B$1:$C$7,2,0),0)

The sales value for product excel has been extracted from the sales column.

2. Video:VLOOKUP Returns zero instead of #N/A

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)….