【Excel函數篇】用VLOOKUP函數提取符合條件的多個結果~

本文包含兩部分內容。

第一部分，藉助輔助列，VLOOKUP函數提取符合條件的多個結果。

第二部分，不藉助輔助列，VLOOKUP函數依然可以提取符合條件的多個結果。

1，提出問題

VLOOKUP函數是Excel中最常用最簡單的條件查詢函數，號稱函數世界中的大眾情人（不明白大眾情人意思的親們返回看本文的封面）。它有兩個眾所周知的特點，查找值必須要在查找範圍的首列；以及它只提取查詢範圍中符合條件的首個查詢結果。

但在實際工作中，我們常常面臨這樣一個問題：查詢符合條件的結果並非一個，而是多個或一個。

此時如果依然使用VLOOKUP函數，怎麼處理哩？

舉個栗子，如下圖所示,根據A1:C10單元格區域的數據，計算A13人員的考核分結果，結果可能是多個，也可能是一個，例如看見星光。

2，輔助列解法

首先，我們在A列前面插入一列，作為輔助列。A2單元格輸入以下公式，並向下複製填充。

=B2&COUNTIF(B$2:B2,B2)

公式計算後的結果如下：

COUNTIF函數用於計算指定單元格範圍內某個值的重複次數。

COUNTIF(B$2:B2,B2)，計算B2在B$2:B2區域中的重複次數。由於查詢範圍的開始行是絕對引用（B$2鎖死），結束行是相對引用（B2開放），因此當公式複製向下填充時，COUNTIF的查詢範圍不斷擴展，例如B$2:B3、B$2:B4……以此對重複值形成重複次數累加計數的結果。

=B2&COUNTIF(B$2:B2,B2)，在COUNTIF函數計算結果的前面加上人員姓名，意思就是每個姓名重複的次數，使之成為獨一無二的標識。

輔助列構建完成後，在C13單元格使用以下VLOOKUP函數，即可得到結果。

=VLOOKUP($B13&COLUMN(A1),$A$1:$D$10,4,0)

VLOOKUP的查找值是$B13&COLUMN(A1)，公式橫向填充後，意思就是在$A$1:$D$10的單元格範圍內，查找看見星光重複1次的結果，看見星光重複2次的結果……

如果需要屏蔽公式錯誤值，可以嵌套IFERROR函數，如下：

=IFERROR(VLOOKUP($B13&COLUMN(A1),$A$1:$D$10,4,0),"""")

3，一個公式

如果不用輔助列，直接使用一個VLOOKUP函數計算出符合條件的多個結果可不可以呢？

當然也可以的，只是計算效率並不高，通常不建議使用。

我們前面講過，VLOOKUP只提取查找範圍的首個匹配結果……

但如果查找範圍不同，它自然就可以依次提取出多個結果。

這句話什麼意思呢？

例如查找「看見星光」，查找範圍是A1:D10時，首個結果是A2，但當查找範圍內縮成A3:D10時，首個結果就成了A5……

把這樣的想法形成公式表達，如下（數組公式）：

=VLOOKUP($A13,INDIRECT(""a""&SMALL(IF($A$1:$A$10=$A13,ROW($1:$10)),COLUMN(A1))&"":c10""),3,0)

SMALL(IF($A$1:$A$10=$A13,ROW($1:$10)),COLUMN(A1))部分，如果A1:A10的值等於A13，則返回對應的行號，否則返回邏輯值FALSE，然後使用SMALL函數從中依次由小到大取行數。

搭配INDIRECT函數，就構成了隨公式向右填充，不斷變化的VLOOKUP查詢區間，例如INDIRECT(A1:A10)，INDIRECT(A3:A10)……

最後使用VLOOKUP查詢取數，即可得出符合條件的多個查詢結果。

需要說明的是，該公式未屏蔽錯誤值，如需屏蔽錯誤值，請召喚IFERROR函數~

就醬紫了。

嗯，那誰，星光大叔還是重申一下，該公式為數組公式，輸入時需同時按下Ctlr Shift 回車……另外該公式運算效率偏差，只作於開拓思路，加深對VLOOKUP函數運算機制的認識，不建議大範圍使用……

啊，冷，大叔睡覺覺去鳥~

The End