Excel 使用关键词搜索并返回多个匹配的数据

大家好,今天和大家分享一个典型的需求的解决方法,即使用关键词在一列种查找,返回与包含关键词的所有数据。

今天的文章,给大家介绍使用公式达到上述效果。

公式

首先给出通用公式:

=IF(搜索词="","",INDEX(元源数据列,SMALL(IF(ISERROR(SEARCH(搜索词,源数据区域)),大于源数据区域最后一行的的数字,ROW(源数据区域)),ROW(A1)))&"")

公式是数组公式,需要以三键输入(Ctrl+Shift+Enter)。公式中,

  • 搜索词表示关键词所在单元格,使用绝对引用方式;
  • 源数据列表示源数据所在列;
  • 源数据区域,使用绝对引用方式;
  • 大于源数据区域最后一行的的数字,字面意思,需要大于源数据区域最后一个单元格的行数。

在本例种,对应的公式如下:

=IF($E$2="","",INDEX(B:B,SMALL(IF(ISERROR(SEARCH($E$2,$B$3:$B$11)),1000,ROW($B$3:$B$11)),ROW(A1)))&"")

 

第一个单元格输入上述公式后,下拉复制到余下单元格。关键词单元格输入内容,在下方区域种就可以显示包含关键词的所有结果。

今天就到这里,有什么问题可以留言交流。

转载注明:文章转载自「懒人Excel - www.lanrenexcel.com

您可能还喜欢...

订阅评论
提醒
37 评论
最新
最旧 最多投票
内联反馈
查看所有评论
77*
3 月 前

整张表格能用这个方法搜索吗

西里麻辣红
1 年 前

把公式复制,数据套用原数据,也没实现这个功能,这里有个疑问,search 是寻找单元格内查找内容的未知,这里用到数列,仅对B3有用啊,不大理解

走来走去
1 年 前

点击搜索结果能定位 就好了

dyq
1 年 前

搜索范围是多列数据怎么办呢?

包子包子
2 年 前

如果我想把搜索出来的多个数据都放在同一个单元格,可以实现吗?各位大大

Hering
2 年 前

很好的组合!就是有个希望改进的地方:如果B列里面有重复值,通过此公式无法实现去重复值的效果,可以实现吗?

Hering
2 年 前
回复给  懒人Excel

我目前的做法就是通过辅助列来实现,即先在辅助列提取出非重复项,再用关键字检索函数实现此目的

Hering
2 年 前
回复给  懒人Excel

我在想是不是可以将去重复的辅助列转化成一个虚拟数列,在通过类似提取关键字函数来实现,不知道有没有这种函数

Hering
2 年 前
回复给  懒人Excel

我目前的做法只有通过添加辅助列,即在辅助列里面先提取出非重复项,再使用提取关键字组合公式来实现,我一直在找此问题的函数公式解决办法,目前都是将将这两个功能分开展示,没有将关键字检索及去重复值结合起来使用

897312258
2 年 前

都看不懂

Liqr
2 年 前

想问一下,输完公式后也三键了,输入关键词返回的是错误值是为什么。
我成功了,刚刚是在表格的中间处理的,这次移到最上方就可以了,这又是为什么呢

最后由Liqr编辑于2 年 前
robby
2 年 前

这个公式的应用场景跟普通的筛选功能差异在哪呢?

mcroni
2 年 前

如果数据源是多列表格,查询结果可以返回多列吗?

masterqt
3 年 前

为什么最后要加&“”呢 是变成文本吗 不理解如果不加 会出现什么困扰?

xunzi
3 年 前

是在输入公式前就要(Ctrl+Shift+Enter)吗?然后结束后也要?

neon
3 年 前

终于解决 感谢

excel小白
3 年 前

要是返回两列的数据怎么做,比如在小区后面加个店址
输入AA,既要找出小区同时小区地址也有

wq123
3 年 前

想问一下,上面第一个动图,E2单元格输入时(还未按enter确认)下面公式就会自动计算,这是新版才有的功能吗?我是2010版,只有按了enter下面的公式才会计算……

笑笑生
3 年 前

最后那个 ROW(A1) 是什么意思

15761635831
3 年 前
回复给  懒人Excel

那要是我的左侧列是从a列开始的呢,好像公式就用不了

会计狗
4 年 前

这个公式还没有学会,倒是学会数组的意思了,感谢

ppstc
4 年 前

下拉复制到余下单元格后依然是相同的结果请问是什么?

15761635831
3 年 前
回复给  ppstc

输入完数组公式后按三键结束公式,再下拉复制余下单元格

37
0
希望看到您的想法,请您发表评论x