To return a blank cell instead of #N/A when using the VLOOKUP function, you can add the IFERROR function to your formula. The syntax for IFERROR is: =IFERROR(value, value_if_error) In this case, the value would be the VLOOKUP function, and the value_if_error would be an empty quotation marks (“”). The updated formula would look like this: =IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), “”) This will return a blank cell if the VLOOKUP function returns an error, such as #N/A. If the VLOOKUP function returns a valid result, it will be displayed as normal. Note: This solution will work for any type of error, not just #N/A. You can use it to return a blank cell for any type of error that may occur.

By /

To ensure that a blank cell is returned instead of the #N/A error when using the VLOOKUP function, the IFERROR function can be incorporated into the formula. The IFERROR function allows for a value to be specified in case of an error, and in this case, an empty quotation mark is used. The updated formula will look like “=IFERROR(VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup]), “”)”. This solution works for any type of error that may occur, not just #N/A.

Excel: Use VLOOKUP to Return Blank Instead of #N/A

You can use the following formula to return a blank value instead of #N/A when using the VLOOKUP function in Excel:

=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))

This particular example attempts to look up the value in cell D2 in the range A2:B11 and return the corresponding value in the second column of the range.

If the lookup value is not found, then a blank is returned.

The following example shows how to use this syntax in practice.

Example: Return Blank 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 “Jazz” in the dataset and return the corresponding value from the points column:

=VLOOKUP(D2,A2:B11,2,FALSE)

The following screenshot shows how to use this formula in practice:

Since “Jazz” does not exist in the team column, the VLOOKUP function returns #N/A as a result.

However, we can use the following formula to return a blank instead of #N/A:

=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))

The following screenshot shows how to use this formula in practice:

How This Formula Works

Recall the formula that we used to return a blank instead of #N/A using VLOOKUP:

=IF(ISNA(VLOOKUP(D2,A2:B11,2,0)),"",VLOOKUP(D2,A2:B11,2,0))

Here is how this formula works:

The ISNA function returns TRUE or FALSE to indicate whether or not the output of the VLOOKUP function is #N/A.

Our formula then uses an IF function to check if the result of ISNA is TRUE or FALSE.

If the result is TRUE (i.e. the lookup value was not found) then the formula returns a blank.

If the result is FALSE (i.e. the lookup value was found) then the formula simply returns the result of the VLOOKUP function.

Additional Resources

The following tutorials explain how to perform other common tasks in Excel:

x

Related terms:

  1. How can I create an Excel formula that returns the value of another cell if a certain cell is blank?
  2. How do I use VLOOKUP to Return Blank Instead of #N/A in Excel?
  3. How can I use VLOOKUP to return blank instead of zero in Excel?
  4. How can I use IFERROR in Google Sheets to display a blank cell if an error occurs?
  5. Question: Excel: Return Blank Cell Instead of Zero in Formulas
  6. Excel Formula: If Blank Then Return Value of Another Cell
  7. How to Return 0 Instead of #N/A with VLOOKUP in Excel?
  8. How do you write an Excel formula that checks if the month is January and returns a specific value?
  9. How do I create an Excel formula that will return a blank cell if the condition is false?
  10. How to Use VLOOKUP to Return Value in Next Cell in Excel?