微軟 excel 教學
Excel自動填入對應資料：VLOOKUP、INDEX MATCH、XLOOKUP 三種方法完整教學【2026最新】
文章目錄
Excel 自動填入對應資料最常用的方法是 VLOOKUP 函數，只要指定查閱值、資料範圍與回傳欄位，就能一秒完成跨欄比對。除了 VLOOKUP，INDEX+MATCH 組合與 2026 年微軟主推的 XLOOKUP 也是熱門選擇。本文從零開始教你三種方法的完整公式與實戰範例，並附上錯誤處理技巧與比較表，讓你不再手動查資料。
VLOOKUP 函數：最經典的自動填入方法
VLOOKUP（Vertical Lookup，垂直查閱）是 Excel 中最廣泛使用的查找函數，能根據指定的關鍵值，從資料表的第一欄向右查找並回傳對應欄位的資料。根據微軟官方文件，VLOOKUP 自 Excel 2007 起即內建，至今仍是全球使用率最高的查找函數之一。
VLOOKUP 的運作邏輯類似查字典：你給它一個「要查的詞」（查閱值），它在指定的「字典」（資料範圍）中找到那個詞，再回傳同一列指定欄位的內容。
常見的應用場景包括：
- 根據員工編號自動填入姓名、部門與職稱
- 根據產品代碼自動帶出品名、單價與庫存量
- 根據訂單編號查找客戶名稱與訂單金額
- 根據學號自動填入學生姓名與成績
VLOOKUP 函數語法與四大參數完整解析
VLOOKUP 的公式語法為 =VLOOKUP(lookup_value, table_array, col_index_num, [range_lookup])，四個參數分別控制「查什麼」「在哪查」「回傳哪欄」「精確或模糊」。
| 參數 | 中文名稱 | 說明 | 範例 |
|---|---|---|---|
| lookup_value | 查閱值 | 你要查找的關鍵字，必須位於 table_array 的第一欄 | A2（員工編號儲存格） |
| table_array | 查閱範圍 | 包含資料的表格範圍，建議用絕對參照（$）鎖定 | $A$1:$D$100 |
| col_index_num | 欄位索引 | 要回傳的資料位於查閱範圍的第幾欄（從 1 起算） | 3（回傳第三欄） |
| [range_lookup] | 比對模式 | FALSE = 精確比對（建議預設）；TRUE = 近似比對 | FALSE |
以員工資料為例，假設 A 欄是員工編號、B 欄是姓名、C 欄是部門、D 欄是薪資，要根據編號查部門：
=VLOOKUP(F2, $A$1:$D$100, 3, FALSE)
這條公式的意思是：在 A1:D100 範圍中，找到 F2 儲存格的編號，回傳同列第 3 欄（部門）的值，使用精確比對。
實戰經驗：我在處理超過 5,000 筆員工資料時，最常犯的錯誤是忘記用 $ 鎖定 table_array。當你向下拖曳公式時，沒有絕對參照的範圍會跟著偏移，導致查找結果錯誤。建議養成習慣：table_array 一律加上 $，例如
$A$1:$D$100。
VLOOKUP 實戰範例：從零開始操作
以下用一個完整的員工查詢範例，示範 VLOOKUP 從建立資料表到輸入公式的完整流程。
- 準備資料表：在 A1:D6 建立員工資料，A 欄為編號（1001~1005）、B 欄為姓名、C 欄為部門、D 欄為月薪。
- 設定查詢區：在 F1 輸入「查詢編號」，在 F2 輸入要查的編號（例如 1003）。
- 輸入 VLOOKUP 公式：在 G2 輸入
=VLOOKUP(F2,$A$1:$D$6,2,FALSE)，按 Enter 即可自動帶出姓名。 - 查詢多欄資料：在 H2 輸入
=VLOOKUP(F2,$A$1:$D$6,3,FALSE)查部門；在 I2 輸入=VLOOKUP(F2,$A$1:$D$6,4,FALSE)查月薪。 - 向下拖曳：如需批次查詢多筆編號，選取 G2:I2 後向下拖曳填滿控點即可。
| 員工編號 (A) | 姓名 (B) | 部門 (C) | 月薪 (D) |
|---|---|---|---|
| 1001 | 王小明 | 業務部 | 45,000 |
| 1002 | 李美玲 | 行銷部 | 42,000 |
| 1003 | 張大華 | 研發部 | 55,000 |
| 1004 | 陳志強 | 人資部 | 40,000 |
| 1005 | 林雅婷 | 財務部 | 48,000 |
在 F2 輸入 1003，G2 的 VLOOKUP 公式會回傳「張大華」，H2 回傳「研發部」，I2 回傳「55,000」。
VLOOKUP 的限制與常見錯誤處理
VLOOKUP 最大的限制是「只能從左往右查」——查閱值必須在資料範圍的第一欄，否則公式無法運作。這是許多初學者踩到的第一個坑。
VLOOKUP 的三大限制：
- 只能向右查找：查閱值必須位於 table_array 最左欄，無法反向查找左邊的欄位
- 欄位索引用數字：當資料表欄位很多時，手動數欄號容易出錯
- 插入欄位會錯位：若在 table_array 中間插入新欄位，col_index_num 不會自動更新
最常見的錯誤是 #N/A，代表找不到對應資料。解決方法是用 IFERROR 函數（If Error，錯誤處理）包裹 VLOOKUP：
=IFERROR(VLOOKUP(F2,$A$1:$D$6,2,FALSE), "查無資料")
這樣當查不到資料時，儲存格會顯示「查無資料」而非難看的 #N/A 錯誤。
其他常見錯誤與對策：
| 錯誤訊息 | 原因 | 解決方法 |
|---|---|---|
| #N/A | 查閱值在資料範圍中不存在 | 確認查閱值拼寫正確，或用 IFERROR 包裹 |
| #REF! | col_index_num 超過 table_array 的欄數 | 檢查欄位索引數字是否正確 |
| #VALUE! | col_index_num 小於 1 | 欄位索引必須 >= 1 |
| 回傳錯誤值 | 資料格式不一致（文字 vs 數字） | 統一查閱值與資料欄的格式 |
INDEX + MATCH：突破 VLOOKUP 限制的進階組合
INDEX + MATCH 是 VLOOKUP 的進階替代方案，最大優勢是可以「向左查找」且不受欄位順序限制。根據微軟 Excel 官方教學，INDEX + MATCH 組合在彈性與效能上均優於 VLOOKUP。
公式結構：=INDEX(回傳範圍, MATCH(查閱值, 查找範圍, 0))
拆解來看：
- MATCH 函數（Match，匹配）：在指定範圍中找到查閱值的「位置」（第幾列），回傳一個數字
- INDEX 函數（Index，索引）：根據 MATCH 回傳的位置數字，從另一個範圍中取出對應的值
以同樣的員工資料為例，要用 INDEX+MATCH 根據姓名反查員工編號（向左查找）：
=INDEX($A$1:$A$6, MATCH(F2, $B$1:$B$6, 0))
這條公式先用 MATCH 在 B 欄找到姓名的列號，再用 INDEX 從 A 欄取出對應的員工編號。這是 VLOOKUP 做不到的「反向查找」。
| 功能 | VLOOKUP | INDEX+MATCH |
|---|---|---|
| 查找方向 | 只能從左到右 | 任意方向 |
| 欄位參照方式 | 用數字（col_index_num） | 直接指定回傳範圍 |
| 插入欄位後 | 可能錯位 | 不受影響 |
| 大量資料效能 | 較慢（整個範圍掃描） | 較快（只掃描查找欄） |
| 學習難度 | 簡單 | 中等 |
| 適用版本 | Excel 2007 以上 | Excel 2007 以上 |
XLOOKUP：2026 年微軟主推的新一代查找函數
XLOOKUP（Extended Lookup，延伸查閱）是微軟在 Microsoft 365 與 Excel 2021 之後推出的新函數，一個函數就能取代 VLOOKUP、HLOOKUP 與 INDEX+MATCH 的組合。
公式語法：=XLOOKUP(查閱值, 查找範圍, 回傳範圍, [找不到時的預設值], [比對模式], [搜尋模式])
XLOOKUP 的三大優勢：
- 預設精確比對：不需要像 VLOOKUP 那樣手動設定 FALSE，降低出錯機率
- 雙向查找：不受欄位順序限制，向左、向右都能查
- 內建錯誤處理：第四個參數可直接設定「找不到時顯示什麼」，不需額外包 IFERROR
以員工資料為例，用 XLOOKUP 根據編號查姓名：
=XLOOKUP(F2, $A$1:$A$6, $B$1:$B$6, "查無此人")
如果要反向查找（根據姓名查編號）：
=XLOOKUP(F2, $B$1:$B$6, $A$1:$A$6, "查無此人")
注意：XLOOKUP 僅適用於 Microsoft 365、Excel 2021 及更新版本。如果你使用 Excel 2019 或更早版本，仍需使用 VLOOKUP 或 INDEX+MATCH。
三種方法完整比較：該選哪一個？
選擇哪種查找函數取決於你的 Excel 版本與需求複雜度——初學者用 VLOOKUP 快速上手，進階使用者用 INDEX+MATCH 獲得最大彈性，Microsoft 365 用戶直接用 XLOOKUP 最省事。
| 比較項目 | VLOOKUP | INDEX+MATCH | XLOOKUP |
|---|---|---|---|
| 查找方向 | 僅限左→右 | 任意方向 | 任意方向 |
| 預設比對模式 | 近似比對（需手動改 FALSE） | 需手動設定 0 | 精確比對（預設） |
| 錯誤處理 | 需外包 IFERROR | 需外包 IFERROR | 內建第 4 參數 |
| 回傳多欄 | 需寫多條公式 | 需寫多條公式 | 可一次回傳多欄 |
| 適用版本 | Excel 2007+ | Excel 2007+ | Microsoft 365 / Excel 2021+ |
| 學習曲線 | 低 | 中 | 低 |
| 推薦場景 | 簡單的單向查找 | 需要反向查找或大量資料 | 新版 Excel 的所有查找需求 |
Excel 自動填入對應資料實戰操作指引
以下是從零開始設定 Excel 自動填入對應資料的完整步驟，適用於任何版本的 Excel。
- 整理原始資料表：確保資料表有清楚的欄位標題，查閱值（如編號）放在最左欄，資料中不要有合併儲存格。
- 確認 Excel 版本：按「檔案 > 帳戶」查看版本。Microsoft 365 或 Excel 2021 以上可用 XLOOKUP；Excel 2019 以下用 VLOOKUP 或 INDEX+MATCH。
- 選擇適合的函數：簡單的左→右查找用 VLOOKUP；需要反向查找用 INDEX+MATCH；新版 Excel 一律用 XLOOKUP。
- 輸入公式並鎖定範圍：在目標儲存格輸入公式，table_array 務必用 $ 絕對參照鎖定（例如
$A$1:$D$100），避免拖曳時範圍偏移。 - 加上錯誤處理：用 IFERROR 包裹公式（XLOOKUP 用戶可直接設定第 4 參數），避免 #N/A 錯誤影響報表美觀。
- 批次填入：選取已輸入公式的儲存格，拖曳右下角填滿控點向下複製，或使用 Ctrl+D 快速填滿整欄。
- 驗證結果：隨機抽查 3~5 筆資料，確認自動填入的結果與原始資料表一致。特別注意文字與數字格式是否統一。
Excel自動填入對應資料 常見問題 FAQ
VLOOKUP 查不到資料顯示 #N/A 怎麼辦？
最常見的原因是查閱值的格式與資料表不一致（例如一邊是文字、一邊是數字），或查閱值有多餘的空白。先用 TRIM 函數清除空白，再用 IFERROR 包裹公式：=IFERROR(VLOOKUP(TRIM(F2),$A$1:$D$100,2,FALSE),"查無資料")。
VLOOKUP 可以向左查找嗎？
VLOOKUP 無法向左查找，這是它最大的限制。如果需要向左查找，請改用 INDEX+MATCH 組合或 XLOOKUP（需 Microsoft 365 / Excel 2021 以上）。
INDEX+MATCH 和 VLOOKUP 哪個比較快？
在大量資料（超過 10,000 筆）的情境下，INDEX+MATCH 通常比 VLOOKUP 快 10%~30%。因為 VLOOKUP 需要掃描整個 table_array，而 MATCH 只掃描查找欄。但在小資料量時差異不明顯。
XLOOKUP 可以在 Excel 2019 使用嗎？
不行，XLOOKUP 僅支援 Microsoft 365（訂閱版）與 Excel 2021 以上的版本。Excel 2019 及更早版本的使用者，建議用 INDEX+MATCH 作為替代方案。
如何同時查找並填入多個欄位的資料？
VLOOKUP 和 INDEX+MATCH 每條公式只能回傳一個欄位，需要分別寫多條公式。XLOOKUP 則可以在回傳範圍中指定多欄（例如 =XLOOKUP(F2,$A:$A,$B:$D)），一次回傳多個欄位的資料。
VLOOKUP 的第四個參數 TRUE 和 FALSE 差在哪？
FALSE 代表精確比對，只回傳完全一致的結果；TRUE（或省略）代表近似比對，會回傳最接近但不超過查閱值的結果。日常工作中 90% 以上的情境都應該用 FALSE，避免取到錯誤資料。近似比對僅適用於已排序的數值資料（例如稅率級距表）。
為什麼 VLOOKUP 拖曳公式後結果全部錯誤？
最常見的原因是 table_array（查閱範圍）沒有用 $ 鎖定絕對參照。當你向下拖曳公式時，沒有 $ 的範圍會跟著偏移。解決方法：將 table_array 改為絕對參照，例如從 A1:D100 改為 $A$1:$D$100。