Explanation
In this example, the goal is create a VLOOKUP formula that will return an empty cell when the lookup result is an empty cell.
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.
With IF function
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)
In both cases, the fourth argument for VLOOKUP is set to zero to force an exact match.
Note: you can use the same general approach with the XLOOKUP function .
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 ("").
Explanation
Note: a simpler approach would be to alter the table used by VLOOKUP directly. But this example explains the mechanics of testing and overriding output from VLOOKUP.
This formula is based on a simple grading example explained in detail here . For a given score, VLOOKUP uses a existing table, the named range key (B5:C9), to calculate a grade. Note match mode is set to approximate.
To override output, VLOOKUP is nested in an IF statement:
=IF(VLOOKUP(F5,key,2,TRUE)="F","x",VLOOKUP(F5,key,2,TRUE))
The literal translation of this formula is:
If VLOOKUP returns “F”, return “x”. Otherwise, return the result from VLOOKUP.
The result of “x” can be customized as desired. To display nothing, provide an empty string ("").
Alternative formula
A simpler, less redundant formula can be created by using IF to check the score directly like this:
=IF(F5<60,"x",VLOOKUP(F5,key,2,TRUE))
However, this formula does not technically override the output of VLOOKUP. Instead, it tests the incoming score value and bypasses VLOOKUP entirely if below 60.
