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

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

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

這個(gè)公式是用來統(tǒng)計(jì)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ù)組合實(shí)現(xiàn)“一對(duì)多查詢”

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

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

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

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

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

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

我們來一步步分析這個(gè)公式的運(yùn)行原理。

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

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

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

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

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

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

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

總結(jié)

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

三種方法的優(yōu)缺點(diǎn)對(duì)比：

1. VLOOKUP函數(shù)

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

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

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

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

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