之前我認為是做不到這個功能,只能將所有工作表合併成一個,再利用vlookup去查詢,昨天有學員又問了我這個問題,我問他可以將所有的工作表合成一個嗎?他說不行,所以就上網查看看可不可用陣列公式來解決問題,真的有人用陣列公式達到,因為他的用法我不熟,又怕照著輸入會有少單引號雙引號及左右括號的問題,所以決定用替換的方式來取代,先拆開指令一個一個試沒問題,再把它取代。

vlookup_multi_sheet_01
1.先做一個跨工作表的查詢,黃色就是我們要用陣列公式取代的部分

 

vlookup_multi_sheet_02
2.利用countif去查詢要查的資料是否在工作表中

 

vlookup_multi_sheet_03
3.會用到match回傳在第幾個工作表中countif有找到值

 

vlookup_multi_sheet_04
4.先將要查詢的工作表取一個名稱SheetList

 

vlookup_multi_sheet_05
5.在用indirect將查詢範圍變成文字

 

vlookup_multi_sheet_06
6.再將文字利用&跟要查詢的資料表結合,最後按下[shift]+[ctrl]+[enter]變成陣列公式

 

vlookup_multi_sheet_07
7.將countif的數字利用>0,變成True

 

vlookup_multi_sheet_08
8.再把沒問題的指令取代match函數

 

vlookup_multi_sheet_09
9.再透過index回傳找到工作表的名稱

 

vlookup_multi_sheet_10
10.利用indirect將查詢的範圍變成文字

 

vlookup_multi_sheet_11
11.再將步驟9中的公式取代文字,但是有發垷找不到會出現#N/A

 

vlookup_multi_sheet_12
12.再利用iferror來將#N/A變成空白

 

範例檔案下載:http://download.bestdaylong.com/f136.htm