excel中查找所含内容并返回列号

Question

A B C
1 there Only a few people know it.
2 know Few people agree to do so.
3 finish There is a little bird singing in the tree.
4 few There is little time left.
5 little I have little money, so I have few friends.
6 history He speaks a little Spanish.
7 picked I have too little time to finish this work.
8 quite I know little about their history.
9 money I picked lots of flowers.
10 friend He has quite a few friends.
如上内容所示,如果在C列中找到了B1中所含的单词,则在D1中返回该单词所在例句所对应的A列的号数. 比如:b1中是there, C3,C4中都有there这个词,则在d1中返回"3,4". 能不能用公式解决这个难题?
因为提交后格式变了,现以图片格式再补充问题.

Answer 1

这个用公式实现不了，就算能搜出来是 3 4 ，也玩不成最后一步——合成到一个单元格D1内，"3,4"

我降低了标准，将合适的序号分单元格存放
在D1内输入公式，数组公式（按Ctrl + Shift + Enter 三键结束公式的输入），
先下拉复制，再选中D列，右拉复制，直到显示为空

=IF(LARGE(ISNUMBER(SEARCH($B1,$C$1:$C$10))*ROW($C$1:$C$10),COLUMN(A1))=0,"",LARGE(ISNUMBER(SEARCH($B1,$C$1:$C$10))*ROW($C$1:$C$10),COLUMN(A1)))本回答被提问者采纳

Answer 2

时间紧迫，简单介绍。执行“数据-筛选-高级筛选”可以根据高级筛选中填写筛选条件.

参考资料：老师教导