表格效率所Excel XLOOKUP 完整教學：左右查找、找不到與版本相容性
快速答案
Excel XLOOKUP：先理解用途與限制
XLOOKUP 可以指定查找陣列與回傳陣列，預設採精確比對，能向左或向右查找；Excel 2019 等舊版本可能不支援。
本文採用的實作角度是「用商品代碼查單價的可複製範例，對照精確查找與相容性。」，適合正在處理「想以 XLOOKUP 依代碼查回資料，並處理找不到與舊版 Excel 相容問題。」的讀者。請先在副本完成測試，再套用到正式檔案。
適用版本：適用 Microsoft 365、Excel 2024、Excel 2021 與支援 XLOOKUP 的版本；舊版可改用 INDEX＋MATCH。
可複製的公式或程式範例
先在測試工作表建立與本文相同的欄位，再依實際檔案調整範圍。公式中的逗號、分號與日期格式可能受地區設定影響。
=XLOOKUP(A2,$F$2:$F$6,$G$2:$G$6,"查無資料",0)範例情境
A2 為商品代碼，F2:F6 是代碼表，G2:G6 是單價。公式先以精確比對找出代碼，再回傳對應單價；找不到時顯示「查無資料」。
開始前要確認的資料結構
- 01查找欄與回傳欄筆數必須相同。先把這一點寫在欄位說明或設定工作表，能減少之後的公式歧義。
- 02文字與數字型別需一致。先把這一點寫在欄位說明或設定工作表，能減少之後的公式歧義。
- 03固定參照避免向下填滿時範圍位移。先把這一點寫在欄位說明或設定工作表，能減少之後的公式歧義。
- 04找不到訊息要能區分真正空值。先把這一點寫在欄位說明或設定工作表，能減少之後的公式歧義。
實作步驟
-
步驟 1：建立唯一商品代碼欄
先在副本或小範圍測試，記錄目前列數與控制總數，避免直接改壞正式檔。 -
步驟 2：選取查找值 A2
完成後查看資料型別、範圍尺寸與輸出位置，再進入下一步。 -
步驟 3：設定代碼範圍與單價範圍
完成後查看資料型別、範圍尺寸與輸出位置，再進入下一步。 -
步驟 4：加入查無資料提示與精確比對
完成後查看資料型別、範圍尺寸與輸出位置，再進入下一步。 -
步驟 5：用已知存在與不存在的代碼測試
用已知正確、空白、錯誤與邊界資料重測，確認結果可被另一位使用者理解。
正式套用前，建議建立最小測試資料：一筆正常值、一筆空白、一筆錯誤或邊界值。若結果會影響報價、薪資、庫存或寄信，還要安排人工核對。
常見錯誤與排查順序
| 常見問題 | 先檢查什麼 | 安全修正 |
|---|---|---|
| 查找範圍與回傳範圍列數不同 | 資料型別、前後空白與範圍起訖 | 先在副本重現，再只改一個變因並核對控制總數 |
| 代碼含前後空白 | 版本、權限、輸出區與公式參照 | 先在副本重現，再只改一個變因並核對控制總數 |
| 數字代碼一邊被儲存為文字 | 資料型別、前後空白與範圍起訖 | 先在副本重現，再只改一個變因並核對控制總數 |
| 舊版開啟後出現 _xlfn.XLOOKUP | 版本、權限、輸出區與公式參照 | 先在副本重現，再只改一個變因並核對控制總數 |
如何確認結果正確
- 用人工可算的小資料核對公式結果。
- 測試空白、零、負數、日期邊界與查無資料。
- 重新開啟檔案或由另一帳戶測試權限與更新。
- 確認手機或窄螢幕查看時，表格仍可橫向捲動而不撐破整頁。
若這份試算表由多人使用，請在交付區標示欄位責任、更新頻率、資料來源與還原方式。公式能自動運算，但不會自動保證輸入資料正確。
常見問題
XLOOKUP 一定要排序嗎？
精確比對不需要排序；使用近似比對或二分搜尋時才要特別確認資料順序。
可以一次回傳多欄嗎？
新版 Excel 可讓回傳陣列涵蓋多欄，結果會溢位到相鄰儲存格。
為什麼看得到代碼卻找不到？
常見原因是型別不同、前後空白或不可見字元。
官方文件與版本來源
本文的函數、權限與平台行為依以下官方文件核對；雲端功能與配額可能更新，正式部署前請再查看最新內容。