為什麼我的 XLOOKUP 明明看得到那筆資料，卻回 #N/A？這篇整理我自己用 XLOOKUP 時踩過的坑，基本語法在 XLOOKUP 入門 講過了，這邊直接從會出事的地方開始。
先看這張價目表，後面大部分的例子都用它，放在 A1:D6：
| 品號 | 品名 | 單價 | 庫存 |
|---|---|---|---|
| A-101 | 蘋果紅茶 | 45 | 37 |
| A-102 | 蘋果綠茶 | 50 | 112 |
| B-201 | 青茶 | 35 | 8 |
| B-202 | 鮮奶茶 | 60 | 24 |
| C-301 | 冬瓜檸檬 | 55 | 19 |
先看完整語法
XLOOKUP 一共六個參數，前三個必填，後面三個選填：
- 找不到時的值：沒找到時顯示什麼，沒填就是
#N/A。 - 比對模式
match_mode：0完全相同，這是預設、-1完全相同，找不到就取下一個較小的、1完全相同，找不到就取下一個較大的、2萬用字元。 - 搜尋模式
search_mode：1由上往下找，這是預設、-1由下往上找、2二分搜尋，資料要遞增排序、-2二分搜尋，資料要遞減排序。
以上是照 官方說明 整理的。接下來每個陷阱，基本上都是這幾個參數沒設對。
Excel 的 XLOOKUP 只有 Microsoft 365、Excel 2021、Excel 2024 才有，Excel 2016、2019 沒有；參數順序和 match_mode、search_mode 的數值與上面相同。下面幾處錯誤碼在 Excel 裡會不一樣，我會在該段標出來。
找不到的時候
我想查品號 A-999 的品名，它不在價目表裡：
來看看結果：#N/A。
這是預設行為，沒什麼好怕的。如果表格是要給別人看的，就用第 4 個參數：
這次會顯示「查無此品號」。
有人習慣整條公式外面再包一層 IFERROR，我個人比較少這樣做。因為 IFERROR 連其他錯誤也一起吞掉，公式哪裡寫壞了你也看不到。第 4 個參數只管「找不到」這一件事，範圍寫錯、型別不對時錯誤還是會跑出來，這樣比較好抓問題。
近似比對：-1 跟 1 差在哪
查找值不一定剛好在表裡，像是「依杯數決定訂單等級」。門檻表放在 A1:B5，故意不照順序排：
| 杯數門檻 | 等級 |
|---|---|
| 13 | 中批 |
| 0 | 散客 |
| 30 | 大批 |
| 6 | 小批 |
這張表我先用手算：17 杯超過 13、還沒到 30，所以應該是「中批」。用 -1 來寫，就是：
來看看結果：「中批」。-1 是完全相同就用它，沒有的話取「下一個較小的」，也就是 13。把 -1 換成 1 就是取「下一個較大的」，17 會落到 30，得到「大批」。
幾個邊界情況：
| 查找值 | 比對模式 | 結果 |
|---|---|---|
| 17 | -1 | 中批 |
| 17 | 1 | 大批 |
| 30 | -1 | 大批，剛好相同 |
| 31 | 1 | 查無，沒有更大的了 |
| -2 | -1 | 查無，沒有更小的了 |
要注意，這張門檻表沒有排序，結果還是對的。很多人包括以前的我，都以為近似比對一定要先排序，那是 VLOOKUP 的習慣。XLOOKUP 預設一個一個往下比，排不排序都可以，下面的二分搜尋才有這個限制。
萬用字元：match_mode 設成 2
品名只記得一半的時候，比對模式填 2 就能用萬用字元：* 代表任意多個字、? 代表一個字。
來看看結果：A-101。品名裡有「茶」的有好幾個，它只會回傳第一個，也就是蘋果紅茶。
這個會得到 A-102。如果忘了填 2，"*茶*" 就只是一串普通文字，在 B 欄找不到，結果是「查無」。
那如果資料裡本來就有 *？A1 是「微糖」，A2 是「微糖*」（備註裡真的有一個星號），B1:B2 是 1 和 2。直接找 "微糖*"，* 會被當萬用字元，先碰到「微糖」，回傳 1。要在 * 前面加 ~，告訴它這是真的星號：
這個會得到 2。
最新一筆：search_mode 設成 -1
這是我最常用的一招。出貨紀錄一直往下加，同一個品號出現很多次，我想要最新的單價。紀錄放在 A1:C6：
| 日期 | 品號 | 單價 |
|---|---|---|
| 2026/09/03 | A-101 | 45 |
| 2026/09/17 | B-201 | 35 |
| 2026/10/01 | A-101 | 48 |
| 2026/10/05 | B-201 | 38 |
| 2026/10/08 | A-102 | 50 |
預設由上往下找，只會抓到第一次出現的 A-101：
得到 45，是九月的舊價錢。把搜尋模式改成 -1，從最後一列往上找：
這次是 48。前面的比對模式要補 0，也就是完全相同，不能跳過去。紀錄要是照日期由舊到新排的，由下往上找的第一筆就是最新一筆。
二分搜尋才需要排序
搜尋模式填 2 遞增或 -2 遞減，會改用二分搜尋，這時資料一定要照對應的順序排好，不然結果不可靠。把上面的門檻表改成由小到大 0、6、13、30，才能這樣寫：
結果一樣是「中批」。好處是資料很大時比較快，不過一般幾百幾千列的表，我自己都直接用預設。為了這點速度，把排序弄壞才是更麻煩的事。
回傳多欄，與被擋住的 #REF!
結果範圍選多欄，XLOOKUP 就會把找到的那一整列帶回來，自動往右展開。
放在 F2，會展開成 F2:H2：
| F | G | H | |
|---|---|---|---|
| 2 | 青茶 | 35 | 8 |
一條公式就拿到品名、單價、庫存，不用寫三次。
但如果旁邊的 G2 已經有東西，比如我隨手打了一個「擋住」，F2 就會變成 #REF!。是的，Sheets 沒有 #SPILL! 這個錯誤碼，展開被擋住時顯示的是 #REF!（Excel 才是 #SPILL!），滑鼠移上去會看到訊息「系統未展開陣列結果，以免覆蓋 G2 中的資料。」。把擋住的儲存格清空就好了。想多了解展開的行為，可以看 陣列思維。
兩個方向一起查
橫的、直的都要查呢？價目表有「中杯」「大杯」兩種價格，放在 A1:C4：
| 中杯 | 大杯 | |
|---|---|---|
| 蘋果紅茶 | 45 | 55 |
| 鮮奶茶 | 60 | 70 |
| 青茶 | 35 | 45 |
我想找鮮奶茶的大杯價格，可以先用 XLOOKUP 找出「大杯」那一欄，再把整欄交給外層找列：
得到 70。內層的查找範圍是單一橫列，結果範圍有三列，所以回傳整欄；外層再從這欄裡按照品名挑列。
範圍大小不一致
查找範圍和結果範圍長度要一樣。價目表的 A2:A6 有 5 列，我如果手滑把結果範圍寫成 B2:B5：
結果是 #N/A，滑鼠移上去的提示是「XLOOKUP 的陣列引數大小不同。」，不是 #VALUE!（同樣的錯誤在 Excel 會顯示 #VALUE!）。常發生在新增資料後，只有一邊的範圍被拉長。用整欄 A:A 與 B:B 就不會有這個問題，但我自己的習慣是範圍寫清楚比較看得懂。
文字和數字：「001」不等於 1
查不到、又看不出哪裡錯，我第一個先懷疑型別。編號 001、002、003 如果是文字，放在 A2:A4，B 欄是品名。用數字 1 去找：
結果是「查無」。文字的「001」和數字 1，在 XLOOKUP 眼裡是兩個東西。反過來也一樣，如果另一份資料的 D 欄是數字 1、2、3，E 欄是品名，而查找值是用 LEFT 從「1-茶」切出來的 "1"，切出來的一定是文字，同樣找不到。
修法是把兩邊弄成同一種型別。資料是文字「001」，查找值就直接寫成 "001"；資料是數字、查找值是文字，就用 VALUE 把文字轉成數字：
另外，用儲存格格式把數字 1 顯示成 001，它的值還是 1，查找時用的是 1。
多餘的空格
從別的系統貼過來的資料，常常尾巴多一個看不見的空格，A-101 變成 A-101 ，也找不到。比對一下長度就知道： 會給 6，而 A-101 應該是 5。
確認是空格之後，用 TRIM 清掉，但查找範圍是多格範圍，要搭配 ARRAYFORMULA：
這樣就找得到了。不過如果這份資料每天都會貼，我會乾脆在匯入的那一欄先用 TRIM 清好，不要每條查找公式都包一層。更完整的清理方式在 文字清理函式。
我還是會用 VLOOKUP 或 INDEX / MATCH 的時候
坦白說，新做的表我幾乎都用 XLOOKUP，因為不用數第幾欄，而且可以往左找。官方說明的範例也是這樣對照：VLOOKUP 要給欄位編號，鑰匙一定要在最左邊。
但我不會特地去改舊檔案裡的 VLOOKUP，改了反而容易出新問題。需要「這筆資料在第幾列」這種位置數字的時候，我也還是會寫 MATCH 配 INDEX。除了這兩種情況，我想不到什麼特別的理由要回頭用它們。
動手練習
找出最新單價
用上面「最新一筆」那張出貨紀錄，範圍是 A1:C6，在 E2 放 B-201，F2 用一條公式查出它最新的單價。查不到時顯示「查無」。
品項加杯型這種兩個條件要同時符合才查，寫法在 XLOOKUP 兩個條件一起找。
要一次撈出多筆符合的資料，XLOOKUP 就不夠用了，那是 FILTER 的工作，下次再聊囉。我是喜特先生，Mr. Sheet，我們下個教學見！
這個步驟如果每週都要重做
- 現在
- 貼進新的出貨紀錄後，手動檢查哪些品號查無、再逐筆補
- 自動化後
- 新紀錄進來就自動比對價目表，查無的品號標出來並通知你
接下來可以
這篇在技能樹的 Lv.2「進階分析」，模組「查找對應的資訊」（第 2／2 篇）。讀完這篇，路線上的下一個模組是「篩選與整理資料」。
回到技能樹相關文章
查看所有文章陣列思維：從一格一格算到一次算一整欄
用全班加 5 分的小例子，看一格一個公式和一個公式整欄展開差在哪，再認識縱向、橫向、二維陣列的形狀，以及展開結果被舊資料擋住時的
文字清理函式參考：TRIM、CLEAN、SUBSTITUTE、PROPER 與看不見的空格
整理 TRIM、CLEAN、SUBSTITUTE、REPLACE、UPPER、LOWER、PROPER 的語法與限制，包含 TRIM 清不掉全形空格與 CHAR(160)、CLEAN 只清 ASCII 0 到 31、SUBSTITUTE 第 4 參數只換第 N 次，以及用 UNICODE 找出看不見的字元。
留言討論
共 0 則留言