vlookup函數(shù)最高級的應用：多表多文件查找

一、跨多工作表查找

【例】工資表模板中，每個部門一個表。

在查詢表中，要求根據提供的姓名，從銷售~綜合5個工作表中查詢該員工的基本工資。

分析：

如果，我們知道A1是銷售部的，那么公式可以寫為：

=VLOOKUP(A2,銷售!A:G,7,0)

如果，我們知道A1可能在銷售或財務表這2個表中，公式可以寫為：

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),VLOOKUP(A2,財務!A:G,7,0))

意思是，如果在銷售表中查找不到(用iferror函數(shù)判斷)，則去財務表中再查找。

如果，我們知道A1可能在銷售、財務或服務表中，公式可以再次改為：

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))

意思是從銷售表開始查詢，前面的查詢不到就到后面的表中查找。

如果，有更多的表，如本例中5個表，那就一層層的套用下去。這也是我們今天提供的VLOOKUP多表查找

方法1：

=IFERROR(VLOOKUP(A2,服務!A:G,7,0),IFERROR(VLOOKUP(A2,人事!A:G,7,0),IFERROR(VLOOKUP(A2,綜合!A:G,7,0),IFERROR(VLOOKUP(A2,財務!A:G,7,0),IFERROR(VLOOKUP(A2,銷售!A:G,7,0),"無此人信息")))))

------------------------------------------

如果你想簡化一下公式，以適合在更多的表中查謁，再提供一個思路，只是公式簡單了，理解起來卻難了。這里你只需要學會怎么修改公式套用就可以了。

方法2：

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT({"銷售";"服務";"人事";"綜合";"財務"}&"!a:a"),A2),{"銷售";"服務";"人事";"綜合";"財務"})&"!a:g"),7,0)

你只需要修改以下部分，就可以直接套用

A2：查找的內容

{""}：大括號內是要查找的多個工作表名稱，用逗號分隔

a:a :本例是姓名在各個表中的A列，如果在B列則為b:b

a:g ：vlookup查找的區(qū)域

7：是vlookup第3個參數(shù)，相對應的列數(shù)。你懂的。

公式思路說明：

1、確定員工是在哪個表中。這里利用countif函數(shù)可以多表統(tǒng)計來分雖計算各個表中該員工存在的個數(shù)。

2、利用lookup(1,0/(數(shù)組),數(shù)組) 結構取得工作表的名稱

3、利用indirec函數(shù)把字符串轉換成單元格引用。

4、利用vlookup查找。

二、跨多文件查找

跨多個文件查找，估計你搜遍網絡也找不到，這也是首次編寫跨多文件查找公式。其實原理和跨多表查找一樣，也是借助lookup等函數(shù)實現(xiàn)。

文件夾中有N個倉庫產品表格，需要在“查詢”文件完成查詢

倉庫表樣式

在查詢表中設置公式，根據產品名稱從指定的文件中sheet1工作表查詢入庫單價

=VLOOKUP(A2,INDIRECT(LOOKUP(1,0/COUNTIF(INDIRECT("["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1!a:a"),A2),"["&{"倉庫1";"倉庫2";"倉庫3"}&".xlsx]sheet1")&"!a:b"),2,0)。

補充：

vlookup函數(shù)的多文件查找，同樣可以用iferror+vlookup的模式，公式雖然長，但容易理解且公式不容易出錯。如果你有一定基礎，倒可以試試第2種方法。

另外，如果工作表或excel文件有幾十個或更多，就需要使用宏表函數(shù)Get.workbook來獲取所有工作表的名稱和用Files獲取所有excel文件名稱，然后應用到公式中。