VLOOKUP函數天階用法：一條函數公式就能一次性完成對100張及以上的表格數據的引用，內容從第二大段開始。

堅持不易，有喜歡的朋友還請多多關注、幫我轉發、收藏、評論、點贊，你們的認可就是我堅持的動力，先謝謝了！

▍一、利用INDIRECT函數十字相交查找、引用excel二維表數據，比VLOOKUP與MATCH函數的組合使用更簡單，更方便。

如圖1，這是同一個工作簿下的兩張表格，右邊表2引用左邊表1的數據。此方法適用范圍比較廣：行列表頭可以互換，順序可以打亂。

圖1

先對表1的A1:G6數據區域定義名稱，選擇首行、最左列定義名稱，如動圖2：

動圖2：定義名稱

定義名稱有規則，內容不能以數字開頭，如果一定要用數字開頭定義名稱，會在數字前面加上下劃線（_1Kg，英文輸入狀態，按住Shift+ - 符號），如圖3：

圖3：數字定義名稱

表1的定義名稱完成，就可以在表2輸入INDIRECT函數進行引用了。在表2的B2單元格輸入公式=INDIRECT($A2) INDIRECT(B$1)，兩個函數之間空格隔開。

$A2表示A列絕對引用，不偏移；B$1表示第1行絕對引用，不會偏移。A列重量是數字開頭，所以數字前面都要統一加“下劃線”，如（_1Kg），中文內容沒有問題。

=INDIRECT($A2) INDIRECT(B$1)公式的意思是 同時引用A2單元格里6Kg地址的內容與B1單元格里C區地址的內容，十字相交所得結果。因為6Kg和C區等都已經定義了名稱，所以就變成了可以引用的地址內容。動圖展示：圖4

動圖4：INDIRECT十字相交引用

▍二、利用INDIRECT和VLOOKUP和COUNTIF函數組合跨工作表一次性可引用100張表格

如圖5：在“匯總表”中出現的人名是前面四張表格里隨機抽取出來的，怎么用VLOOKUP一次性引用完成。（如果有100張工作表，1000個人名，而且還不知道他們是在哪個部門，一個一個找太麻煩了。）

圖5

?思路解析：先求出部門，再VLOOKUP配合INDIRECT函數一次性引用。如圖6

圖6

?步驟一：先通過VBA代碼自動提取所有工作表名稱，解決手輸的煩惱。

Sub a() For Each sh In Sheets k = k + 1 Cells(k, 1) = sh.Name Next End Sub 這是提取工作表名稱代碼，不用理解含義，復制保存好，用的時候直接粘貼，很方便，不用擔心VBA很麻煩。

我新建一個工作表叫“提取各工作表名稱”，用來放提取出的工作表名稱。然后復制好VBA代碼——右鍵點擊工作表“提取各工作表名稱”——點“查看代碼”——出現了VBA編輯對話框——雙擊窗口左邊的“提取各工作表名稱”表——把VBA代碼粘貼，點上方的“綠三角”運行，關掉VBA編輯窗口，OK。如圖7和圖8：

圖7

圖8

動圖9展示：

動圖9：VBA代碼粘貼

?步驟二：給提取出來的工作表名稱新建定義名稱，方便INDIRECT函數引用。

把A1:A4的四個工作表名稱選中，定義名稱為“部門”，如圖10

動圖10：定義名稱

?步驟三：用公式找出每個人所對應的部門表格

在D2單元格輸入公式=LOOKUP(1,0/COUNTIF(INDIRECT(部門&"!A:D"),$A2),部門)。

函數解析：① INDIRECT(部門&"!A:D")表示引用定義名稱“部門”表格A:D列地址的內容，定義的名稱“部門”包含了財務部、銷售部、 生產部 、研發部四張表格。

② COUNTIF(查找區域，查找值），COUNTIF(INDIRECT(部門&"!A:D"),$A2)就是A2小張在所有部門表格的A:D列查找，這部分函數結果就是{1;0;0;0}，分別對應{"財務部";"銷售部";"生產部";"研發部"}。

③ 0/COUNTIF(INDIRECT(部門&"!A:D"),$A2)表示0/{1;0;0;0}。因為0除以1為0，但是0除以0是錯誤的，數學計算是不成立的，所以最終結果為{0;#DIV/0!;#DIV/0!;#DIV/0!}。

④ LOOKUP是模糊查找函數，有一種向量形式，參數是LOOKUP（查找值，查找區域，結果區域）。=LOOKUP(1,0/COUNTIF(INDIRECT(部門&"!A:D"),$A2),部門)就是=LOOKUP(1,{0;#DIV/0!;#DIV/0!;#DIV/0!},{"財務部";"銷售部";"生產部";"研發部"})。兩個數組的位置是一 一對應的，因為0對應"財務部"，其他都是錯誤值就不對應，所以得出A2小張是財務部。

如圖11：思路解析圖；如圖12：動圖展示

圖11：函數解析圖

圖12：動圖展示

注意：如果有一個人名在其他表格也有同名，那這個同名的可能會出現錯誤結果。這不是公式的問題，是給的信息太少的問題，因為就給了人名一個信息，沒有給部門信息，就算手動一個一個找也會錯，因為你不知道對方是要找哪個部門的人。

?步驟四：用VLOOKUP和INDIRECT函數組合，一次性同時引用100張表格數據（請注意:前方高能）

在B2單元格輸入=VLOOKUP($A2,INDIRECT($D2&"!A:Z"),MATCH(B$1,INDIRECT($D2&"!1:1"),0),0)。

函數解析：① MATCH（查找值，查找區域是單行或單列，查找類型），MATCH(B$1,INDIRECT($D2&"!1:1"),0)表示在“引用D2單元格里財務部表格第1行地址的內容”中精確查找B1工資，0是精確查找，反饋結果是數字2。（這是動態引用，不管前面任何一張表格怎么更換表頭的順序，插入列或減去列，都會自動匹配結果）。

② INDIRECT($D2&"!A:Z")表示“引用D2單元格里財務部表格A:Z列的內容”，寫A:Z列是為了把所有表格的數據包含進去，怕遺漏數據。

③=VLOOKUP($A2,INDIRECT($D2&"!A:Z"),MATCH(B$1,INDIRECT($D2&"!1:1"),0),0)就是最終顯示的結果，其他單元格就向左或向右的填充就可以了。

如圖14：函數公式解析圖

圖14：公式解析

如圖15：

圖15：一條函數同時引用100張表格

▍其實找人名對應部門表格的公式和最后引用的VLOOKUP公式是可以合并成一條的，但是如果合并成一條真的是太長太長，光括號都能把人看暈，所以還是分步寫成兩段公式比較清楚和容易理解。