按 Excel 任意列查询，一对多找出所有结果，妈呀，放弃得了

一对多查询本来已经挺难的，如果还要将任意列作为查询项，一对多查询出结果，这个是不是更难？

案例：

下图 1 是一个防疫物品团购表，请制作一个下拉菜单，可以按楼号或者物资查询，不但能列出所有查询结果，还能在原来的表上高亮显示。

效果如下图 2 所示。

GIF

解决方案：

先制作下拉菜单。

1. 将 B、C 列的内容部分复制粘贴到任意空白区域。

2. 选中 S 列 --> 选择菜单栏的“数据”-->“删除重复值”

3. 在弹出的对话框中选择“以当前选定区域排序”--> 点击“删除重复项”

4. 点击“确定”

5. 点击“确定”

6. 用同样的方式给 T 列去重。

7. 将去重后的两列合并成一列。

8. 在 E 列设置下拉菜单，选中 E2 单元格 --> 选择菜单栏的“数据”-->“数据验证”

9. 在弹出的对话框中选择“设置”选项卡，按以下方式设置 --> 点击“确定”：

• 允许：选择“序列”

• 来源：选择刚才去重后合并的序列
  

下拉菜单已做好。

接下来设置公式。

10. 将 D 列作为辅助列，在 D2 单元格中输入以下公式 --> 下拉复制公式：

=OR((A2=$E$2),(B2=$E$2))+D1

公式释义：

• 只要 A2 或 B2 单元格任有一个与 E2 的查询值相同，辅助列的值就在上一个单元格的基础上 +1；

• 也就是说，符合查询项的每一行，都对应着一个新的递增值第一次出现的位置；一对多查询的时候，总是查找出重复项的第一个值，这样就能查找出符合查询条件的所有值；
  

11. 将 G 至 I 列设置为查询结果区域，在 G2 单元格中输入以下公式 --> 向右向下拖动公式：

=IFERROR(INDEX(A$2:A$29,MATCH(ROW(A1),$D$2:$D$29,0)),'')

公式释义：

• MATCH(ROW(A1),$D$2:$D$29,0)：在 D 列的数值区域精确查找 A1 的行值，即 1，并返回查找结果在整个序列中的排列位置；当公式下拉，row 函数随着行值递增会返回逐渐递增的序列；

• INDEX(A$2:A$29,...)：在区域 A$2:A$29 中查找出 match 函数结果所对应的同一行的值；

• IFERROR(...,'')：找不到就留空

* 请注意参数的绝对和相对引用。

最后设置自动高亮显示结果。

12. 选中 A2:C29 区域 --> 选择菜单栏的“开始”-->“条件格式”-->“新建规则”

13. 在弹出的对话框中选择“使用公式确定要设置格式的单元格”--> 输入以下公式 --> 点击“格式”按钮：

=OR(($A2=$E$2),($B2=$E$2))

公式释义：

• A 或 B 列中只要有单元格等于查询值，就触发；

* 这个公式一定要注意参数绝对和相对引用，$E$2 单元格必须绝对引用，而 A、B 列的单元格要列值固定，行值不固定。

14. 在弹出的对话框中选择“填充”选项卡 --> 选择所需的填充色 --> 点击“确定”

15. 点击“确定”

设置好了。

GIF