本教程展示了如何在Excel中使用INDEX和MATCH函數(shù)，以及它為什么比VLOOKUP更優(yōu)越。

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

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

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

INDEX函數(shù) - 語法和用法

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

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

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

有關更多信息，請參閱Excel INDEX函數(shù)。

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

=INDEX(A1:C10,2,3)

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

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

MATCH函數(shù) - 語法和用法

Excel的MATCH函數(shù)在單元格范圍內搜索查找值，并返回該值在范圍中的相對位置。

MATCH函數(shù)的語法如下：

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

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

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

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

有關更多信息，請參閱Excel MATCH函數(shù)。

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

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

如何在Excel中使用INDEX MATCH函數(shù)

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

對于垂直查找，您只使用MATCH函數(shù)來確定行號，并直接將列范圍提供給INDEX：

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

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

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

現(xiàn)在，讓我們分析這個公式的每個組成部分實際上做了什么：

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

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

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

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

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

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

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

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

提示：

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

INDEX MATCH與VLOOKUP的比較

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

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

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

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

要了解INDEX MATCH與XLOOKUP之間的細微差別，請?zhí)剿鞅局改现械纳钊敕治觯篍xcel XLOOKUP與INDEX MATCH。

Excel INDEX MATCH - 公式示例

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

從右到左查找的INDEX MATCH公式

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

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

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

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

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

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

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

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

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

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

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

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

此公式的工作原理

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

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

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

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

INDEX(B2:D11, 2, 3)

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

Excel INDEX MATCH查找多個條件

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

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

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

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

以下INDEX MATCH公式效果很好：

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

其中C2:C10是要從中返回值的范圍，F(xiàn)1是criteria1，A2:A10是與criteria1進行比較的范圍，F(xiàn)2是criteria2，B2:B10是與criteria2進行比較的范圍。

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

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

這些公式的工作原理

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

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

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

Excel INDEX MATCH與AVERAGE、MAX、MIN

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

結合MAX的INDEX MATCH

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

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

結合MIN的INDEX MATCH

要定位列D中的最小值并從列C中提取關聯(lián)的值，請使用以下公式：

=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 ))

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

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

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

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

結合IFNA / IFERROR使用INDEX MATCH

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

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

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

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

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

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

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

練習工作簿下載

Excel INDEX MATCH示例（.xlsx文件）

以上是Excel索引匹配與Vlookup-公式示例的詳細內容。更多信息請關注PHP中文網(wǎng)其他相關文章！