一對多查詢本來已經(jīng)挺難的，如果還要將任意列作為查詢項，一對多查詢出結果，這個是不是更難？

案例：

下圖 1 是一個防疫物品團購表，請制作一個下拉菜單，可以按樓號或者物資查詢，不但能列出所有查詢結果，還能在原來的表上高亮顯示。

效果如下圖 2 所示。

GIF

解決方案：

先制作下拉菜單。

1. 將 B、C 列的內(nèi)容部分復制粘貼到任意空白區(qū)域。

2. 選中 S 列 --> 選擇菜單欄的“數(shù)據(jù)”-->“刪除重復值”

3. 在彈出的對話框中選擇“以當前選定區(qū)域排序”--> 點擊“刪除重復項”

4. 點擊“確定”

5. 點擊“確定”

6. 用同樣的方式給 T 列去重。

7. 將去重后的兩列合并成一列。

8. 在 E 列設置下拉菜單，選中 E2 單元格 --> 選擇菜單欄的“數(shù)據(jù)”-->“數(shù)據(jù)驗證”

9. 在彈出的對話框中選擇“設置”選項卡，按以下方式設置 --> 點擊“確定”：

• 允許：選擇“序列”

• 來源：選擇剛才去重后合并的序列
  

下拉菜單已做好。

接下來設置公式。

10. 將 D 列作為輔助列，在 D2 單元格中輸入以下公式 --> 下拉復制公式：

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

公式釋義：

• 只要 A2 或 B2 單元格任有一個與 E2 的查詢值相同，輔助列的值就在上一個單元格的基礎上 +1；

• 也就是說，符合查詢項的每一行，都對應著一個新的遞增值第一次出現(xiàn)的位置；一對多查詢的時候，總是查找出重復項的第一個值，這樣就能查找出符合查詢條件的所有值；
  

11. 將 G 至 I 列設置為查詢結果區(qū)域，在 G2 單元格中輸入以下公式 --> 向右向下拖動公式：

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

公式釋義：

• MATCH(ROW(A1),$D$2:$D$29,0)：在 D 列的數(shù)值區(qū)域精確查找 A1 的行值，即 1，并返回查找結果在整個序列中的排列位置；當公式下拉，row 函數(shù)隨著行值遞增會返回逐漸遞增的序列；

• INDEX(A$2:A$29,...)：在區(qū)域 A$2:A$29 中查找出 match 函數(shù)結果所對應的同一行的值；

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

* 請注意參數(shù)的絕對和相對引用。

最后設置自動高亮顯示結果。

12. 選中 A2:C29 區(qū)域 --> 選擇菜單欄的“開始”-->“條件格式”-->“新建規(guī)則”

13. 在彈出的對話框中選擇“使用公式確定要設置格式的單元格”--> 輸入以下公式 --> 點擊“格式”按鈕：

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

公式釋義：

• A 或 B 列中只要有單元格等于查詢值，就觸發(fā)；

* 這個公式一定要注意參數(shù)絕對和相對引用，$E$2 單元格必須絕對引用，而 A、B 列的單元格要列值固定，行值不固定。

14. 在彈出的對話框中選擇“填充”選項卡 --> 選擇所需的填充色 --> 點擊“確定”

15. 點擊“確定”

設置好了。

GIF