Excel: How to leave cell empty (instead of 0) when VLOOKUP has no match?

Question

I have an excel workbook with 2 worksheet: countries and companies. The companies worksheet is partially filled-in through VLOOKUPs in the countries worksheet, as follows:

countries:

     A       B
1 COUNTRY HOF_LTO
2 Belgium    4
3 Japan      5
4 Spain
5 Sweden     0

companies:

    A         B       C
1 COMPANY  COUNTRY HOF_LTO
2 Belgacom Belgium    4
3 Onkyo    Japan      5
4 Sony     Japan      5
5 ATTM     Spain      0
6 Nokia    Sweden     0

I'm filling the C column by using the formula:

=VLOOKUP($B2;countries!$A$1:$C$5;MATCH(companies!B$1;countries!$1:$1;0);FALSE)

What goes wrong is: even though Spain does not have a HOF_LTO-value in countries, the formula puts 0 as the appropriate value in cell companies!C5. Instead it should leave the cell empty and only put in a zero (0) when it says 0 in the countries tab, as it does in the case of Sweden.

How do I need to edit the formula so it will leave cells empty when there is no country value to match?

Answer 1

This should solve it:

=VLOOKUP(...) & ""

This will force Excel into making that cell reference a text value, thus preventing the conversion of blanks into zeroes.

Taken from https://superuser.com/a/906954/222835