Excel中的一對(duì)多查詢，很多人都是不知道公式要怎么寫(xiě)的。就拿我們最常用的VLOOKUP函數(shù)、LOOKUP函數(shù)來(lái)說(shuō)，僅僅依靠一個(gè)函數(shù)是無(wú)法做到的，有些人也會(huì)加上一些輔助列來(lái)達(dá)到一對(duì)多查詢的目的，但都是比較麻煩的。

下面教你一組萬(wàn)能公式“INDEX+SMALL+IF+ROW+IFERROR”，學(xué)會(huì)這個(gè)函數(shù)組合，可以解決Excel中一對(duì)多查詢90%以上的問(wèn)題。

下面有一張圖，想要在A1:F9單元格區(qū)域中，找出符合B11單元格“銷售一部”的所有內(nèi)容，并顯示到以A13開(kāi)始的單元格區(qū)域中，圖片中符合條件的只有第2、第5和第6行，已經(jīng)用其他顏色標(biāo)出來(lái)。如果用這組函數(shù)來(lái)實(shí)現(xiàn)，你有什么思路嗎？

具體操作步驟如下：

1、選中A13單元格 -- 在編輯欄中輸入公式“=IFERROR(INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))),'')”-- 按組合鍵“Ctrl+Shift+Enter”結(jié)束該公式。

2、可以看到A13單元格公式返回的結(jié)果是“銷售1部”-- 將A13單元格往右拖拉至F13單元格 -- 往下拖拉至F15單元格 -- 即可得到部門為“銷售一部”的所有數(shù)據(jù)。

3、動(dòng)圖演示如下。

4、公式解析。

（1）ROW($A$2:$A$9)：

生成一個(gè)行號(hào)的數(shù)組{2;3;4;5;6;7;8;9}，即A1:F9單元格區(qū)域中的第2行至第9行的行號(hào)。

（2）IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))：

判斷$A$2:$A$9單元格區(qū)域中的內(nèi)容是否跟$B$11單元格的內(nèi)容相等，如果相等，返回

$A$2:$A$9對(duì)應(yīng)的行號(hào)，否則，返回FALSE。此時(shí)該公式得到的結(jié)果是一組數(shù)組{2;FALSE;FALSE;5;6;FALSE;FALSE;FALSE}。數(shù)組中的2、5和6代表“銷售1部”所在的行號(hào)。

（3）SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1))：

SMALL函數(shù)的作用是：在一列數(shù)值中，按從小到大的順序取第n個(gè)值。第1個(gè)參數(shù)IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))為數(shù)據(jù)區(qū)域。第2個(gè)參數(shù)ROW(A1)為返回的數(shù)據(jù)在數(shù)組或數(shù)組區(qū)域里的位置（從小到大）。IF($A$2:$A$9=$B$11,ROW($A$2:$A$9))返回的結(jié)果為{2;FALSE;FALSE;5;6;FALSE;FALSE;FALSE}。由于SMALL函數(shù)在計(jì)算最小值時(shí)忽略邏輯值TRUE和FALSE以及文本型數(shù)字。所以該公式最后得到的結(jié)果為{2;5;6;#NUM!;#NUM!;#NUM!;#NUM!;#NUM!}。

（4）INDEX(A$1:A$9,SMALL(IF($A$2:$A$9=$B$11,ROW($A$2:$A$9)),ROW(A1)))：

INDEX函數(shù)的作用是：用來(lái)引用我們所需的信息。其有3個(gè)參數(shù)。第1個(gè)參數(shù)表示我們要引用的區(qū)域，第2個(gè)參數(shù)是表示要引用的行數(shù)，第3個(gè)參數(shù)表示要引用的列數(shù)。最終的結(jié)果就是引用區(qū)域內(nèi)行和列的交叉內(nèi)容。這里返回的結(jié)果是第2行、第5行和第6行單元格的內(nèi)容，即“銷售1部”。

（5）IFFEROR函數(shù)。

我們上兩步返回的值都有錯(cuò)誤值，這樣看起來(lái)感覺(jué)不是很好，為了將錯(cuò)誤值變成空，可以使用IFFEROR函數(shù)，IFFEROR函數(shù)有2個(gè)參數(shù)，如果我們將第2個(gè)參數(shù)設(shè)置為空，公式執(zhí)行時(shí)會(huì)判斷是否有錯(cuò)誤值輸出，如果有，直接讓錯(cuò)誤值不顯示。下圖是沒(méi)有使用IFFEROR函數(shù)得到的結(jié)果。

以上就是小編今天要跟大家將的一對(duì)多查詢的通用組合公式。相信大家看了這篇教程之后還是一臉懵逼的，看一遍沒(méi)看懂就多看幾遍，再跟著練習(xí)幾次就可以學(xué)會(huì)了。