INDEX 與 MATCH 進階陷阱：重複值、多條件、範圍錯位與近似比對

我想查「青茶」大杯（L）的單價，公式沒有報錯，回來的卻是 35，那是中杯（M）的價格。INDEX / MATCH 最麻煩的地方就是這樣，錯的時候常常不會有 #N/A 提醒你。

各自的語法在 INDEX 與 MATCH 入門，實際做事的例子在 情境實戰篇，這篇直接從會出事的地方開始。

後面大部分的例子都用這張價目表，放在 A1:C9，每個品項有 M 和 L 兩種杯型：

F1 放品項「青茶」，F2 放杯型「L」。

重複的值只會找到第一個

先用入門篇的寫法，只用品項去找：

結果是 35。MATCH 從上往下找，找到第一個符合的就停了。青茶在 A 欄的第 5 個和第 6 個都有，它只回 5，所以取到 C6 的 35。

原因講完了，修法有幾種，看資料的樣子選。

多條件：先把兩欄接成一欄

最直覺的是加一欄輔助欄，把品項和杯型接起來。D2 輸入後往下複製到 D9：

D 欄會變成 烏龍奶茶|M、烏龍奶茶|L……，每一列都不重複。查的時候也把兩個條件用同樣的方式接起來：

來看看結果：42。

中間那條 "|" 不要省。兩個條件直接接在一起，不同的組合有可能變成同一串字，例如品項 A1 加杯型 2，和品項 A 加杯型 12，接出來都是 A12。加一個資料裡不會出現的分隔字元，就不會撞到。

輔助欄的好處是公式短、好讀，缺點是多一欄要維護，資料新增時要記得把 D 欄往下複製。

多條件：不加欄的陣列寫法

不想加輔助欄的話，可以讓 MATCH 去找「兩個條件同時成立」的那一列：

• A2:A9=F1：A 欄每一格是不是青茶，得到一欄 TRUE / FALSE。
• B2:B9=F2：B 欄每一格是不是 L。
• 兩欄相乘：TRUE 當 1、FALSE 當 0，兩個都成立的列才是 1。
• MATCH(1, …, 0)：找第一個 1 在第幾個，這裡是 6。

來看看結果：42。外面包 ARRAYFORMULA，是讓括號裡的比較一次對整欄去做。

三個條件就多乘一組，不用再多加欄。我個人在資料表不能改的時候才用這種寫法，自己的表我還是比較喜歡輔助欄，陣列版過一陣子回頭看，要花時間才讀得懂。

那 SUMIFS 呢

如果查的是數字，而且每個組合只會出現一次，SUMIFS 也能做：

一樣得到 42，而且公式最短。不過它的前提是組合只會出現一次。假設有人不小心在第 10 列多貼了一列「青茶、L、42」，範圍也一起拉長到第 10 列，SUMIFS 會把兩列加起來得到 84，不會報錯。INDEX / MATCH 的兩種寫法遇到重複都只取第一個，還是 42。價格這種資料我比較在意「不要偷偷變成兩倍」，所以不拿 SUMIFS 當查表用。

要最後一筆，不是第一筆

另一種重複是同一個品項進了好幾次貨，我要的是最新的進貨價。進貨紀錄放在 A1:C7，依日期由舊到新排：

F1 放「烏龍茶葉」。入門篇的寫法 =INDEX(C2:C7, MATCH(F1, B2:B7, 0)) 會得到 310，那是 8/3 那一筆。

MATCH 沒辦法由下往上找，所以我改成算出「符合的位置裡最大的那一個」：

• 最外層先拒絕空白查詢，避免表內的空白列被當成符合項目。
• B2:B7=F1：烏龍茶葉的列是 TRUE，其他是 FALSE。
• ROW(B2:B7)-ROW(B2)+1：每一列在範圍裡是第幾個，1 到 6。
• 相乘之後，烏龍茶葉剩下 1、3、5，MAX 取 5，LET 把這個位置命名為 pos。
• 沒有符合的列時，pos 是 0，先回傳「查無」；有找到才交給 INDEX 取價。

來看看結果：318，也就是 9/14 的進貨價。這邊的「最後一筆」指的是表格最下面的那筆，不是日期最新的那筆，所以紀錄要照日期排好。

不能省略位置為 0 的判斷。INDEX(C2:C7, 0) 是回傳整欄範圍，所以原本直接把 MAX 交給 INDEX 的寫法，在查無品項時會往下溢出六筆價格，包 IFERROR 也擋不住。本例查「青茶茶葉」得到 268，查不存在的品項得到「查無」。最外層的空白判斷讓查詢空白時也固定回「查無」，即使資料範圍內有空白品項也一樣。這裡用 = 比較，*、? 會當作一般字元，不會當成萬用字元。

這個情況用 XLOOKUP 短很多，後面比較的時候再看。

範圍的起點要對齊

回到價目表。INDEX 取值的範圍和 MATCH 找位置的範圍是兩個各自獨立的參數，Sheets 不會檢查它們是不是從同一列開始。

我想查蘋果紅茶 M 杯，手滑把 MATCH 的範圍多框了標題列：

結果是 54，那是蘋果紅茶 L 的價格，正確答案是 45。蘋果紅茶在 A1:A9 是第 4 個，但在 C2:C9 的第 4 個是下一列的 C5。多框了一列，位置就整個往後差一格。把 MATCH 的範圍改成 A2:A9 就對了，位置是 3，取到 C4 的 45。

差一列還算好抓，因為值看起來不太對。如果剛好查的是最後一筆，位置會超出 INDEX 的範圍。進貨紀錄裡的「青茶茶葉」只有最後一列：

這次會得到 #NUM!，把滑鼠移上去，提示是 INDEX 的第 2 個參數超出範圍。

我的習慣是讓所有範圍的第一列、最後一列都一樣：要嘛都從第 2 列起，要嘛全部用整欄。

資料會增加的時候

價目表會一直加新品項的話，C2:C9 這種寫死的範圍，新增第 10 列就查不到了。Sheets 的範圍可以只寫起點、不寫終點：

假設我在第 10 列新增了「四季春、M、40」，這條公式不用改，會得到 40。C2:C9 的寫法就會是 #N/A，因為 MATCH 的範圍不包含第 10 列。

要注意兩點：

• 兩個範圍的起點一樣是第 2 列，上一節的對齊規則照舊。
• 這種範圍會包含表格下面的所有列，所以不要在價目表的正下方放別的東西。

另一個做法是把資料轉成表格，用欄位名稱當範圍，新增列時範圍會一起變長，做法在 表格入門。OFFSET 也做得到動態範圍，不過公式會變得很難讀，我個人幾乎不用。

近似比對：級距查表

前面都是「找這個東西在哪」，用 0。另一種是「落在哪一級」，才輪到 1 和 -1。

我有一張批量折扣表，放在 A1:B5：

D2 放訂購杯數 40：

搜尋類型 1 會找「小於或等於 40 的最大值」，也就是 36，位置是 3，所以得到 0.92。烏龍奶茶單價 58，40 杯就是 58 × 40 × 0.92 = 2134.4。

邊界值的結果，我用幾個杯數對一下：

剛好等於門檻的杯數，算在那一級。比第一級還小的數，例如杯數 -1，找不到「小於或等於它」的值，會得到 #N/A。

一定要由小到大排好

1 這個搜尋類型，前提是 A 欄由小到大。沒排序它也不會報錯，只是結果沒有保證，可能對也可能錯。入門篇那張沒排序的單價欄，省略第三個參數的 MATCH 實際得到的是 #N/A，而級距表要是被誰排錯了順序，你不一定每次都這麼幸運看到錯誤。所以我會在表旁邊註明「請維持由小到大」。

另外，品名、代碼這種要完全相同的查詢，千萬不要用 1。打錯一個字時，近似比對可能還是給你一個答案，而 0 會老實告訴你 #N/A。

由大到小用 -1

如果門檻是由大到小排的，搜尋類型改 -1，找的是「大於或等於它的最小值」。運費依重量上限算，放在 A1:B5，由大到小：

包裹 3.5 公斤，大於或等於 3.5 的最小上限是 5，位置是 2：

得到 140。超過最大的上限，例如 10.5 公斤，會得到 #N/A。

該用 INDEX 加 MATCH，還是 XLOOKUP？

同樣的價目表，單一條件 XLOOKUP 一條就好：

F1 是青茶，得到 35，一樣是第一個。語法在 XLOOKUP 入門。

XLOOKUP 在這篇的幾個陷阱上比較佔便宜：

• 範圍錯位：XLOOKUP 的查找範圍和結果範圍大小不同時會直接報 #N/A，提示「XLOOKUP 的陣列引數大小不同」。例如 =XLOOKUP("蘋果紅茶", A1:A9, C2:C9)，多框一列就被擋下來，不會像 INDEX / MATCH 那樣默默差一格。
• 最後一筆：搜尋模式填 -1 就由下往上找。進貨紀錄那題寫成 =XLOOKUP(F1, B2:B7, C2:C7, "查無", 0, -1)，一樣得到 318。
• 多條件：=ARRAYFORMULA(XLOOKUP(1, (A2:A9=F1)*(B2:B9=F2), C2:C9))，得到 42，邏輯跟 MATCH 的陣列寫法相同，另一種接成鍵的寫法與找不到、取最後一筆的處理在 XLOOKUP 兩個條件一起找。

INDEX / MATCH 還是有它的位置：

• 位置要給好幾個欄共用時，獨立一個 MATCH 欄最清楚。
• 取整列整欄去加總，或雙向查表，INDEX 的列欄參數比較直接，做法在情境實戰篇。
• 繼承別人的表，裡面本來就是這個組合，看得懂才改得動。

這裡不比誰比較快。表真的變慢，先看 Google 試算表效能基礎。我自己的做法是新做的查表先用 XLOOKUP，碰到上面這幾種情況才換回 INDEX / MATCH。

練習

先檢查範圍，再檢查重複值，多半就能找到原因，試試看吧！

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