我們先來看下兩種借助輔助列進行“一對多查詢”的方法，一種是用VLOOKUP函數(shù)，一種是用INDEX MATCH函數(shù)組合。

用VLOOKUP函數(shù)實現(xiàn)“一對多查詢”

如下圖所示，首先在A列建立輔助列，在A2單元格輸入如下公式，然后向下填充至A10單元格。

這個公式是用來統(tǒng)計H2單元格的內(nèi)容第幾次出現(xiàn)在B列，返回1、2、3...n序列值。

然后在I2單元格輸入如下公式，然后向右、再向下填充至L10單元格。

=IFERROR(VLOOKUP(ROW(1:1),$A:$F,COLUMN(C:C),0),'')

用INDEX MATCH函數(shù)組合實現(xiàn)“一對多查詢”

用INDEX MATCH進行“一對多查詢”的原理和VLOOKUP相同，都是將對班級信息的查詢，轉(zhuǎn)換成對數(shù)字序列1、2、3....n的查詢。因此只需要將圖2中的公式改為如下形式，即可完成查詢。

以上兩種方法的詳細講解及注意事項，可以參考之前的一篇文章：Excel中兩種常見的“一對多”查詢方式橫向?qū)Ρ?，孰?yōu)孰劣？

用INDEX SMALL IF函數(shù)組合實現(xiàn)“一對多查詢”

這種方法的最大好處是不需要輔助列，直接輸入公式即可得到結(jié)果，比前兩種方法更加便捷！如圖4所示，我們在H2單元格輸入如下公式，然后按住Ctrl Shift Enter，然后向右、向下填充至K10單元格。

=IFERROR(INDEX(B:B,SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))),'')

最終的查找結(jié)果如下圖所示：

我們來一步步分析這個公式的運行原理。

這部分公式用來將A1:A10范圍內(nèi)的值與G2單元格的值進行逐個比對，如果相同，則返回A1:A10對應(yīng)的行號（ROW函數(shù)的作用）。我們可以選中這部分公式，然后按下F9鍵查看最終結(jié)果。

{FALSE;2;3;FALSE;5;6;FALSE;FALSE;FALSE;FALSE}

發(fā)現(xiàn)返回的數(shù)組中只有2、3、5、6四個值，其它均為FALSE，也就是說在A1:A10范圍的第2、3、5、6行找到了需要查找的數(shù)據(jù)！

SMALL(IF($A$1:$A$10=$G$2,ROW($1:$10)),ROW(A1))

SMALL函數(shù)對上一步得到的數(shù)據(jù)結(jié)果進行取數(shù)，隨著公式向下填充，依次提取第1、2、3...n個最小值，這些數(shù)字對應(yīng)的是符合條件班級的行號。比如我們選中H2單元格中的這部分公式，按F9顯示查找結(jié)果{2}，即得到了第一個匹配結(jié)果的行號為2。

這一步，是用INDEX定位對應(yīng)B列數(shù)據(jù)的位置（第2行），結(jié)果為B2。

最后的IFERROR函數(shù)是為了屏蔽多余行中的錯誤。因為，如果沒有IFERROR函數(shù)，查詢結(jié)果是這個樣子的：

總結(jié)

本文通過討論了三種“一對多查詢”的方法，帶領(lǐng)大家進一步熟悉了幾個函數(shù)組合在查詢中的典型用法。我們不難看出三種方法各有優(yōu)劣，在實際的工作實踐中到底選擇哪一種方法，要根據(jù)它們的特點，以及任務(wù)量大小等綜合考量。

三種方法的優(yōu)缺點對比：

1. VLOOKUP函數(shù)

優(yōu)點：公式相對簡單，容易理解。缺點：需要借助輔助列，并要求輔助列必須是查詢范圍的第1列。

2. INDEX MATCH函數(shù)組合

優(yōu)點：靈活多變，不用考慮輔助列順序。缺點：需要借助輔助列，不太方便。

3. INDEX SMALL IF函數(shù)組合

優(yōu)點：非常方便，不需要輔助列，一個公式就可以解決問題。缺點：公式相對復(fù)雜，不易理解。