Excel 核心函數 · XLOOKUP 與 VLOOKUP
Excel Lookup Functions (XLOOKUP, VLOOKUP, INDEX/MATCH)
什麼是 Excel 核心函數?
- 查詢函數是用一個基準值把兩張表連起來的工具
- XLOOKUP 很方便,但視版本而定可能無法使用
- 多數錯誤並非來自函數本身,而是比對基準值的狀態
學會查詢函數,並不是為了背語法。人資與教育訓練承辦人的 Excel 工作,大多是用一個基準值把兩張表連起來。例如用人事名冊的員工編號接上教育修課紀錄;把學員名單補上部門與職級;從年薪合約對象表帶入考績等級。這些事若用眼睛逐筆複製貼上,在 300 人規模下一定會有幾行對不上。查詢函數是為了精準度,而非速度的工具。
VLOOKUP 的格式為 =VLOOKUP(查找值, 表格範圍, 欄位編號, 0),會在表格最左邊的欄位中查找,再取出右邊第幾欄的值。若未將最後一個引數設為 0(FALSE),就會以近似比對運作,抓取相近的值;若在表格中間插入一欄,欄位編號會跟著位移,取出錯誤的值。INDEX/MATCH 的格式為 =INDEX(取值欄, MATCH(查找值, 查找欄, 0)),查找欄與取值欄分開指定,因此可以向左查詢,也不受插入欄位影響。
XLOOKUP 的寫法是 =XLOOKUP(要查找的值, 查找範圍, 要傳回的範圍, 找不到時顯示的值)。預設為完全相符,也沒有查找方向的限制,找不到時的提示文字可以直接在函數內指定。不過它只能在 Microsoft 365 及較新版本中使用,因此要先確認收件人的 Excel 版本。若在舊版開啟,公式會變成錯誤。若要與多家公司交換檔案,INDEX/MATCH 是最安全的共通做法。
在現場最常出錯的地方,其實不是函數本身,而是基準值的狀態。若員工編號一邊存成數字、另一邊存成文字,或姓名後面多了空白,無論用哪種函數都會回傳「找不到」。若以姓名當基準值,遇到同名同姓的人,只會取到第一位的值,而且不會出現任何錯誤提示。基準值應選用員工編號這類不重複的值,並且習慣性地把結果的錯誤筆數與總筆數和原始資料比對,這比選哪個函數更重要。
| 比較 | VLOOKUP | INDEX/MATCH | XLOOKUP |
|---|---|---|---|
| 基本格式 | =VLOOKUP(查找值, 表格範圍, 欄位編號, 0) | =INDEX(要取得的欄, MATCH(要查找的值, 查找欄, 0)) | =XLOOKUP(要查找的值, 查找範圍, 要傳回的範圍) |
| 查找方向 | 僅限查找欄的右側 | 無限制 | 無限制 |
| 比對方式預設值 | 近似比對(需填入 0 才會精確比對) | MATCH 以 0 指定完全相符 | 完全相符 |
| 插入欄位時 | 欄位編號可能位移而出錯 | 影響小 | 影響小 |
| 找不到時 | 錯誤顯示(需以 IFERROR 包覆) | 錯誤顯示(需以 IFERROR 包覆) | 以第四個引數指定顯示文字 |
| 可使用的版本 | 幾乎所有版本 | 幾乎所有版本 | Microsoft 365 等近期版本 |
VLOOKUP 是試算表早期就使用的查詢函數,為了克服其限制,INDEX 與 MATCH 的組合成為業界常見的做法。XLOOKUP 則是微軟為了減少這兩者的不便而後來新增的函數,因此視 Excel 版本可能無法使用。
我們以一個假設的案例來看。員工 680 人的食品製造商 A 公司,教育訓練團隊在年底的教育實績報告前,必須把人事名冊與 LMS 修課紀錄 2,300 筆接在一起。去年一位專員花了兩天複製貼上,報告送出後才發現,各部門的修課率有 3 處算錯。
- 教育團隊的代理以修課紀錄中的姓名查詢部門,結果 11 位同名同姓者的部門全都被填成第一位的部門。主管說:"別用姓名了,改用員工編號重新對應吧。"
- 改用員工編號查詢後,這次有 140 筆出現「查無資料」。從 LMS 下載的員工編號是以文字格式儲存,轉換成數字後,錯誤筆數減少為 6 筆。
- 剩下的 6 筆是年中離職的員工。專員在 XLOOKUP 的第四個引數填入「離職者確認」,讓空白處改為顯示原因。
- 我把檔案寄給子公司人資部門後,對方回覆「公式全部顯示錯誤」。原來對方用的是舊版 Excel,因此我改用 INDEX/MATCH 重新撰寫公式後再寄過去。
- 主管要求在工作表最上方設置核對欄位,比對查詢結果筆數與原始筆數;確認兩邊 2,300 筆完全一致後,才將報表送出。
- 彙整時間從兩天縮短為 3 小時,報告後的更正要求也從 3 件降為 0 件。同一份檔案之後也沿用於下一季的業績彙整。
Ja-ryong Gu
金鍾赫