我继承了一些非常旧的工作簿，它们就像一堆乱七八糟的链接和查找。我正在整理它们，并试图让工作表结构更合理。我经常遇到的一个问题是格式不匹配。我目前使用这个公式，它能满足我的需求：
=ifna(vlookup(A1&"",data!$a$1:$b$2000,2,0),vlookup(value(A1),data!$a$1:$b$2000,2,0))
但如果存在的话，我希望它更整洁。
有没有更简单的方法可以将文本作为数字进行搜索，将数字作为文本进行搜索而不改变源数据？
答案1
要将文本搜索为数字，请使用 =NUMBERVALUE()。这会将保存为文本的数字转换为数字，并且不会对保存为数字的数字产生任何影响。如果单元格包含字母，则会返回 #VALUE! 错误。
要将以文本形式存储的数字转换为数字，您还可以使用双重否定，如下所示。两个否定等于一个肯定，但它也会将像 '1000 这样的文本转换为像 1000 这样的数字。
=--B3
要将数字作为文本进行搜索，请使用 =TEXT()。这会将数字转换为文本。请查看帮助菜单以了解选项。
您可以在公式中使用这些，这样您的查找就不会因不匹配而产生混淆。例如：
=VLOOKUP(NUMBERVALUE(B3),B:B,1,FALSE)
=VLOOKUP(TEXT(B3,"#"),B:B,1,FALSE)
答案2
使用：
=VLOOKUP( TEXT(C1,"@"), TEXT($A$1:$B$2000,"@"), 2, FALSE)
您可以使用一种简单的方法来处理您的范围：TEXT()同时使用查找值和查找范围。
我发现 Excel 的常见字符串长度限制不会影响使用，TEXT()即使使用 108,000 个单元格的查找列。当限制时，Excel 在公式中生成的字符串的通常最大值为 32,767 个字符左右，并且通常会低得多。因此，如果它要为公式构建一个至少为 108,000 个字符的内部字符串（而我的数据每个单元格不止一个字符，因此要长得多），它肯定超出了我所见过或听说过的任何此类限制，因此VLOOKUP()一定不是以这种方式限制的函数。所以TEXT()应该总是没问题的。
似乎可以使用VALUE()替代方法，但如果整个查找范围内的任何材料为非数字，则该方法会失败。所以... 没有帮助。
因此，这是一个简单的公式，没有“考虑这个，考虑那个”。没有错误。没有顾虑。
扩展此功能使用的注意事项：如果您遇到更复杂的情况，其中一个值可以以两种方式出现（在一个单元格中格式化为数字的数字字符，以及在另一个单元格中格式化为文本的数字字符 - 因此 6 和“6”都会出现），您不能使用此功能，因为它会消除这种区别并返回先出现的结果。它确实实现了这个用例，但将其扩展到更复杂的用例则需要仔细考虑。