If you do a VLOOKUP to a reference cell which is empty, the VLOOKUP will return 0

To stop this you can apply a custom number format to the cell(s):

  • Select the cell(s)
  • from the Home tab select Format Cells
  • Select Number and Custom format
  • Enter custom number type : 0;;;@

 

Note: Whatever you put after the @ will be appended to the cell content

See https://support.office.com/en-gb/article/Display-or-hide-zero-values-3ec7a433-46b8-4516-8085-a00e9e476b03

Also see