vba Excel VLOOKUP N/A 错误
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/17194239/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Excel VLOOKUP N/A error
提问by stackErr
I have a table setup like this:
我有一个这样的表设置：
I am trying to do a lookup, where Column D value matched one of the Column A values and returns Column C value.
我正在尝试进行查找，其中 D 列值与 A 列值之一匹配并返回 C 列值。
The numbers in column A and D are stored as text.
A 列和 D 列中的数字存储为文本。
My formula is VLOOKUP(F3,A1:C3,3,TRUE)but this returns "Value not available error". What is wrong with the formula?
我的公式是，VLOOKUP(F3,A1:C3,3,TRUE)但这会返回“值不可用错误”。公式有什么问题？
EDIT
编辑
Figured out that some of the values were stored as general.
发现某些值被存储为一般值。
Now the problem is that I have to get an exact match with leading zeroes. For example in Column D I have "27154" but in Column A I have "000027154", these should match.
现在的问题是我必须与前导零完全匹配。例如，在列 DI 中有“27154”但在列 AI 中有“000027154”，这些应该匹配。
But if I have "000271540" or any other variant in Column A, it should not match.
但是如果我在 A 列中有“000271540”或任何其他变体，它不应该匹配。
All the numbers in Column A are 9 digits long with leading zeroes where needed.
A 列中的所有数字都是 9 位数字，需要时在前导零。
回答by shahkalpesh
VLOOKUP(TEXT(F3, "000000000"),A1:C3,3,FALSE)
It will require creating the same value for the VLOOKUPto find the value. Looking at your example, the length of the text in column A is 9 characters. As a result, the padding is applied which will be used to search. To make it exact match, FALSEis used as last argument to VLOOKUP.
它将需要创建相同的值VLOOKUP才能找到该值。查看您的示例，A 列中的文本长度为 9 个字符。结果，应用了将用于搜索的填充。为了使其完全匹配，FALSE用作VLOOKUP.
回答by Skip Intro
You can use wildcards in VLOOKUP:
您可以在VLOOKUP以下位置使用通配符：
=VLOOKUP("*"&F3,A2:C3,3,FALSE)
回答by Joseph
There's nothing wrong with the formula. The problem is that the value in A2 is text and treated as text when comparing to the number in F3.
公式没有问题。问题是 A2 中的值是文本并在与 F3 中的数字进行比较时被视为文本。
If you can't change your values in column A, then you can use this array formula:
如果您无法更改 A 列中的值，则可以使用此数组公式：
=SUM((F3=VALUE(A2:A3))*(C2:C3))
Enter with CTRL+SHIFT+ENTER
使用 CTRL+SHIFT+ENTER 输入
This will convert the values in A2:A3 as numbers for the comparison against F3.
这会将 A2:A3 中的值转换为数字，以便与 F3 进行比较。
