vba Excel 找不到值，即使它存在于工作表中
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/19818691/
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 can't find a value even though it exists within the worksheet
提问by gatechuks
this is my first post so i am sorry if this is confusing at all. I am trying to use a vLookup to run a comparative analysis between two reports. I am using a part number as a reference and trying to return the cost associated with the part from one of the two reports. So, the first issue that I encountered was due to the fact that some of the part numbers had letters in them and some didn't, so to be consistent I used the following code to clean up the part numbers:
这是我的第一篇文章，所以如果这令人困惑，我很抱歉。我正在尝试使用 vLookup 在两个报告之间运行比较分析。我使用零件号作为参考，并试图从两份报告之一中返回与零件相关的成本。因此，我遇到的第一个问题是由于某些零件号中有字母而有些没有，因此为了保持一致，我使用以下代码来清理零件号：
IFERROR(VALUE(F11&C11), F11&C11)
IFERROR(值(F11&C11), F11&C11)
where F11 and C11 are two components of the part number that needed to be concatenated to generate the full number. Now, the vLookup will not return anything except for #N/A for a few of the part numbers that are actually in the sheet. All of the part numbers are formatted the same for the 892 part numbers that I am searching for but get a returned value on 571 of the 892 part numbers but of the remaining 321 part numbers that did not have a return, about a third actually exist in my sheet. Lastly and for example, part number 110874402 exists in both sheets but gets a #N/A from the vLookup. When I copy the value from one sheet and search it in the other sheet using Ctrl + F, I get the following:
其中 F11 和 C11 是零件号的两个组成部分，需要将它们连接起来以生成完整编号。现在，vLookup 不会返回任何内容，除了#N/A 之外的一些实际在工作表中的部件号。对于我正在搜索的 892 个零件编号，所有零件编号的格式都相同，但在 892 个零件编号中的 571 个零件编号上获得了返回值，但在其余 321 个零件编号中没有返回值，大约三分之一实际存在在我的工作表中。最后，例如，部件号 110874402 存在于两个工作表中，但从 vLookup 中获得 #N/A。当我从一张工作表复制值并使用 Ctrl + F 在另一张工作表中搜索它时，我得到以下信息：
(I have an image to show but apparently can't post it without a reputation of 10 or more...oops)
（我有一张图片要展示，但显然不能在没有 10 个或更多声誉的情况下发布它......哎呀）
The highlighted cell shows that the value exists but Excel can't find it. Does anyone have any ideas why this is or what I could be doing differently? I've been having this issue for a few months now on separate projects and haven't found any resolution.
突出显示的单元格显示该值存在但 Excel 找不到它。有没有人知道为什么会这样，或者我可以做些什么不同的事情？我已经在不同的项目上遇到这个问题几个月了，但没有找到任何解决方案。
Thanks in advance,
提前致谢，
回答by SeanC
try =VLOOKUP("*"&TRIM(F569)&"*", BOBJ!$D$3:$P$2237, 7, FALSE)- I have a feeling spaces may have crept around the part numbers, which means that the exact match will not work.
尝试=VLOOKUP("*"&TRIM(F569)&"*", BOBJ!$D$3:$P$2237, 7, FALSE)- 我有一种感觉，零件号周围可能会出现空格，这意味着完全匹配将不起作用。
The TRIM takes the spaces from the cell you are looking at, and the "*"'s will allow a wildcard search - note that this also means that CAT would also match CAT1, but if it produces results where there were none before, it gives you something to check for.
TRIM 从您正在查看的单元格中获取空格，并且"*"'s 将允许通配符搜索 - 请注意，这也意味着 CAT 也将匹配 CAT1，但如果它产生以前没有的结果，它会给你一些东西检查。
