訂單補價格、兩張表對照：用 XLOOKUP 處理價目表與查不到的品項

門市傳來的訂單只寫了品名和杯數，我要補上商品代碼、單價、庫存，算出每一筆的金額，再看看價目表裡有哪些品項這個月根本沒人點。

這篇是拿 XLOOKUP 做事。語法在 XLOOKUP 入門 講過了，近似比對、萬用字元這些邊角在 XLOOKUP 進階陷阱，同一批品項改用 INDEX 和 MATCH 的做法則在 INDEX 與 MATCH 實戰。

先看資料

價目表放在 A1:D8，品名在最左邊：

訂單放在旁邊的 F1:G8，一共 7 筆，青茶出現兩次，「奶蓋紅茶」價目表裡沒有，是我故意放的：

H 到 K 是我要補的四欄，標題分別是代碼、單價、庫存、金額。價目表如果放在別張工作表，把範圍前面加上工作表名稱就好，例如 價目表!$A$2:$A$8，其他都一樣。

一條公式帶出三欄

代碼、單價、庫存在價目表是連在一起的 B 到 D 欄。結果範圍選這三欄，XLOOKUP 找到品名之後會把那一列的三格一起帶回來，自動往右展開，不用寫三次。

H2 輸入：

• $F2：訂單的品名，欄固定、列不固定，往下複製時會跟著換列。
• $A$2:$A$8：價目表的品名欄，查找範圍，兩個方向都固定。
• $B$2:$D$8：結果範圍，一次三欄。
• "查無此品"：找不到時顯示的字，下一節再講。

先手算第一筆：四季春在價目表的第 6 個，那一列是 TE-641、40、29。往下複製到 H8，來看看結果：

這個做法有個前提：H2 展開時要用到 I2 和 J2，這兩格得是空的。如果已經有東西擋住，H2 會變成 #REF!，這個情況在進階篇有講。金額欄我放在 K，剛好避開。

另外，帶出來的欄位順序就是價目表 B 到 D 的順序。價目表之後如果有人把欄位調換，H1 到 J1 的標題我會一起檢查，因為公式不會幫你改標題。

算出金額

K 欄是杯數乘單價。單價在 I 欄，奶蓋紅茶那一列沒有單價，先確認是數字才乘：

先手算：12 × 40 = 480，9 × 35 = 315，21 × 58 = 1218，33 × 45 = 1485，6 × 35 = 210，15 × 52 = 780，奶蓋紅茶算 0。往下複製後，K2 到 K8 是 480、315、1218、0、1485、210、780。

總金額：

480 + 315 + 1218 + 0 + 1485 + 210 + 780 = 4488，公式得到 4488。

查不到的時候

奶蓋紅茶那一筆，我選擇讓它顯示「查無此品」，沒有填 0 或留白。

理由很實際：單價填 0 的話，金額會悄悄變成 0，總金額看起來一切正常，沒人發現少算了 5 杯。留白也一樣，從畫面上看不出是沒查到，還是價目表本來就沒填。文字很醜，不過一眼就看得到。

要知道有幾筆查不到、少算了多少杯，直接對 H 欄統計：

得到 1。杯數要用 SUMIF，條件範圍是 H 欄、加總範圍是 G 欄：

得到 5。這兩個數字放在表格上方，看到不是 0 就知道要回頭補價目表。

要提醒的是，找不到時的值只管「找不到」。範圍寫歪、型別不對造成的錯誤還是會跑出來，這也是我比較不喜歡用 IFERROR 把整條包起來的原因。COUNTIF 和 SUMIF 的語法在 SUMIF 與 COUNTIF。

兩張表對照：誰有誰沒有

接下來的問題是：價目表有、這個月訂單沒有的，是哪幾個？

先手算。價目表 7 個品項，逐個到訂單裡數它出現幾次。在價目表旁邊的 E 欄，用 COUNTIF 數：

往下複製到 E8，來看看結果：

0 的就是沒人點：蘋果綠茶和熟成紅茶。這邊我沒有用 XLOOKUP 當作判斷，是因為它只會找到第一筆，青茶出現兩次它不會告訴你。只問「有沒有」，數次數比較直接。

不想多一欄的話，把 COUNTIF 放進 FILTER，一條公式列出來：

COUNTIF 的條件這次給整個範圍，所以一次數 7 個品名，得到 7 個次數，FILTER 留下次數是 0 的。你應該會得到：

外面包 IFNA 是因為 FILTER 沒有符合的資料時回 #N/A，如果每個品項都有人點，會顯示「全都有人點」。FILTER 的語法在 FILTER 函式。

反過來問：訂單有、價目表沒有的是誰？把兩個範圍對調。訂單可能有重複，所以外面加 UNIQUE：

得到 奶蓋紅茶。這就是前面那筆「查無此品」的清單版：訂單一多，我會先看這條，把缺的品項一次補進價目表。

兩條公式只差在誰是「被數的」、誰是「被檢查的」。想不起來的時候，我會問自己：我要列出的是哪一張表的品名，那張表的範圍就放在 FILTER 的第一個參數。

看懂舊檔裡的 VLOOKUP

接手別人的檔案，查找幾乎都是 VLOOKUP。讀的時候我固定照這個順序看：

1. 第一個參數：拿什麼去找。
2. 第二個參數：範圍。只會在範圍的第一欄找，所以品名一定在範圍最左邊。
3. 第三個參數：欄號。從範圍的第一欄起算，A、B、C 是範圍內的第幾欄，不是整張試算表的欄。
4. 第四個參數：FALSE 或 0 是完全一樣才算，沒寫或寫 TRUE 是近似比對。

套在這份價目表上，常見的幾種寫法和改寫是：

第一條是在 A 欄找品名，回傳範圍第 3 欄（單價），完全一樣才算。第二條最後的 0 就是 FALSE，意思一樣。第三條外面包的 IFERROR 讓任何錯誤都顯示查無此品，XLOOKUP 的第四個參數只管找不到，兩者不完全等價。

第四個參數沒寫的 VLOOKUP，要多留意。資料沒有排序的時候，近似比對的結果不可靠，可能回錯的值，也可能回 #N/A，要看資料的排列。看到沒寫的，我會先問做這個檔案的人是不是漏寫了 FALSE，不要直接改。要是原本就是在查等級、門檻，改成 XLOOKUP 時近似比對要自己指定，做法在進階篇的「近似比對：-1 跟 1 差在哪」那一節。

最容易出事的是欄號。價目表在品名和代碼之間被人插入一欄「備註」，範圍自動從 $A$2:$D$8 變成 $A$2:$E$8，欄號 3 卻沒有跟著動，原本第 3 欄是單價，現在變成代碼：

VLOOKUP 本來要查單價，卻拿到代碼；XLOOKUP 的結果範圍會跟著搬到 D 欄，還是單價。

後面的金額如果是拿這個結果去乘，會變成 #VALUE!，那還容易發現。只是被拿去顯示、沒有去乘的話，錯的欄位會一直放在那邊。所以我接手 VLOOKUP 的檔案，第一件事是查它有沒有被插過欄。

VLOOKUP 和 XLOOKUP 的語法對照在 XLOOKUP 入門 有整理。整份檔案不必全部改：能運作又沒人改欄位的，看得懂就夠了，要動的是新加的，或是已經出過錯的那幾條。

新增訂單與價目表要改哪裡

• 訂單多一列：把 H 到 K 這一列往下複製。H 欄的公式只要放在每一筆的第一格，右邊兩格是自動展開的，不用複製。
• 價目表多一個品項：$A$2:$A$8 和 $B$2:$D$8 要一起拉長，例如都改成到第 9 列。漏改一個，查找範圍和結果範圍長度對不上，會回 #N/A。
• 對照那兩條 FILTER 的範圍也要同步拉長，不然新品項不會被算進去。

練習

訂單和價目表這類的對照，做一次之後換成你自己的資料就會順很多，試試看吧！

我是喜特先生，Mr. Sheet，我們下個教學見！