使用Excel的【数据验证】功能创建下拉菜单应该是我表弟熟悉的技能。

当然，有些朋友可能还不知道。 不知道的人大概都是E世界的新人，他们是天真可爱的表兄弟，他们是早上八九点钟的太阳，他们是祖国——咳咳，作为来过的人，星光还是真诚地建议大家阅读EH公众号之前的文章，仔细研究一下【数据有效性】的相关知识——毕竟这部分知识是很酷很实用的。

我们这一期讲的也是与数据有效性相关的。 它利用CELL函数创建具有模糊查询效果的动态下拉菜单。

这是什么意思？ 我们用动态图来说话吧。

目标

在A列的某个区域输入关键字，包含该关键字的数据将显示在数据有效性下拉列表中。

材料

一个列表：

生产流程

在单元格 E2 中输入数组公式并将其填充到单元格区域 E10：

（左右拖动可查看完整公式）

=INDEX(D:D,SMALL(IF(ISNUMBER(FIND(CELL("contents"),D$2:D$10)),ROW($2:$10),4^8),ROW(A1)))&""

这个公式看上去很复杂，但是如果你学习并掌握了我们之前发的INDEX+SMALL+IF函数例程文章，理解起来就会简单很多。

CELL 函数省略第二个参数并获取最后更改的单元格的值。

FIND 函数查询CELL 函数的结果，查看它是否存在于单元格区域D2:D10 中。 如果存在，则返回一个位置值。 否则，它返回一个错误值并生成一个内存数组，例如：

{#VALUE!;4;4;2;#VALUE!;#VALUE!;…;#VALUE!}。

我们再看一下这部分：

IF(ISNUMBER(FIND(CELL("contents"),D$2:D$10)),ROW($2:$10),4^8)

该函数确定FIND函数的结果是否为数值。 如果是数值，则 IF 函数判断为真，并返回相关值对应的行号。 如果不是，则返回值 4^8，即 65536。

我们看一下完整的公式：

=INDEX(D:D,SMALL(IF(ISNUMBER(FIND(CELL("contents"),D$2:D$10)),ROW($2:$10),4^8),ROW(A1)))&""

SMALL函数将IF函数的结果从小到大取，随着公式向下填充，依次提取第1、2、3、4...N个最小值，从而得到条件满足条件 - 包括最后一次更改。 单元格值所在单元格的行号。

INDEX函数根据SMALL函数返回的索引值获取结果。

当SMALL函数得到的结果为4^8，即65536时，说明所有满足条件的行号都已经穷尽了。 此时，INDEX函数将返回单元格的值。 一般来说，具有如此大行号的单元格是空白单元格。 这时使用&""方法来避免空白单元格返回零值的问题，使其返回false。 无效的。

由于CELL("")获取的是最后更改的单元格的值，而编辑上面公式时的单元格就是最后更改内容的单元格，所以这会造成循环引用，不过不用担心。

将公式填入E10单元格后，结果如下：

在设置下拉菜单中选择单元格区域A2:A9，然后单击【数据】选项卡中的【数据验证】（版本13之前名为【数据验证】）。

在弹出的数据验证对话框中，单击【设计】选项卡上【允许】输入框右侧的按钮，在下拉列表中选择【序列】。

点击【】右侧的选择按钮，选择工作表的数据区域：$E$2:$E$10

单击[错误警告]选项卡并取消选中[输入无效数据时显示错误警告(S)]。

终于[确认]，完成了。

结论

挠头……那个……对于大多数表弟来说，今天分享的内容难度确实有点高。 我想很多人都很困惑。 难点在于长裹脚布的作用。

对于INDEX+SMALL+IF的函数套路，被戏称为万金油套路。 由此可见它有多么强大——作为早上八九点钟的太阳，E世界未来的希望，我们怎能不掌握它？

——新的一天了，兄弟姐妹们，好好学习吧！