VLOOKUP函數(shù)天階用法：一條函數(shù)公式就能一次性完成對100張及以上的表格數(shù)據(jù)的引用，內(nèi)容從第二大段開始。
堅持不易，有喜歡的朋友還請多多關(guān)注、幫我轉(zhuǎn)發(fā)、收藏、評論、點贊，你們的認可就是我堅持的動力，先謝謝了！
▍一、利用INDIRECT函數(shù)十字相交查找、引用excel二維表數(shù)據(jù)，比VLOOKUP與MATCH函數(shù)的組合使用更簡單，更方便。
如圖1，這是同一個工作簿下的兩張表格，右邊表2引用左邊表1的數(shù)據(jù)。此方法適用范圍比較廣：行列表頭可以互換，順序可以打亂。
圖1
先對表1的A1:G6數(shù)據(jù)區(qū)域定義名稱，選擇首行、最左列定義名稱，如動圖2：
動圖2：定義名稱
定義名稱有規(guī)則，內(nèi)容不能以數(shù)字開頭，如果一定要用數(shù)字開頭定義名稱，會在數(shù)字前面加上下劃線（_1Kg，英文輸入狀態(tài)，按住Shift+ - 符號），如圖3：
圖3：數(shù)字定義名稱
表1的定義名稱完成，就可以在表2輸入INDIRECT函數(shù)進行引用了。在表2的B2單元格輸入公式=INDIRECT($A2) INDIRECT(B$1)，兩個函數(shù)之間空格隔開。
$A2表示A列絕對引用，不偏移；B$1表示第1行絕對引用，不會偏移。A列重量是數(shù)字開頭，所以數(shù)字前面都要統(tǒng)一加“下劃線”，如（_1Kg），中文內(nèi)容沒有問題。
=INDIRECT($A2) INDIRECT(B$1)公式的意思是 同時引用A2單元格里6Kg地址的內(nèi)容與B1單元格里C區(qū)地址的內(nèi)容，十字相交所得結(jié)果。因為6Kg和C區(qū)等都已經(jīng)定義了名稱，所以就變成了可以引用的地址內(nèi)容。動圖展示：圖4
動圖4：INDIRECT十字相交引用
▍二、利用INDIRECT和VLOOKUP和COUNTIF函數(shù)組合跨工作表一次性可引用100張表格
如圖5：在“匯總表”中出現(xiàn)的人名是前面四張表格里隨機抽取出來的，怎么用VLOOKUP一次性引用完成。（如果有100張工作表，1000個人名，而且還不知道他們是在哪個部門，一個一個找太麻煩了。）
圖5
?思路解析：先求出部門，再VLOOKUP配合INDIRECT函數(shù)一次性引用。如圖6
圖6
?步驟一：先通過VBA代碼自動提取所有工作表名稱，解決手輸?shù)臒馈?/strong>
Sub a() For Each sh In Sheets k = k + 1 Cells(k, 1) = sh.Name Next End Sub這是提取工作表名稱代碼，不用理解含義，復(fù)制保存好，用的時候直接粘貼，很方便，不用擔心VBA很麻煩。
我新建一個工作表叫“提取各工作表名稱”，用來放提取出的工作表名稱。然后復(fù)制好VBA代碼——右鍵點擊工作表“提取各工作表名稱”——點“查看代碼”——出現(xiàn)了VBA編輯對話框——雙擊窗口左邊的“提取各工作表名稱”表——把VBA代碼粘貼，點上方的“綠三角”運行，關(guān)掉VBA編輯窗口，OK。如圖7和圖8：
圖7
圖8
動圖9展示：
動圖9：VBA代碼粘貼
?步驟二：給提取出來的工作表名稱新建定義名稱，方便INDIRECT函數(shù)引用。
把A1:A4的四個工作表名稱選中，定義名稱為“部門”，如圖10
動圖10：定義名稱
?步驟三：用公式找出每個人所對應(yīng)的部門表格
在D2單元格輸入公式=LOOKUP(1,0/COUNTIF(INDIRECT(部門&'!A:D'),$A2),部門)。
函數(shù)解析：①INDIRECT(部門&'!A:D')表示引用定義名稱“部門”表格A:D列地址的內(nèi)容，定義的名稱“部門”包含了財務(wù)部、銷售部、 生產(chǎn)部 、研發(fā)部四張表格。
②COUNTIF(查找區(qū)域，查找值），COUNTIF(INDIRECT(部門&'!A:D'),$A2)就是A2小張在所有部門表格的A:D列查找，這部分函數(shù)結(jié)果就是{1;0;0;0}，分別對應(yīng){'財務(wù)部';'銷售部';'生產(chǎn)部';'研發(fā)部'}。
③0/COUNTIF(INDIRECT(部門&'!A:D'),$A2)表示0/{1;0;0;0}。因為0除以1為0，但是0除以0是錯誤的，數(shù)學(xué)計算是不成立的，所以最終結(jié)果為{0;#DIV/0!;#DIV/0!;#DIV/0!}。
④LOOKUP是模糊查找函數(shù)，有一種向量形式，參數(shù)是LOOKUP（查找值，查找區(qū)域，結(jié)果區(qū)域）。=LOOKUP(1,0/COUNTIF(INDIRECT(部門&'!A:D'),$A2),部門)就是=LOOKUP(1,{0;#DIV/0!;#DIV/0!;#DIV/0!},{'財務(wù)部';'銷售部';'生產(chǎn)部';'研發(fā)部'})。兩個數(shù)組的位置是一 一對應(yīng)的，因為0對應(yīng)'財務(wù)部'，其他都是錯誤值就不對應(yīng)，所以得出A2小張是財務(wù)部。
如圖11：思路解析圖；如圖12：動圖展示
圖11：函數(shù)解析圖
圖12：動圖展示
注意：如果有一個人名在其他表格也有同名，那這個同名的可能會出現(xiàn)錯誤結(jié)果。這不是公式的問題，是給的信息太少的問題，因為就給了人名一個信息，沒有給部門信息，就算手動一個一個找也會錯，因為你不知道對方是要找哪個部門的人。
?步驟四：用VLOOKUP和INDIRECT函數(shù)組合，一次性同時引用100張表格數(shù)據(jù)（請注意:前方高能）
在B2單元格輸入=VLOOKUP($A2,INDIRECT($D2&'!A:Z'),MATCH(B$1,INDIRECT($D2&'!1:1'),0),0)。
函數(shù)解析：① MATCH（查找值，查找區(qū)域是單行或單列，查找類型），MATCH(B$1,INDIRECT($D2&'!1:1'),0)表示在“引用D2單元格里財務(wù)部表格第1行地址的內(nèi)容”中精確查找B1工資，0是精確查找，反饋結(jié)果是數(shù)字2。（這是動態(tài)引用，不管前面任何一張表格怎么更換表頭的順序，插入列或減去列，都會自動匹配結(jié)果）。
②INDIRECT($D2&'!A:Z')表示“引用D2單元格里財務(wù)部表格A:Z列的內(nèi)容”，寫A:Z列是為了把所有表格的數(shù)據(jù)包含進去，怕遺漏數(shù)據(jù)。
③=VLOOKUP($A2,INDIRECT($D2&'!A:Z'),MATCH(B$1,INDIRECT($D2&'!1:1'),0),0)就是最終顯示的結(jié)果，其他單元格就向左或向右的填充就可以了。
如圖14：函數(shù)公式解析圖
圖14：公式解析
如圖15：
圖15：一條函數(shù)同時引用100張表格
▍其實找人名對應(yīng)部門表格的公式和最后引用的VLOOKUP公式是可以合并成一條的，但是如果合并成一條真的是太長太長，光括號都能把人看暈，所以還是分步寫成兩段公式比較清楚和容易理解。
聯(lián)系客服
