用 INDEX 與 MATCH 補代碼、查銷量：訂單表與雙向查表實戰

門市回報的訂單表只寫了品名和杯數，我要補上商品代碼和單價，再算出每一筆的金額。代碼在價目表裡是品名的左邊，用 VLOOKUP 查不到，這種時候就輪到 INDEX 和 MATCH 出場。

INDEX 和 MATCH 各自的語法，在 INDEX 與 MATCH 入門 講過了，這篇直接拿來做事。

先看資料

價目表跟入門篇一樣，放在 A1:D8：

訂單放在旁邊的 F1:G7，只有品名和杯數。H1 到 K1 我先寫好標題「位置」「代碼」「單價」「金額」，這四欄是要補的：

最後一筆「奶蓋紅茶」價目表裡沒有，我是故意放的，等一下看它會怎麼處理。

先用 MATCH 找位置

補代碼和補單價，找的都是同一件事：這個品名在價目表第幾列。所以我先用 H 欄把位置算出來，之後兩欄都共用它。

H2 輸入：

• $F2：訂單的品名，欄固定、列不固定，往下複製時會跟著換列。
• $B$2:$B$8：價目表的品名欄，兩個方向都固定，複製時不會跑掉。
• 0：完全相同才算。

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

四季春是價目表品名欄的第 6 個，跟上面的表對得起來。奶蓋紅茶找不到，所以是 #N/A。

往左查代碼，往右查單價

位置有了，I 欄補代碼。代碼在價目表 A 欄，品名在 B 欄，也就是代碼在品名的左邊。INDEX 取值的範圍跟找位置的範圍是各自指定的，往左往右都一樣寫。

I2 輸入：

• INDEX($A$2:$A$8, $H2)：從代碼欄取第 H2 個。
• IFNA(…, "查無此品")：只有碰到 #N/A 才換成這段文字，其他的錯誤不會被蓋掉。

J 欄的單價只差在取值的範圍，換成 C 欄：

兩欄都往下複製，來看看結果：

我個人喜歡把 MATCH 獨立成一欄，原因有兩個。一是同一個品名只找一次位置，代碼、單價、庫存各自 INDEX 就好，公式都很短。二是哪一筆查不到，看 H 欄就知道，不用去拆一條長公式。

要再補庫存的話，多一欄 INDEX($D$2:$D$8, $H2) 就好，MATCH 不用動。

算出金額

K 欄是杯數乘單價。奶蓋紅茶的單價是文字，直接乘會出錯，所以先確認單價真的是數字：

先手算：四季春 12 × 40 = 480，青茶 9 × 35 = 315，烏龍奶茶 21 × 58 = 1218，檸檬紅茶 15 × 52 = 780，蘋果紅茶 33 × 45 = 1485，奶蓋紅茶 0。往下複製後，金額欄是 480、315、1218、780、1485、0。

總金額：

480 + 315 + 1218 + 780 + 1485 = 4278，公式也得到 4278。奶蓋紅茶算 0 只是讓總和不報錯，那一筆還是要回頭請人補進價目表，所以我會留著 I、J 欄的「查無此品」不刪。

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

• 訂單多一列：把 H 到 K 這幾欄往下複製一列就好。
• 價目表多一個品項：$B$2:$B$8、$A$2:$A$8、$C$2:$C$8 要一起拉長，例如全部改成到第 9 列。漏改其中一個，位置和取值就對不上，這個陷阱在 進階篇 會講。
• 價目表中間插入一欄：INDEX 和 MATCH 各自框的是整欄範圍，插入的欄不在它們裡面，公式不用重寫。如果用 VLOOKUP，第三個參數的欄號是寫死的數字，插入欄之後就要自己檢查。

雙向查表：品項乘門市

第二個任務。上個月四間門市各賣了多少杯，我想讓人選一個品項和一間門市，就看到那一格的杯數。這張表放在新的工作表，範圍 A1:E6：

標題列是第 1 列，品項在 A 欄。A9 寫「品項」、A10 寫「門市」，B9 輸入想查的品項「蘋果紅茶」，B10 輸入門市「新竹店」。手算的話，蘋果紅茶是第 2 列，新竹店是第 3 欄，交叉的那格是 41。

INDEX 的列和欄兩個參數，剛好各給一個 MATCH：

• B2:E6：只有數字的部分，不含標題列和品項欄。
• MATCH(B9, A2:A6, 0)：品項在 A 欄的位置，蘋果紅茶是 2，當作「列」。
• MATCH(B10, B1:E1, 0)：門市在標題列的位置，新竹店是 3，當作「欄」。

來看看結果：41。

這邊要注意，兩個 MATCH 的範圍要跟 INDEX 的範圍對齊。找列的範圍 A2:A6 從第 2 列起，跟 B2:E6 同一列開始，找欄的範圍 B1:E1 從 B 欄起，跟 B2:E6 同一欄開始。只要有一邊多框了標題，位置就會差一格。

B9 和 B10 我會設成下拉選單，輸入就不會打錯字，做法在 資料驗證。

整列、整欄一起拿

INDEX 的列或欄填 0，會把那一整列或整欄傳回來，這時候就可以交給 SUM、MAX 這類函式。

蘋果紅茶四間門市加起來賣了多少：

96 + 134 + 41 + 77 = 348，公式得到 348。

新竹店所有品項合計，換成列填 0、欄用 MATCH：

63 + 41 + 105 + 92 + 28 = 329，得到 329。

這間門市賣最好的是哪一項

再多一步：先找出某間門市那一欄的最大值，再用 MATCH 找它排第幾，最後用 INDEX 把品項名稱拿出來。

信義店那一欄是 B2:B6，最大值是 142，先固定寫死：

得到 烏龍奶茶。想讓門市跟著 B10 變，就把 B2:B6 換成前面那段整欄的 INDEX：

B10 是新竹店，那一欄最大的是 105，對到青茶，所以得到 青茶。這條公式有點長，我自己會把整欄的 INDEX 先放進一格輔助儲存格，或用 LET 命名，之後讀起來會輕鬆很多。

這時候還要用 INDEX 和 MATCH 嗎

單純的查表，我新做的表會先用 XLOOKUP，一條就完成。上面這幾個情境我還是會用 INDEX / MATCH：位置要被多個欄共用、雙向查表、取整列整欄去加總。其中多條件和近似比對的取捨，放在進階篇。

練習

多把位置獨立出來、多用整列整欄，你的查表就會越來越好維護，試試看吧！

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