Excel表格中查找含指定子字符串的单元格的无LAMBDA公式问询

本方案兼容Excel 2007及以上所有正式发布版本，无需使用LAMBDA等预览阶段函数，返回结果为单元格引用，可直接使用:拼接其他单元格生成新区域。

通用公式 #

=INDEX(rng,SMALL(IF(ISNUMBER(SEARCH(txt,rng)),ROW(rng)-MIN(ROW(rng))+1),1),SMALL(IF(ISNUMBER(SEARCH(txt,rng))*(ROW(rng)-MIN(ROW(rng))+1=SMALL(IF(ISNUMBER(SEARCH(txt,rng)),ROW(rng)-MIN(ROW(rng))+1),1)),COLUMN(rng)-MIN(COLUMN(rng))+1),1))

参数说明：

• rng：替换为你要搜索的目标表格区域，例如B2:D5
• txt：替换为要匹配的指定文本，可以是直接输入的带引号文本（例如"im"），也可以是存储待匹配文本的单元格引用

输入说明 #

• 如果你使用的是Excel 365/2021及以后版本，直接输入公式回车即可生效
• 如果你使用的是Excel 2019及更早版本，输入公式后需要按下Ctrl+Shift+Enter三键组合以数组公式形式运行

效果验证 #

• 场景1：搜索区域B2:D5，指定文本im，公式返回单元格B2（值为Jim）
• 场景2：搜索区域B2:D5，指定文本omas，公式返回单元格C5（值为Thomas）

扩展说明 #

• 如果需要区分大小写匹配，将公式中的SEARCH替换为FIND即可
• 如果不存在匹配内容，公式默认返回#NUM!错误，可在外层嵌套IFERROR自定义无匹配时的返回结果，例如：=IFERROR(原公式,"无匹配结果")

内容的提问来源于stack exchange，提问作者SoftTimur