一對(duì)多查詢本來(lái)已經(jīng)挺難的,，如果還要將任意列作為查詢項(xiàng),，一對(duì)多查詢出結(jié)果，這個(gè)是不是更難,？

案例：

下圖 1 是一個(gè)防疫物品團(tuán)購(gòu)表,，請(qǐng)制作一個(gè)下拉菜單，可以按樓號(hào)或者物資查詢,，不但能列出所有查詢結(jié)果,，還能在原來(lái)的表上高亮顯示。

效果如下圖 2 所示,。

GIF

解決方案：

先制作下拉菜單,。

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

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

3. 在彈出的對(duì)話框中選擇“以當(dāng)前選定區(qū)域排序”--> 點(diǎn)擊“刪除重復(fù)項(xiàng)”

4. 點(diǎn)擊“確定”

5. 點(diǎn)擊“確定”

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

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

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

9. 在彈出的對(duì)話框中選擇“設(shè)置”選項(xiàng)卡,，按以下方式設(shè)置 --> 點(diǎn)擊“確定”：

• 允許：選擇“序列”

• 來(lái)源：選擇剛才去重后合并的序列
  

下拉菜單已做好。

接下來(lái)設(shè)置公式,。

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

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

公式釋義：

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

• 也就是說(shuō),，符合查詢項(xiàng)的每一行，都對(duì)應(yīng)著一個(gè)新的遞增值第一次出現(xiàn)的位置,；一對(duì)多查詢的時(shí)候,，總是查找出重復(fù)項(xiàng)的第一個(gè)值，這樣就能查找出符合查詢條件的所有值,；
  

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

=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,，并返回查找結(jié)果在整個(gè)序列中的排列位置,；當(dāng)公式下拉，row 函數(shù)隨著行值遞增會(huì)返回逐漸遞增的序列,；

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

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

* 請(qǐng)注意參數(shù)的絕對(duì)和相對(duì)引用。

最后設(shè)置自動(dòng)高亮顯示結(jié)果,。

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

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

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

公式釋義：

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

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

14. 在彈出的對(duì)話框中選擇“填充”選項(xiàng)卡 --> 選擇所需的填充色 --> 點(diǎn)擊“確定”

15. 點(diǎn)擊“確定”

設(shè)置好了。

GIF