VLOOKUP if blank return blank

the VLOOKUP function searches for a value in a table you specified. If there is no value it looks for, It returns the #N/A error.

You can use two functions to deal with this problem: IFNA, and IFERROR.

If the result in a lookup table is an empty cell, VLOOKUP returns 0.

This can be a problem when the lookup table contains zeros, because you may wrongly assume that the lookup table contains zeros, when in fact it contains blank spaces.

To deal with this problem, you can check the result of VLOOKUP using the IF function, and return the custom result if you find blank space.

Return a blank if VLOOKUP is blank

This is the example, we are going to use.

This is the formula used in E2:

=VLOOKUP(A2,A1:B7,2,0)

This is used in F2:

=IF(VLOOKUP(A2,A1:B7,2,0)="","",VLOOKUP(A2,A1:B7,2,0))

Explanation

Let’s take a look at how the formula works.

The IF function returns TRUE if the condition is met and FALSE otherwise. But you can add additional arguments to this function in order to display the values you want. This is what we’ve done in this formula.

The VLOOKUP checks if the result is a blank space. If it’s true, the formula returns “”, otherwise it returns the VLOOKUP table. In our case, it’s a value from the Age column.

=VLOOKUP(A2,A1:B7,2,0)