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

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

“為什麼我需要這個(gè)？”您可能會(huì)問(wèn)。因?yàn)閂LOOKUP有許多限制，在許多情況下可能會(huì)阻止您獲得期望的結(jié)果。另一方面，INDEX MATCH組合更靈活，具有許多出色的功能，使其在許多方面優(yōu)於VLOOKUP。

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

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

Excel的INDEX函數(shù)根據(jù)您指定的行和列號(hào)從數(shù)組中返回一個(gè)值。 INDEX函數(shù)的語(yǔ)法非常簡(jiǎn)單：

INDEX(array, row_num, [column_num]) 以下是對(duì)每個(gè)參數(shù)的簡(jiǎn)單解釋?zhuān)?/p>

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

有關(guān)更多信息，請(qǐng)參閱Excel INDEX函數(shù)。

這是一個(gè)最簡(jiǎn)單的INDEX公式示例：

=INDEX(A1:C10,2,3)

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

非常簡(jiǎn)單，對(duì)吧？然而，在處理真實(shí)數(shù)據(jù)時(shí)，您幾乎永遠(yuǎn)不會(huì)知道想要的行和列號(hào)，這就是MATCH函數(shù)派上用場(chǎng)的地方。

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

Excel的MATCH函數(shù)在單元格範(fàn)圍內(nèi)搜索查找值，並返回該值在範(fàn)圍中的相對(duì)位置。

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

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

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

例如，如果範(fàn)圍B1:B3包含值“New-York”、“Paris”、“London”，下面的公式返回?cái)?shù)字3，因?yàn)椤癓ondon”是范圍中的第三個(gè)條目：

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

有關(guān)更多信息，請(qǐng)參閱Excel MATCH函數(shù)。

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

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

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

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

對(duì)於垂直查找，您只使用MATCH函數(shù)來(lái)確定行號(hào)，並直接將列範(fàn)圍提供給INDEX：

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

要查找某個(gè)首都的人口，比如日本的首都，請(qǐng)使用以下INDEX MATCH公式：

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

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

• MATCH函數(shù)在範(fàn)圍A2:A10中搜索查找值“Japan”，並返回?cái)?shù)字3，因?yàn)椤癑apan”是查找數(shù)組中的第三個(gè)。
• 行號(hào)直接傳遞給INDEX的row_num參數(shù)，指導(dǎo)它從該行返回一個(gè)值。

因此，上面的公式變成一個(gè)簡(jiǎn)單的INDEX(C2:C,3)，表示在C2到C10單元格中搜索並從該範(fàn)圍的第3個(gè)單元格中提取值，即C4，因?yàn)槲覀儚牡诙虚_(kāi)始計(jì)數(shù)。

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

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

重要提示！ INDEX的array參數(shù)中的行數(shù)應(yīng)與MATCH的lookup_array參數(shù)中的行數(shù)匹配，否則公式將產(chǎn)生錯(cuò)誤結(jié)果。

等等……為什麼我們不簡(jiǎn)單地使用以下Vlookup公式？浪費(fèi)時(shí)間試圖弄清楚Excel MATCH INDEX的奧秘有什麼意義？

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

在這種情況下，完全沒(méi)有意義:) 這個(gè)簡(jiǎn)單的示例僅用於演示目的，以便您了解INDEX和MATCH函數(shù)如何一起工作。以下示例將向您展示這種組合的真正力量，它可以輕鬆?wèi)?yīng)對(duì)許多複雜場(chǎng)景，而VLOOKUP則會(huì)遇到困難。

提示：

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

INDEX MATCH與VLOOKUP的比較

在決定使用哪個(gè)函數(shù)進(jìn)行垂直查找時(shí)，大多數(shù)Excel專(zhuān)家都認(rèn)為INDEX MATCH遠(yuǎn)優(yōu)於VLOOKUP。然而，許多人仍然堅(jiān)持使用VLOOKUP，首先是因?yàn)樗?jiǎn)單，其次是因?yàn)樗麄儧](méi)有完全理解使用Excel中的INDEX MATCH公式的所有好處。沒(méi)有這種理解，沒(méi)有人願(yuàn)意花時(shí)間學(xué)習(xí)更複雜的語(yǔ)法。

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

使用INDEX MATCH代替VLOOKUP的4個(gè)主要原因

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

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

Excel INDEX MATCH - 公式示例

了解了學(xué)習(xí)MATCH INDEX函數(shù)的理由，讓我們進(jìn)入最有趣的部分，看看如何將理論知識(shí)應(yīng)用於實(shí)踐。

從右到左查找的INDEX MATCH公式

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

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

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

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

提示。如果您計(jì)劃將INDEX MATCH公式用於多個(gè)單元格，請(qǐng)務(wù)必使用絕對(duì)單元格引用（如$A$2:$A$10和$C$2:$C$10）鎖定兩個(gè)範(fàn)圍，以免在復(fù)制公式時(shí)發(fā)生變形。

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

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

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

簡(jiǎn)單地說(shuō)，使用兩個(gè)MATCH函數(shù)- 一個(gè)獲取行號(hào)，另一個(gè)獲取列號(hào)。我祝賀那些猜對(duì)的人:)

INDEX(array, MATCH( vlookup value , column to look up against , 0), MATCH( hlookup value , row to look up against , 0)) 現(xiàn)在，請(qǐng)查看下面的表格，讓我們構(gòu)建一個(gè)INDEX MATCH MATCH公式來(lái)查找給定國(guó)家在給定年份的人口（以百萬(wàn)計(jì)）。

在G1中輸入目標(biāo)國(guó)家（vlookup值），在G2中輸入目標(biāo)年份（hlookup值），公式如下所示：

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

此公式的工作原理

每當(dāng)您需要理解一個(gè)複雜的Excel公式時(shí)，請(qǐng)將其分解成更小的部分，看看每個(gè)單獨(dú)的函數(shù)做了什麼：

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

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

上述行和列號(hào)傳遞給INDEX函數(shù)的相應(yīng)參數(shù)：

INDEX(B2:D11, 2, 3)

結(jié)果，您在B2:D11範(fàn)圍內(nèi)的第2行和第3列的交叉點(diǎn)處獲得一個(gè)值，即D3單元格中的值。簡(jiǎn)單嗎？是的！

Excel INDEX MATCH查找多個(gè)條件

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

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

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

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

以下INDEX MATCH公式效果很好：

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

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

記得通過(guò)按Ctrl Shift Enter正確輸入公式，Excel會(huì)自動(dòng)用花括號(hào)括起公式，如截圖所示：

如果您寧願(yuàn)不在工作表中使用數(shù)組公式，請(qǐng)?jiān)诠街刑砑恿硪粋€(gè)INDEX函數(shù)，並用通常的Enter鍵完成：

這些公式的工作原理

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

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

這是公式邏輯的高級(jí)解釋。有關(guān)完整細(xì)節(jié)，請(qǐng)參閱Excel INDEX MATCH與多個(gè)條件。

Excel INDEX MATCH與AVERAGE、MAX、MIN

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

結(jié)合MAX的INDEX MATCH

要查找列D中的最大值並從同一行中的列C返回一個(gè)值，請(qǐng)使用此公式：

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

結(jié)合MIN的INDEX MATCH

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

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

結(jié)合AVERAGE的INDEX MATCH

要計(jì)算D2:D10中最接近平均值的值並從列C中獲取相應(yīng)的值，請(qǐng)使用以下公式：

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

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

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

在我們的示例中，列D中的人口按降序排序，因此我們使用-1作為匹配類(lèi)型。結(jié)果，我們得到“Tokyo”，因?yàn)槠淙丝冢?3,189,000）是最接近大於平均值（12,269,006）的匹配。

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

結(jié)合IFNA / IFERROR使用INDEX MATCH

正如您可能已經(jīng)註意到，如果Excel中的INDEX MATCH公式找不到查找值，它會(huì)產(chǎn)生一個(gè)#N/A錯(cuò)誤。如果您希望用更有意義的內(nèi)容替換標(biāo)準(zhǔn)錯(cuò)誤符號(hào)，請(qǐng)將您的INDEX MATCH公式包裝在IFNA函數(shù)中。例如：

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

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

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

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

請(qǐng)記住，在許多情況下，掩蓋所有錯(cuò)誤可能是不明智的，因?yàn)樗鼈儠?huì)提醒您公式中可能存在的故障。

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

練習(xí)工作簿下載

Excel INDEX MATCH示例（.xlsx文件）

以上是Excel索引匹配與Vlookup-公式示例的詳細(xì)內(nèi)容。更多資訊請(qǐng)關(guān)注PHP中文網(wǎng)其他相關(guān)文章！