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

一、跨多工作表查找

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

在查詢表中，要求根據(jù)提供的姓名，從銷售~綜合5個(gè)工作表中查詢?cè)搯T工的基本工資。

分析：

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

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

如果，我們知道A1可能在銷售或財(cái)務(wù)表這2個(gè)表中，公式可以寫為：

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),VLOOKUP(A2,財(cái)務(wù)!A:G,7,0))

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

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

=IFERROR(VLOOKUP(A2,銷售!A:G,7,0),IFERROR(VLOOKUP(A2,財(cái)務(wù)!A:G,7,0),VLOOKUP(A2,!A:G,7,0)))

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

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

方法1：

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

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

如果你想簡(jiǎn)化一下公式，以適合在更多的表中查謁，再提供一個(gè)思路，只是公式簡(jiǎn)單了，理解起來(lái)卻難了。這里你只需要學(xué)會(huì)怎么修改公式套用就可以了。

方法2：

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

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

A2：查找的內(nèi)容

{""}：大括號(hào)內(nèi)是要查找的多個(gè)工作表名稱，用逗號(hào)分隔

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

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

7：是vlookup第3個(gè)參數(shù)，相對(duì)應(yīng)的列數(shù)。你懂的。

公式思路說(shuō)明：

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

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

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

4、利用vlookup查找。

二、跨多文件查找

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

文件夾中有N個(gè)倉(cāng)庫(kù)產(chǎn)品表格，需要在“查詢”文件完成查詢

倉(cāng)庫(kù)表樣式

在查詢表中設(shè)置公式，根據(jù)產(chǎn)品名稱從指定的文件中sheet1工作表查詢?nèi)霂?kù)單價(jià)

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

補(bǔ)充：

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

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