Excel索引匹配與Vlookup-公式示例

本教程展示瞭如何在Excel中使用INDEX和MATCH函數，以及它為什麼比VLOOKUP更優越。

在最近的幾篇文章中，我們努力向初學者解釋了VLOOKUP函數的基礎知識，並為高級用戶提供了更複雜的VLOOKUP公式示例。現在，我將嘗試不僅僅是勸您不要使用VLOOKUP，而是至少展示一種在Excel中進行垂直查找的替代方法。

“為什麼我需要這個？”您可能會問。因為VLOOKUP有許多限制，在許多情況下可能會阻止您獲得期望的結果。另一方面，INDEX MATCH組合更靈活，具有許多出色的功能，使其在許多方面優於VLOOKUP。

由於本教程的目的是展示如何通過結合使用INDEX和MATCH函數來在Excel中進行替代的垂直查找，我們不會詳細討論它們的語法和用法。我們只會覆蓋理解總體概念所需的最低限度，然後深入探討公式示例，這些示例揭示了使用INDEX MATCH代替VLOOKUP的所有優勢。

INDEX函數- 語法和用法

Excel的INDEX函數根據您指定的行和列號從數組中返回一個值。 INDEX函數的語法非常簡單：

INDEX(array, row_num, [column_num]) 以下是對每個參數的簡單解釋：

• array - 您想要從中返回值的單元格範圍。
• row_num - 您想要從中返回值的數組中的行號。如果省略，則需要column_num。
• column_num - 您想要從中返回值的數組中的列號。如果省略，則需要row_num。

有關更多信息，請參閱Excel INDEX函數。

這是一個最簡單的INDEX公式示例：

=INDEX(A1:C10,2,3)

該公式在A1到C10單元格中搜索，並返回第2行第3列的單元格值，即C2單元格。

非常簡單，對吧？然而，在處理真實數據時，您幾乎永遠不會知道想要的行和列號，這就是MATCH函數派上用場的地方。

MATCH函數- 語法和用法

Excel的MATCH函數在單元格範圍內搜索查找值，並返回該值在範圍中的相對位置。

MATCH函數的語法如下：

MATCH(lookup_value, lookup_array, [match_type]) - lookup_value - 您正在查找的數字或文本值。

• lookup_array - 正在搜索的單元格範圍。
• match_type - 指定返回精確匹配還是最接近的匹配：
  • 1或省略（默認） - 近似匹配（下一個較小的）。
  • 0 - 精確匹配。在INDEX/MATCH組合中，您幾乎總是需要精確匹配，因此您將MATCH函數的第三個參數設置為0。
  • -1 - 近似匹配（下一個較大的）。

例如，如果範圍B1:B3包含值“New-York”、“Paris”、“London”，下面的公式返回數字3，因為“London”是范圍中的第三個條目：

=MATCH("London",B1:B3,0)

有關更多信息，請參閱Excel MATCH函數。

乍一看，MATCH函數的用處似乎值得懷疑。誰會在意值在範圍中的位置？我們真正想知道的是值本身。

讓我提醒您，查找值的相對位置（即行和列號）正是您需要提供給INDEX函數的row_num和column_num參數的。您記得，Excel INDEX可以找到給定行和列交匯處的值，但它無法確定您想要的具體行和列。

如何在Excel中使用INDEX MATCH函數

現在您已經了解了基礎知識，我相信您已經開始理解MATCH和INDEX是如何一起工作的。簡而言之，INDEX通過列和行號查找查找值，而MATCH提供這些號碼。就是這樣！

對於垂直查找，您只使用MATCH函數來確定行號，並直接將列範圍提供給INDEX：

INDEX(要從中返回值的列, MATCH(查找值,要查找的列, 0)) 仍然難以理解嗎？通過一個示例可能更容易理解。假設您有一份國家首都及其人口的列表：

要查找某個首都的人口，比如日本的首都，請使用以下INDEX MATCH公式：

=INDEX(C2:C10, MATCH("Japan", A2:A10, 0))

現在，讓我們分析這個公式的每個組成部分實際上做了什麼：

• MATCH函數在範圍A2:A10中搜索查找值“Japan”，並返回數字3，因為“Japan”是查找數組中的第三個。
• 行號直接傳遞給INDEX的row_num參數，指導它從該行返回一個值。

因此，上面的公式變成一個簡單的INDEX(C2:C,3)，表示在C2到C10單元格中搜索並從該範圍的第3個單元格中提取值，即C4，因為我們從第二行開始計數。

不想在公式中硬編碼城市嗎？在某個單元格中輸入它，比如F1，將單元格引用提供給MATCH，您將得到一個動態查找公式：

=INDEX(C2:C10, MATCH(F1,A2:A10,0))

重要提示！ INDEX的array參數中的行數應與MATCH的lookup_array參數中的行數匹配，否則公式將產生錯誤結果。

等等……為什麼我們不簡單地使用以下Vlookup公式？浪費時間試圖弄清楚Excel MATCH INDEX的奧秘有什麼意義？

=VLOOKUP(F1, A2:C10, 3, FALSE)

在這種情況下，完全沒有意義:) 這個簡單的示例僅用於演示目的，以便您了解INDEX和MATCH函數如何一起工作。以下示例將向您展示這種組合的真正力量，它可以輕鬆應對許多複雜場景，而VLOOKUP則會遇到困難。

提示：

• 在Excel 365和Excel 2021中，您可以使用更現代的INDEX XMATCH公式。
• 對於Google Sheets，請參閱本文中的INDEX MATCH公式示例。

INDEX MATCH與VLOOKUP的比較

在決定使用哪個函數進行垂直查找時，大多數Excel專家都認為INDEX MATCH遠優於VLOOKUP。然而，許多人仍然堅持使用VLOOKUP，首先是因為它更簡單，其次是因為他們沒有完全理解使用Excel中的INDEX MATCH公式的所有好處。沒有這種理解，沒有人願意花時間學習更複雜的語法。

下面，我將指出MATCH INDEX相對於VLOOKUP的主要優勢，您可以決定它是否值得加入您的Excel工具庫。

使用INDEX MATCH代替VLOOKUP的4個主要原因

1. 從右到左查找。任何有經驗的用戶都知道，VLOOKUP無法向左查找，這意味著您的查找值始終應位於表的最左列。 INDEX MATCH可以輕鬆進行左側查找！以下示例展示了它的實際操作：如何在Excel中查找左側的值。
2. 安全地插入或刪除列。當從查找表中刪除或添加新列時，VLOOKUP公式會損壞或提供錯誤的結果，因為VLOOKUP的語法要求指定您想要從中提取數據的列的索引號。自然，當您添加或刪除列時，索引號會發生變化。使用INDEX MATCH，您指定返回列範圍，而不是索引號。因此，您可以自由地插入和刪除任意數量的列，而無需擔心更新每個相關的公式。
3. 查找值的大小無限制。使用VLOOKUP函數時，您的查找條件的總長度不能超過255個字符，否則您將得到#VALUE!錯誤。因此，如果您的數據集包含長字符串，INDEX MATCH是唯一可行的解決方案。
4. 更高的處理速度。如果您的表格相對較小，Excel性能方面幾乎不會有顯著差異。但是，如果您的工作表包含數百或數千行，以及相應的數百或數千個公式，MATCH INDEX將比VLOOKUP運行得更快，因為Excel只需處理查找和返回列，而不是整個表數組。如果您的工作簿包含像VLOOKUP和SUM這樣的複雜數組公式，VLOOKUP對Excel性能的影響可能會特別明顯。關鍵在於檢查數組中的每個值都需要單獨調用VLOOKUP函數。因此，您的數組包含的值越多，您的工作簿中的數組公式越多，Excel的性能就越慢。

要了解INDEX MATCH與XLOOKUP之間的細微差別，請探索本指南中的深入分析：Excel XLOOKUP與INDEX MATCH。

Excel INDEX MATCH - 公式示例

了解了學習MATCH INDEX函數的理由，讓我們進入最有趣的部分，看看如何將理論知識應用於實踐。

從右到左查找的INDEX MATCH公式

如前所述，VLOOKUP無法向左查找。因此，除非您的查找值位於最左列，否則Vlookup公式幾乎不可能為您帶來想要的結果。 Excel中的INDEX MATCH函數更具多功能性，並且並不真正關心查找和返回列的位置。

對於這個示例，我們將在示例表的左側添加Rank列，並嘗試找出俄羅斯首都莫斯科的人口排名。

在G1中輸入查找值，使用以下公式在C2:C10中搜索並從A2:A10中返回相應的值：

=INDEX(A2:A10,MATCH(G1,C2:C10,0))

提示。如果您計劃將INDEX MATCH公式用於多個單元格，請務必使用絕對單元格引用（如$A$2:$A$10和$C$2:$C$10）鎖定兩個範圍，以免在復制公式時發生變形。

使用INDEX MATCH MATCH在行和列中查找

在上面的示例中，我們使用INDEX MATCH作為經典VLOOKUP的替代品，從預定義的單列範圍中返回值。但是，如果您需要在多個行和列中查找呢？換句話說，如果您想執行所謂的矩陣或雙向查找呢？

這聽起來可能很棘手，但公式與基本的Excel INDEX MATCH函數非常相似，只有一個區別。猜猜是什麼？

簡單地說，使用兩個MATCH函數- 一個獲取行號，另一個獲取列號。我祝賀那些猜對的人:)

INDEX(array, MATCH( vlookup value , column to look up against , 0), MATCH( hlookup value , row to look up against , 0)) 現在，請查看下面的表格，讓我們構建一個INDEX MATCH MATCH公式來查找給定國家在給定年份的人口（以百萬計）。

在G1中輸入目標國家（vlookup值），在G2中輸入目標年份（hlookup值），公式如下所示：

=INDEX(B2:D11, MATCH(G1,A2:A11,0), MATCH(G2,B1:D1,0))

此公式的工作原理

每當您需要理解一個複雜的Excel公式時，請將其分解成更小的部分，看看每個單獨的函數做了什麼：

MATCH(G1,A2:A11,0) - 在A2:A11中搜索G1單元格中的值（“China”）並返回其位置，即2。

MATCH(G2,B1:D1,0)) - 在B1:D1中搜索以獲取G2單元格中值（“2015”）的位置，即3。

上述行和列號傳遞給INDEX函數的相應參數：

INDEX(B2:D11, 2, 3)

結果，您在B2:D11範圍內的第2行和第3列的交叉點處獲得一個值，即D3單元格中的值。簡單嗎？是的！

Excel INDEX MATCH查找多個條件

如果您有機會閱讀我們的Excel VLOOKUP教程，您可能已經測試了一個帶有多個條件的Vlookup公式。然而，該方法的一個顯著限制是需要添加一個輔助列。好消息是，Excel的INDEX MATCH函數也可以查找兩個或更多條件，而無需修改或重組源數據！

以下是帶有多個條件的通用INDEX MATCH公式：

{=INDEX( return_range , MATCH(1, ( criteria1 = range1 ) ( criteria2 = range2*), 0))} 注意。這是一個數組公式，必須使用Ctrl Shift Enter快捷鍵完成。

在下面的示例表中，假設您想根據兩個條件， Customer和Product查找金額。

以下INDEX MATCH公式效果很好：

=INDEX(C2:C10, MATCH(1, (F1=A2:A10) * (F2=B2:B10), 0))

其中C2:C10是要從中返回值的範圍，F1是criteria1，A2:A10是與criteria1進行比較的範圍，F2是criteria2，B2:B10是與criteria2進行比較的範圍。

記得通過按Ctrl Shift Enter正確輸入公式，Excel會自動用花括號括起公式，如截圖所示：

如果您寧願不在工作表中使用數組公式，請在公式中添加另一個INDEX函數，並用通常的Enter鍵完成：

這些公式的工作原理

這些公式使用與基本INDEX MATCH函數相同的方法，通過單列查找。為了評估多個條件，您創建兩個或多個表示每個單獨條件的匹配和非匹配的TRUE和FALSE值數組，然後將這些數組的相應元素相乘。乘法操作將TRUE和FALSE轉換為1和0，並生成一個數組，其中1對應於滿足所有條件的行。 MATCH函數以查找值1查找數組中的第一個“1”，並將其位置傳遞給INDEX，INDEX從指定列中返回該行中的值。

非數組公式依賴於INDEX函數處理數組的原生能力。第二個INDEX配置為0 row_num ，因此它將整個列數組傳遞給MATCH。

這是公式邏輯的高級解釋。有關完整細節，請參閱Excel INDEX MATCH與多個條件。

Excel INDEX MATCH與AVERAGE、MAX、MIN

Microsoft Excel有專門的函數來查找範圍內的最小、最大和平均值。但是，如果您需要從與這些值相關聯的另一個單元格中獲取值呢？在這種情況下，請將MAX、MIN或AVERAGE函數與INDEX MATCH結合使用。

結合MAX的INDEX MATCH

要查找列D中的最大值並從同一行中的列C返回一個值，請使用此公式：

=INDEX(C2:C10, MATCH(MAX(D2:D10), D2:D10, 0))

結合MIN的INDEX MATCH

要定位列D中的最小值並從列C中提取關聯的值，請使用以下公式：

=INDEX(C2:C10, MATCH(MIN(D2:D10), D2:D10, 0))

結合AVERAGE的INDEX MATCH

要計算D2:D10中最接近平均值的值並從列C中獲取相應的值，請使用以下公式：

=INDEX(C2:C10, MATCH(AVERAGE(D2:D10), D2:D10, -1 ))

根據您的數據如何組織，向MATCH函數的第三個參數（match_type）提供1或-1：

• 如果您的查找列（在我們的例子中是列D）按升序排序，請輸入1。公式將計算小於或等於平均值的最大值。
• 如果您的查找列按降序排序，請輸入-1。公式將計算大於或等於平均值的最小值。
• 如果您的查找數組包含一個恰好等於平均值的值，您可以輸入0以進行精確匹配。不需要排序。

在我們的示例中，列D中的人口按降序排序，因此我們使用-1作為匹配類型。結果，我們得到“Tokyo”，因為其人口（13,189,000）是最接近大於平均值（12,269,006）的匹配。

您可能很好奇，VLOOKUP也可以執行此類計算，但作為數組公式：VLOOKUP與AVERAGE、MAX、MIN。

結合IFNA / IFERROR使用INDEX MATCH

正如您可能已經註意到，如果Excel中的INDEX MATCH公式找不到查找值，它會產生一個#N/A錯誤。如果您希望用更有意義的內容替換標準錯誤符號，請將您的INDEX MATCH公式包裝在IFNA函數中。例如：

=IFNA(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "No match is found")

現在，如果有人輸入查找表中不存在的值，公式將明確告知用戶未找到匹配：

如果您希望捕獲所有錯誤，而不僅僅是#N/A，請改用IFERROR函數而不是IFNA：

=IFERROR(INDEX(C2:C10, MATCH(F1,A2:A10,0)), "Oops, something went wrong!")

請記住，在許多情況下，掩蓋所有錯誤可能是不明智的，因為它們會提醒您公式中可能存在的故障。

這就是如何在Excel中使用INDEX和MATCH。我希望我們的公式示例對您有幫助，並期待下週在我們的博客上見到您！

練習工作簿下載

Excel INDEX MATCH示例（.xlsx文件）

以上是Excel索引匹配與Vlookup-公式示例的詳細內容。更多資訊請關注PHP中文網其他相關文章！