If the search result of the VLOOKUP function is blank, 0 is displayed instead of blank.
How to avoid.
There are two workarounds.
A similar workaround is possible with the XLOOKUP function.
Basically, you can use string concatenation, but there are a few problems, so we will compare the advantages and disadvantages of both methods.
| Method | Advantage | Disadvantage |
|---|---|---|
| String concatenation. | Formula is simple. | 1. When acquiring numerical values, the type of data may be problematic. 2. If someone who does not know this method sees it, it is difficult to understand what the string concatenation is intended for. |
| IF function. | 1. The intent of the formula is easily understood by those other than the creator. 2. The type of data does not matter. 3. Arbitrary display is possible. | The same VLOOKUP function needs to be written twice, complicating the formula. |
String concatenation.
Concatenate an empty string to the VLOOKUP function.
=VLOOKUP function&""
If the search result is actually 0, the display will also be 0.
However, since the data type is a string, it is left-justified.
For numerical values, there is no major problem, since they can be calculated even if they are strings.
However, in the case of dates, it is no longer possible to display the date from the serial value.
Even if the display format is changed, the serial value remains the same.
To avoid this problem, use the IF function.
IF function.
Use the IF function in the following cases.
- If you do not want to change the data type.
- When you want to display non-whitespace characters such as “-” or “Empty”.
= IF(VLOOKUP function="" "String to be displayed when blank",VLOOKUP function)
The two VLOOKUP functions are exactly the same formula.
No problem with date data.
If the LET function can be used, the formulas are simpler because the VLOOKUP function does not need to be doubled.
=LET(Arbitrary name, VLOOKUP function formulas, IF(Arbitrary name="","",Arbitrary name) )
=LET(v, VLOOKUP(C2,B6:C8,2,FALSE), IF(v="","",v) )
---