Exceljet

Quick, clean, and to the point

VLOOKUP if blank return blank

Excel formula: VLOOKUP if blank return blank
Generic formula 
=IF(VLOOKUP(A1,data,col,0)="","",VLOOKUP(A1,data,col,0))
Explanation 

To check for empty cells in VLOOKUP results, you can combine the VLOOKUP function with the IF function. In the example shown, the formula in G5, copied down, is:

=IF(VLOOKUP(E5,data,2,0)="","",VLOOKUP(E5,data,2,0))

where "data" is the named range B5:C11.

Context

When VLOOKUP can't find a value in a lookup table, it returns the #N/A error. You can use the IFNA function or IFERROR function to trap this error. However, when the result in a lookup table is an empty cell, no error is thrown, VLOOKUP simply returns a zero.

This can cause problems when the lookup table contains actual zero values, because it suggests that blank cells in the lookup table also contain zeros, when they in fact are empty. To work around this problem you can test the result of VLOOKUP explicitly with the IF function, then return a custom result if you find an empty string.

How the formula works

To test the result of VLOOKUP directly, we use the IF function like this:

=IF(VLOOKUP(E5,data,2,0)="",""

Translated: if the result from VLOOKUP is an empty string (""), return an empty string.

If the result from VLOOKUP is not an empty string, run VLOOKUP again and return a normal result:

VLOOKUP(E5,data,2,0)

Note in both cases, the forth argument for VLOOKUP is set to zero to force an exact match.

Alternative with LEN or ISNUMBER

Depending on your needs, you can expand the idea above to run more specific tests. For example, to test for cells that literally have zero characters (i.e. a length of zero),  you can use the LEN function like this:

=IF(LEN(VLOOKUP(E5,data,2,0))=0,"",VLOOKUP(E5,data,2,0))

To test for numeric results only, you can use the ISNUMBER function, and reorder the logic like this:

=IF(ISNUMBER(VLOOKUP(E5,data,2,0)),VLOOKUP(E5,data,2,0),"")

Translated: if the result from VLOOKUP is a number, return a normal lookup. If not, return an empty string ("").

Author 
Dave Bruns

Excel Formula Training

Formulas are the key to getting things done in Excel. In this accelerated training, you'll learn how to use formulas to manipulate text, work with dates and times, lookup values with VLOOKUP and INDEX & MATCH, count and sum with criteria, dynamically rank values, and create dynamic ranges. You'll also learn how to troubleshoot, trace errors, and fix problems. Instant access. See details here.