本文是專門為新書打造的「拓展閱讀系列」之一，我們希望做到實體書與線上學習的有效結合，通過二維碼打通實體書與線上資源，構建完善的Excel知識體系。

我以前寫過兩篇關於VLOOKUP函數的文章，流傳甚廣，在這兩篇文章中，我把VLOOKUP吹上了天。

如果你對此函數用的不太熟，先來學習這兩篇文章：

入門|快速掌握VLOOKUP函數之精解精析

進階|熟練使用VLOOKUP函數之精解精析【深度長文】

在眾多查找函數中，VLOOKUP確實稱得上扛把子的，但是它也有局限性，這不，一對多查找他似乎就無能為力。

檢察院以人民的名義派了三個小組去抓貪官，需要從左面的表格中查找出二組的成員，但是二組對應兩個成員，也就是說一次要查找並返回兩個值。

我們知道Vlookup總會以第一個被找到數據作為最終的匹配數據，所以，當有兩個」二組「同時存在時，很自然的，它只能默認為匹配首個，也就是說只能返回」高育良「。

那麼，是不是傳說中的Vlookup遇到這種情況也只能望洋興嘆，無能為力啊？

當然不是！

作為專業從事匹配工作的函數，Vlookup只是需要一些額外的輔助。

需要注意的是，原生態的Vlookup，用於匹配的數據必須是唯一的，這是由函數的四個參數決定的，改變不了這個事實，所以我們只能從查找匹配的數據源上進行改造。

—01—

構造輔助列

雖然一個組別對應多為成員，但是這種對應也是有規律的。比如，第一個「一組」對應「侯亮平」，第二個「一組」對應「陳海」……

所以，為了實現一對一匹配，需要構建出組別的次序。在C2單元格中輸入公式=COUNTIF(A$2:A2,A2)，並向下複製填充，可以得出每一個組別的次序。

然後在B列左側插入一個新列，並將A列數據和C列數據組合（使用&進行單元格組合），形成新的數據列，如圖。

奇蹟出現了，使用新組別這一列數據去查找成員，相當於為每一個組別創造了唯一的識別碼，再用Vlookup時，就能精確地一對一匹配到了。

你看，原以為搞不定一對多排序是VLOOKUP函數的問題，實際是數據源的問題。至此，可得出解決此問題的關鍵點為：

①通過COUNTIF函數，製造出序列（難點是A$2:A2動態引用，這是創造正確編號的核心）；

②通過&組合組別和次序，製造唯一性。

—02—

實現VLOOKUP一對多查找

萬事俱備只欠東風，最後一步——Vlookup多條件匹配。

在G2單元格中寫入公式=VLOOKUP(F$2&ROW(A1),B:C,2,0)，然後向下複製填充，直到出現#N/A錯誤，則會返回「二組」對應的所有成員。

這裡使用ROW(A1)函數生成序列，然後再與F2單元格組合，於是就依次生成「二組1」，「二組2」，相當於VLOOKUP函數的第一個參數依次按照「新組別」中的參數出現。

—03—

還能怎麼玩

上面的查找方式，將查找的多個結果依次放入不同的行單元格中，這就導致無法批量查找，即無法同時查找「二組」和「三組」的成員，必須分開寫公式。

所以，通常情況下，我們會將查找到的多個結果放入不同列中，效果如下圖所示。

稍微對公式進行修改，就能實現這樣的效果。

在G2單元格中寫入公式=VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)，並向下拖動填充，然後向右拖動填充，直到每一個組別對應的成員都出現錯誤值#N/A為止，這說明每一個組別對應的成員都被查找出來了。

我們將此公式

=VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)

與上文中的公式對比

=VLOOKUP(F$2&ROW(A1),B:C,2,0)

首先第一個參數變成了($F2&COLUMN(A$1)，這是因為我們需要同時查找「二組」、「三組」、「一組「對應的成員，因此在向下複製時，引用的單元格需要依次變更為F3、F4，所以這裡的$F2需要對行需要對行進行相對引用，又因為公式需要向右複製，必須確保引用的一直是F列的數據，所以列為絕對引用。

同理，我們在列方向進行填充，因此需要是使用COLUMN函數構造序號。

— 04 —

屏蔽錯誤值

因為我們事先不知道一個組別對應幾名成員，所以必須一直向右填充公式，直到出現錯誤值為止，才算把所有的數值查找完畢。

為了避免出現錯誤值，可以使用IFERROR函數進行嵌套。

將G2單元格中的公式改為

=IFERROR（VLOOKUP($F2&COLUMN(A$1),$B:$C,2,0)，"")

第一參數就是查找公式，第二參數代表「如果第一參數的運算結果為錯誤值時所顯的自定義的值」。這裡，可以設定為""，也就是顯示為「空」。

這樣，向右複製到出現空單元格為止即可。

卧槽，一個VLOOKUP函數都被玩出花了！

·END·

IOS專用打賞，一種碉堡了的打賞方式

↓↓↓

推薦閱讀：

相关文章