# Excel - 如何根据A列中的多行在B列中查找字符串并返回C列中的相应行？(Excel - How to find strings in Column B based on multiple rows in Column A and return the corresponding row in Column C?)

``` 我在Excel 2007+中有4列。
Col A包含搜索文本（我想要搜索的文本）。 Col B包含我要搜索的所有文本。 Col C包含另一组不会被搜索的值，但如果匹配则将输出到Col D.
基本上，我在Col D中需要一个公式，其逻辑如下：
```FOR EACH row in Col A,
IF (a row in B) contains text from each row in A, D = C.
```
例：
```  A     B         C         D
1 dog   dog bone  dog woof  dog woof
2 cat   dog bait  dog meow  dog meow
3 bird  wolf      asd
4 pig   cat       we        we
```
如您所见，在整个列B中搜索A1。对于Col B中与A1匹配的每一行，C中的相应行输出到D.由于在B1和B2中找到字符串“dog”， C1和C2分别输出到D1和D2。
B3与Col A不匹配或包含任何文本，因此不输出任何内容。
B4包含来自A2的“cat”，因此C4输出到D4。
搜索结果完成后，我将基于Col D过滤表。基本上我想排除Col，其中Col B不包含Col A中任何行的文本，我根据此过滤器检查Col C的数据。
我试过使用INDEX和MATCH以及SEARCH，但我无法绕过公式及其输出。
请帮忙... I have 4 columns in Excel 2007+.
Col A contains search text (text that I want to search with). Col B contains all the text that I want to search from. Col C contains another set of value that will not be searched, but will be output to Col D if there is a match.
Basically, I need a formula in Col D whereby the logic goes like this:
```FOR EACH row in Col A,
IF (a row in B) contains text from each row in A, D = C.
```
Example:
```  A     B         C         D
1 dog   dog bone  dog woof  dog woof
2 cat   dog bait  dog meow  dog meow
3 bird  wolf      asd
4 pig   cat       we        we
```
As you can see, a search for A1 is performed in the entire column B. For every row in Col B that matches A1, the corresponding row in C is output to D. Since the string "dog" is found in B1 and B2, C1 and C2 are output to D1 and D2 respectively.
B3 does not match or contain any text from Col A, so nothing it output.
B4 contains "cat" from A2, so C4 is output to D4.
After the search result is done, then I will filter the table based on Col D. Basically I want to exclude rows whereby Col B contains no text from any rows in Col A, and I check Col C's data based on this filter.
I've tried to use INDEX and MATCH and SEARCH, but I just can't wrap my head around the formula and its output.

## 最满意答案

``` 当您使用工作表公式轻松获得所需结果时，我会建议您反对VBA
将以下公式放入单元格D1并向下拖动
```{=IF(SUM(IFERROR(FIND(\$A\$1:\$A\$4,\$B1),0)),\$C1,"")}
```
这是一个数组公式，所以省略花括号并使用Ctrl + Shift + Enter输入公式 I would advise against VBA when you can achieve the required results easily with worksheet formulas
Put the following formula into cell D1 and drag down
```{=IF(SUM(IFERROR(FIND(\$A\$1:\$A\$4,\$B1),0)),\$C1,"")}
```
It's an array formula so leave out the curly braces and enter the formula using Ctrl+Shift+Enter```
2014-10-24

## 我应该在哪里压平Backbone中要收集的项目数组？(Where should I flatten array of items for collection in Backbone?)

var ListView = Backbone.View.extend({ ... initialize: function() { this.collection.on('reset', this.render, this); } ... }); var // create collection list = new List(), // create view listView = new ListView({