vba Excel VLOOKUP 和 SEARCH 组合
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/23811521/
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 and SEARCH combination
提问by LauraB
I'm trying to search for part of a text string, in a column of text and return the second column. Hopefully this will make more sense with an example (note that this example is made up - I cannot post the exact data I'm using but this is similar to it).
我试图在文本列中搜索文本字符串的一部分并返回第二列。希望这对示例更有意义（请注意，此示例是虚构的 - 我无法发布我正在使用的确切数据，但这与它类似）。
For example:
例如：
A D E
Really good dog Good dog text1
red dog collar Brown dog text2
Brown Toy dog big dog text3
dog collar text4
dog walking text5
A B
Really good dog text1
red dog collar text4
Brown Toy dog Not Found
So column D is searched through to provide a match to the first value in column A, if there's a match it returns the corresponding text in column E, if there is no match it returns "Not Found". As you can see from the expected results, column B, the string "Good dog" appears within "Really good dog" so "Really good dog" has a result of "text1". Note that case does not matter.
因此，搜索 D 列以提供与 A 列中第一个值的匹配项，如果匹配，则返回 E 列中的相应文本，如果没有匹配项，则返回“未找到”。从预期结果中可以看到，B 列中的字符串“Good dog”出现在“Really good dog”中，因此“Really good dog”的结果为“text1”。请注意，大小写无关紧要。
I've been playing around with
我一直在玩
=(VLOOKUP("*"&DCE!X2&"*",Lookups!$K:$L,2,FALSE))
which does not work because it's not all of the text string in the cell that could be matched to a value. I've tried using
这不起作用，因为并非单元格中的所有文本字符串都可以与值匹配。我试过使用
=IF(SUMPRODUCT(--(NOT(ISERR(SEARCH($A:$A,C1)))))>0,"1","")
which would be great if I could swap the range with the cell within SEARCH.
如果我可以将范围与 SEARCH 中的单元格交换，那就太好了。
I know there's a MATCH version of these formulae but I haven't been able to get that to work either.
我知道这些公式有 MATCH 版本，但我也无法让它发挥作用。
The only formula I have working is a huge nested IF statement
我使用的唯一公式是一个巨大的嵌套 IF 语句
=IF(ISNUMBER(SEARCH(Lookups!$K,X2)),Lookups!$L,IF(ISNUMBER(SEARCH(Lookups!$K,X2)),Lookups!$L,IF(ISNUMBER(SEARCH(Lookups!$K,X2)),Lookups!$L,IF(ISNUMBER(SEAR......
which I do not want to be creating for 100+ items to search for. This sees if the first value in the search column (column D) is contained within the lookup string (column A) and if it is it returns the result (column E) and if it is not it returns "Not Found".
我不想为 100 多个要搜索的项目创建。这会查看搜索列（D 列）中的第一个值是否包含在查找字符串（A 列）中，如果是，则返回结果（E 列），如果不是，则返回“未找到”。
Also, the cell references in the above formulae do not directly relate to the example, they're just examples of the formulae I have been trying (I've found on various posts on this site).
此外，上述公式中的单元格引用与示例没有直接关系，它们只是我一直在尝试的公式示例（我在本网站的各种帖子中找到了）。
Any advice would be greatly appreciated. Sorry if this is difficult to follow through, I'm fairly new to Stackoverflow.
任何建议将不胜感激。抱歉，如果这很难理解，我对 Stackoverflow 还很陌生。
采纳答案by Ioannis
Nice question! This works for the example you provided:
好问题！这适用于您提供的示例：
It is an array formula, so it needs to be confirmed with Control+Shift+Enter.
是数组公式，需要用Control+Shift+Enter确认。
What it does is it creates an array of Find's and casts them to boolean (the NOT((NOT...))part. Then the --part converts the boolean to 1and MATCHfinds the place of that 1in the array, which is the row that we need. This will work as long as the data start from row 1, but it can be easily modified to work otherwise. I am not entirely sure it meets all your needs though.. For example, a potential issue is that if there are two identical strings in column B, it will return the upper one, due to the way the MATCH()function works.
它所做的是创建一个Find's数组并将它们转换为布尔值（NOT((NOT...))部分。然后--部分将布尔值转换为1并MATCH找到1数组中的位置，即我们需要的行。这将工作因为数据从第 1 行开始，但它可以很容易地修改为以其他方式工作。但我不完全确定它满足您的所有需求。例如，一个潜在的问题是，如果列中有两个相同的字符串B，它将由于MATCH()函数的工作方式，返回上一个。
Sneaky but it probably does the job!
偷偷摸摸，但它可能会起作用！
