Many extractions I did contain some empty cells or cells with 0. One of the options is to delete them but in some situation, I need to keep them. What I do most, it is to put another value, for example, all cells with zero, I will put “none” and for empty ones, “not related”.

When I use the formula ?

When I need to display another result if the cell is 0 or empty.

How to use the formula ?

The formula in this topic is with "," so depending of the operating system of your PC, the formula should have ";" instead of ",".

How are the formulas ?

=IF()

=VLOOKUP()

This formula is telling to display an empty cell if the value is 0.

=IF(VLOOKUP(A2,A:B,2,0)=0,"",VLOOKUP(A2,A:B,2,0))

In fact, you can ask it to put anything you want, if you want to display “nothing” for all 0 values, the formula will be:

=IF(VLOOKUP(A2,A:B,2,0)=0,"nothing",VLOOKUP(A2,A:B,2,0))

Of course, if you prefer to tell it to display the empty cell or “nothing” for another value, just change the “=0” to for instance “=5”.

This formula works the same way if the cell value is empty:

=IF(VLOOKUP(A2,A:B,2,0)="","empty",VLOOKUP(A2,A:B,2,0))

Instead to put the cell reference A2, you can put the name:

=IF(VLOOKUP("chocolate",A:B,2,0)=0,"",VLOOKUP("chocolate",A:B,2,0))

The point is that if you have 2 or more “chocolate”, the formula will only take the first one that it will find. Putting the name is better only if the name is unique and not duplicate.