Excel函數公式一對多查詢的12種公式

完成效果：

在熟悉常用函數語法之後，如果要解決實際工作問題，往往需要幾個函數通過嵌套組合才能解決問題，這就是所謂的思路。收集的思路越多，越容易想到解決辦法。下面針對一對多查詢這個問題，介紹12種公式中運用的一些常用思路。

以下公式均需要按【CTRL SHIFT ENTER】三鍵結束。

公式1

E2=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))),"")

思路分析：

通過條件函數IF判斷，只要符合要求的記錄，全部返回所在行號，否則返回False。SMALL函數的作用是從小到大逐個返回行號，最後通過INDEX函數返回符合要求的記錄。IFERROR函數的作用是當所有記錄顯示完出錯時，公式返回空。以下所有公式的這個信息函數作用是一樣的，就不再贅述了。

公式2

F2=IFERROR(VLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),2,),"")

思路分析：

利用IF函數重新構造兩列數組，一列是符合條件的行號，一列是符合條件的記錄。再利用VLOOKUP函數從小到大行號查找返回符合要求的記錄。

公式3

G2=IFERROR(VLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),2,),"")

思路分析：

思路與公式2思路大致一樣，唯一不同的是公式3是通過CHOOSE函數重新構造兩列數組。

公式4

H2=IFERROR(HLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),TRANSPOSE(IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),2,),"")

思路分析：

思路和公式2類似，不過這裡是通過HLOOKUP函數查找，查找區域需要TRANSPOSE函數轉置一下才能返回正確結果。

公式5

I2=IFERROR(HLOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),TRANSPOSE(CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),2,),"")

思路分析：

思路和公式4大致一樣，唯一不同的是公式5是通過CHOOSE函數重新構造兩列數組。

公式6

J2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11),"")

思路分析：

這裡利用了LOOKUP函數以下語法：

LOOKUP(lookup_value, lookup_vector, [result_vector])

在LOOKUP函數把符合條件的行號從小到大返回結果。

公式7

K2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),IF({1,0},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),"")

思路分析：、

這裡利用了LOOKUP函數以下語法：

LOOKUP(lookup_value, array)

思路是一樣的，不過語法結構不同，注意與公式6的區別。

公式8

L2=IFERROR(LOOKUP(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),CHOOSE({1,2},IF(A$1:A$11=D$2,ROW(A$1:A$11)),B$1:B$11)),"")

思路分析：

思路與公式7一樣，不同的是公式8通過CHOOSE函數重新構造兩列數組。

公式9

M2 =IFERROR(OFFSET(B$1,SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))-1,),"")

思路分析：

利用OFFSET函數，以B1單元格為起點，向下偏移相應符合條件行數的記錄。

公式10

N2 =IFERROR(INDIRECT("B"&SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))),"")

思路分析：

利用INDIRECT函數A1樣式單元格引用。

公式11

O2 =IFERROR(INDIRECT("r"&SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1))&"c2",),"")

思路分析：

利用INDIRECT函數R1C1樣式單元格引用。

公式12

P2 =IFERROR(INDIRECT(ADDRESS(SMALL(IF(A$1:A$11=D$2,ROW(A$1:A$11)),ROW(A1)),2)),"")

思路分析：

與公式10類似，這裡是通過ADDRESS函數返回單元格引用地址。