当VLOOKUP没有匹配时，如何使单元格为空(而不是0)?

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:

我有一个excel工作簿，有两个工作表:国家和公司。这些公司的工作表通过在国家工作表上的vlooup部分填写，如下:

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:

我用公式来填充C列

=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.

错误的是:即使西班牙在国家中没有HOF_LTO-value，但是这个公式将0作为细胞公司的适当价值!C5。相反，它应该让单元格为空，并且只在“国家”选项卡中输入0(0)，就像在瑞典那样。

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

我要如何编辑这个公式，这样当没有国家值匹配时，它就会让细胞空出来?

5 个解决方案

#1

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);
    "")

#2

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.

这将迫使Excel使该单元格引用一个文本值，从而防止将空格转换成零。

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

从https://superuser.com/a/906954/222835

#3

=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.

连接确保数据被视为字符串而不是数字。如果这个值只是一个空单元，那么您将得到一个空单元格。

#4

Alternatively, if it is just the presentation of the data that you need to control, adjust the cell formatting to display 0 values as blank instead of as the digit zero.

或者，如果只是表示需要控制的数据，那么调整单元格格式，以显示0值为空白，而不是为0。

#5

-1

Simple solution: Do a find and replace on the full array of your countries source workbook, replacing empty cells with @. (make sure "Find entire cells only" is checked when you do the Replace All.) Then go back and run your vlookups. Copy and paste values to get rid of the vlookups and only keep the results. Now search and replace @ symbols with nothing in the destination file. Voilà.

简单的解决方案:在您的国家源工作簿的完整数组中查找并替换，用@替换空单元格。(当你做替换的时候，一定要检查“发现整个细胞”。)然后返回并运行您的vlook。复制和粘贴值，以清除vlookup，只保留结果。现在，在目标文件中搜索并替换@符号。瞧。

#1