根據《DIME》報導，Excel用戶在處理資料查詢時，VLOOKUP常被視為首選功能。然而，當資料需要「搜尋左側資料」、「動態指定欄位」或「資料結構不穩定」時，VLOOKUP的限制就會顯現。為此，INDEX與MATCH函數的組合提供了更強大的解決方案，特別是在需要靈活應對資料變化時。本文將以圖解方式說明VLOOKUP與INDEX+MATCH的差異與使用場景。

VLOOKUP的限制：你曾經遇到過嗎？ 當你使用VLOOKUP查詢資料時，是否曾遇到這些情況？

  • 想要取得資料欄位位於查詢欄位的左側，但無法實現。
  • 在資料表中新增或刪除欄位時，所有公式都因為「列數編號」錯位而出錯。
  • 當資料欄位眾多時，需要反覆輸入VLOOKUP公式，導致公式過於繁雜。

這些問題正是VLOOKUP的「設計限制」所致，而INDEX+MATCH則可克服這一切。

VLOOKUP vs. INDEX+MATCH：快速比較 如果僅需進行「向右搜尋」，VLOOKUP仍是簡單且實用的選擇。但當你需要：

  • 向左搜尋資料
  • 動態指定欄位
  • 處理資料結構變動

INDEX+MATCH便成為更穩健的方案。對於Microsoft 365或Excel 2021以上版本用戶，XLOOKUP則提供了更簡潔的替代方案。

INDEX+MATCH的基礎構造與應用 INDEX函數用來取得特定位置的資料值，而MATCH函數則可搜尋資料位置，兩者搭配後能精準定位資料。

=INDEX(回傳範圍, MATCH(搜尋值, 搜尋範圍, 0))

例如，若想根據商品ID取得商品名，可使用：

=INDEX(G:G, MATCH(C3, F:F, 0))

這表示在F列中搜尋C3的值，並於G列中回傳對應資料。與VLOOKUP不同的是，INDEX+MATCH不會因為資料結構變動而出錯，因為它是直接指定欄位。

三個INDEX+MATCH的實務應用場景

  1. 向左搜尋資料 VLOOKUP只能搜尋目標欄位往右的資料，而INDEX+MATCH則能跨越欄位限制，從目標欄位往左取得資料。

  2. 資料結構變動時的穩定性 當資料表中新增或刪除欄位時，INDEX+MATCH不會因為列數編號偏移而出錯，相比之下，VLOOKUP的公式需重新調整。

  3. 動態指定欄位 透過MATCH函數與欄位標題的結合，可讓INDEX+MATCH根據指定的標題動態選擇欄位，提升公式靈活性。

=INDEX(F:I, MATCH(C3, F:F, 0), MATCH(G1, F1:I1, 0))

此公式中，G1可用來選擇「商品名」、「單價」或「製造商」等欄位，並自動調整回傳結果，非常適合用於報表的互動設計。

VLOOKUP、INDEX+MATCH、XLOOKUP的選擇策略 若團隊成員皆使用Microsoft 365或Excel 2021以上版本，則XLOOKUP是最佳選擇，其語法更簡潔，功能也更強大。然而，在使用舊版Excel或需確保與舊版本相容的場合，INDEX+MATCH仍是不可替代的方案。

常見問題解答（FAQ）

  • Q：INDEX+MATCH會比VLOOKUP慢嗎？ A：在一般業務資料量（數千～數萬筆）下，兩者的速度差異並不明顯。

  • Q：INDEX+MATCH能否處理複數條件？ A：可以，透過&運算符號可將多個條件合併，例如：

=INDEX(H:H, MATCH(B3&C3, F:F&G:G, 0))

  • Q：XLOOKUP與INDEX+MATCH哪個更好？ A：若使用新版本Excel，XLOOKUP的語法更簡潔且功能更強；但如果需與舊版本相容，則INDEX+MATCH更為穩定。

結語 INDEX+MATCH的靈活性與穩定性，讓它成為VLOOKUP的強大替代者。雖然XLOOKUP在新版本中提供了更簡潔的解法，但在舊版本環境中，INDEX+MATCH依然是不可或缺的工具。掌握這三種函數的應用，將能大幅提升Excel資料處理的效率與穩定性。

📰 本文資料來源 • DIME（原文為日文，本文已根據內容翻譯為中文）