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

If in your country sheet all countries are only present once you could use the following in stead:

=IF(ISNUMBER(OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0));
    OFFSET(countries!$B$1;MATCH(B2;countries!A:A;0)-1;0);
    "")

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

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

Concatenate ensures the data is treated like a string instead of a number. If the value is just an empty cell, you'll simply get an empty cell.

Related