Excel VLOOKUP和SEARCH组合 [英] Excel VLOOKUP and SEARCH combination

问题描述

我正在搜索文本字符串的一部分，并在文本列中返回第二列。希望这将更有意义的一个例子（注意，这个例子是弥补的 - 我不能发布我正在使用的确切数据，但这是类似的）。

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

例如：

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

所以列通过搜索D来提供与A列中的第一个值的匹配，如果匹配返回列E中的相应文本，如果没有匹配则返回未找到。从预期结果可以看出，列B，字符串好狗出现在真正好狗中，所以真正好狗的结果是text1。请注意，这种情况并不重要。

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.

我一直在玩弄

=(VLOOKUP("*"&DCE!X2&"*",Lookups!$K$2:$L$19,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$1:$A$3,C1)))))>0,"1","") 

如果我可以使用SEARCH中的单元格来交换范围，那将是非常好的。

which would be great if I could swap the range with the cell within SEARCH.

我知道这些公式有一个MATCH版本，但是我还没有得到

I know there's a MATCH version of these formulae but I haven't been able to get that to work either.

我工作的唯一公式是一个巨大的嵌套IF语句

The only formula I have working is a huge nested IF statement

=IF(ISNUMBER(SEARCH(Lookups!$K$2,X2)),Lookups!$L$2,IF(ISNUMBER(SEARCH(Lookups!$K$3,X2)),Lookups!$L$3,IF(ISNUMBER(SEARCH(Lookups!$K$4,X2)),Lookups!$L$4,IF(ISNUMBER(SEAR......

我不想创建100个以上的项目进行搜索，这可以看到搜索列（列D）中的第一个值是否包含在查找字符串中列A），如果是，则返回结果（列E），如果不是，则返回未找到。

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

此外，上述单元格引用公式没有直接涉及到这个例子，他们只是我一直在尝试的公式的例子（我在这个网站的各种帖子上发现）。

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

任何建议非常感谢对不起，如果这很难跟进，我对Stackoverflow来说相当新鲜。

Any advice would be greatly appreciated. Sorry if this is difficult to follow through, I'm fairly new to Stackoverflow.

推荐答案

很好的问题！这适用于您提供的示例：

Nice question! This works for the example you provided:

它是一个数组公式，所以需要用Control + Shift + Enter确认。

It is an array formula, so it needs to be confirmed with Control+Shift+Enter.

它的作用是创建一个数组 Find 并将其转换为布尔值（ NOT（（NOT ...）） part，然后 - 部分将布尔值转换为 1 和 MATCH 在数组中找到 1 的位置，这是我们需要的行，只要数据从第1行，但是它可以很容易地被修改以另外工作。我不完全确定它满足了你所有的需要。例如，一个潜在的问题是，如果列中有两个相同的字符串 B ，它将返回上一个，由于 MATCH（）功能的作用方式。

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 1 and MATCH finds the place of that 1 in 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.

鬼鬼祟祟，但这可能是工作！

Sneaky but it probably does the job!

这篇关于Excel VLOOKUP和SEARCH组合的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！