INDEX和MATCH函數教學

INDEX 和 MATCH 是 Excel 中最常用的公式之一。這是因為 INDEX 和 MATCH 非常靈活,您可以進行水平和垂直搜尋、二維搜尋、多個條件的搜尋等。如果你想提昇你的 Excel 技巧,那麼就學習INDEX 和 MATCH吧。

本文簡單介紹如何配搭一起使用 INDEX 和 MATCH 來執行搜尋。首先解釋 INDEX,然後是 MATCH,然後向您展示如何將這兩個函數組合在一起作雙向搜尋。

INDEX 函數

INDEX 實際上是做什麼的?簡而言之,INDEX 檢索範圍內給定位置的值。例如,假設您有一張水果排名列表(見下文),並且您想通過公式獲得第四位的名稱,即Apple。您可以像這樣使用 INDEX:

=INDEX(A2:A6,4)

INDEX 返回範圍的第 4 行中的值。

如果你想用INDEX得到Price怎麼辦?在這種情況下,我們可以同時提供行號和列號。下面的 INDEX 公式使用 A2:B6 中的全範圍數據,行號為 4,列號為 2:

=INDEX(A2:B6,4,2)

INDEX 檢索第 4 行第 2 列的值。

總而言之,INDEX 根據數字位置在範圍內的定位獲取值。當範圍是一維時,您只需要提供一個行號。當範圍是二維的時,您需要提供行號和列號。

此時,您可能會想“那又怎樣?實際上怎會知道表格中某項內容的位置呢?”

非常正確。我們需要一種方法來定位我們正在尋找的東西的位置。

這方法就是MATCH功能。

MATCH功能

MATCH 函數被設計用於一個目的:在一個範圍內找到一個項目的位置。例如,我們可以使用 MATCH 來獲取單詞“Apple”在這個水果列表中的位置,如下所示:

=MATCH(“apple”,A2:A6,0)

MATCH 返回 4,因為“Apple”是第 4 項。MATCH 不區分大小寫。

MATCH 不關心範圍是水平的還是垂直的,如下所示:

=MATCH(“apple”,C2:G2,0)

水平範圍的結果相同,MATCH 返回 4。

重要提示:MATCH 函數中的最後一個參數是MATCH類型。MATCH類型很重要,它控制MATCH是精確MATCH還是近似MATCH。在許多情況下,您會希望使用零 (0) 來強制精確MATCH。MATCH類型默認為 1,這是近似MATCH,因此提供一個(0)值很重要。

INDEX 和 MATCH 一起配搭使用

現在我們已經介紹了 INDEX 和 MATCH 的基礎知識,我們如何將這兩個函數組合到一個公式中?考慮下面的數據,該表顯示了水果列表和四個月的價錢:

假設我們要編寫一個公式來返回給定水果三月份的價錢。從上面的討論中,我們知道我們可以給 INDEX 一個行號和列號來檢索一個值。例如,要返回 Apple的三月銷售額,我們提供範圍 B2:E6 的第 4 行和第 3 列:

=INDEX(B2:E6,4,3) // 返回 $6.2

但我們顯然不想對數字進行hard code。相反,我們想要動態搜尋。

我們將如何做到這一點?當然是 MATCH 函數。MATCH 可以完美地找到我們需要的位置。讓我們將列4變成動態化。這是修改後的公式,將 MATCH 函數嵌入在 INDEX 中代替 4:

=INDEX(B2:E6,MATCH(“Apple”,A2:A6,0),3)

更進一步,我們將在 MATCH 中使用 C8的值:

=INDEX(B2:E6,MATCH(C8,A2:A6,0),3)

MATCH 找到“Apple”並將 4 返回到行的 INDEX。

使用 INDEX 和 MATCH 進行雙向搜尋

上面,我們使用 MATCH 函數動態搜尋行號,但hard code了列號。我們如何使公式完全動態化,以便我們可以返回任何給定水果在任何給定月份的價錢?方法是使用 MATCH 兩次 — 一次獲得行位置,一次獲得列位置。

從上面的例子中,我們知道 MATCH 可以很好地處理水平和垂直陣列。這意味著我們可以使用 MATCH 輕鬆找到給定月份的位置。例如,此公式返回三月的位置,即 3:

=MATCH(“Mar”,B1:E1,0) // 返回 3

但當然我們不想hard code任何值,所以讓我們更新工作表以允許輸入月份名稱,並使用 MATCH 搜尋我們需要的列號。下面的屏幕顯示了結果:

使用 INDEX 和 MATCH 進行完全動態的雙向搜尋。

=INDEX(B2:E6,MATCH(C8,A2:A6,0),MATCH(C9,B1:E1,0))

第一個 MATCH 公式返回 4 給 INDEX 作為行號,第二個 MATCH 公式返回 3 給 INDEX 作為列號。一旦 MATCH 運行,公式就簡化為:

=INDEX( B2:E6, 4, 3 )

INDEX 正確返回 6.2,這是 Apple 三月份的價錢。

你可以觀看[Excel教學][入門] Index+Match教學 (廣東話)短片,了解有關例子的操作示範。

按此在Medium閱讀文章。

發表評論

Open chat
我有Excel問題想問Excel爸打