一、效果展示
我们制作的可搜索下拉菜单效果如图所示。当在单元格中输入关键字后，下拉菜单的项目会随之变化，仅显示包含该关键字的项目。
以下是我们示例所使用的数据：
二、操作步骤详解
-
先将“菜品”表格通过【Ctrl+T】快捷键转化为超级表。这样一来，每当有新的菜品被添加进“点菜”工作表时，下拉菜单便会自动更新其菜品列表。
-
当在C2单元格输入特定关键字时，我们可以使用公式从A列中提取出符合条件的结果至E列。
具体地，构造公式的过程如下：
在G至J列设置四列辅助列后，“辅助列4”最终会返回我们期望的结果。
-
(1) 辅助列1的G2单元格公式：利用=ISNUMBER(SEARCH($C$2，A2))确认A2至A13中的菜品是否包含特定的关键字。
-
(2) 辅助列2的H2单元格公式：使用=IF(G2，ROW($A2)-1，"")来找出包含关键字的菜品在A2至A13中的位置。
-
(3) 辅助列3的I2单元格公式：利用=ALL($H$2:$H$13，ROW($A1))将含有关键字的菜品行号进行排序。
-
(4) 辅助列4的J3单元格公式：使用=IFERROR(INDEX($A$2:$A$13，I2)，"")从菜品清单中提取出符合条件的菜品。
-
以上就是提取包含特定关键字菜品的分步公式过程。
实际上，这些公式可以整合为一个复合公式：
=IFERROR(INDEX($A$2:$A$13，ALL(IF(ISNUMBER(SEARCH($C$2，$A$2:$A$13))，ROW($A$2:$A$13)-1，"")，ROWS($E$2:E2)))，"")。这个公式是数组公式，输入时需同时按住Ctrl+Shift+Enter。
-
为了使符合条件的菜品拥有动态名称，当搜索关键字变化时，符合条件的菜品数量也会随之变化。我们需要使用公式来定义这个动态名称。
具体操作是：点击【公式】-【定义名称】，打开【新建名称】对话框。
-
(1) 在【名称】框中输入定义的名称“符合条件的菜品”；
(2) 在引用位置输入以下公式：
=OFFSET(菜品!$E$2，0，0，COUNTIF(菜品!$E$2:$E$13，"？"))。此公式用于返回“菜品”工作表中E2至E13单元格的非空单元格区域。
-
接下来是设置数据有效性，以创建下拉菜单。
选择“点菜”工作表中需要设置下拉菜单的B2至B8单元格，然后点击【数据】-【数据验证】，打开数据验证对话框。
-
(1) 在【允许】选项中选择“序列”；
(3) 在【出错警告】中取消勾选“输入无效数据时显示出错警告”，以防止输入关键字后出现错误提示，从而确保下拉菜单能够正常显示包含关键字的选项。
-
在“菜品”工作表的C2单元格中输入以下公式：
=CELL("contents")。输入后按Enter键，若出现提示，请单击“确定”。