Excel MATCH 用法與函數範例教學

常常在工作的時候,我們會需要在眾多的資料中找出某位員工的業績區間,或者是需要查詢是否有新增加的貨物編碼,但是要使用甚麼樣的函數,才能夠讓你迅速地解決這些工作上的問題呢?

EXCEL 的 MATCH 函數就可以幫助你快速地解決這些問題。利用 MATCH 函數你可以找到你所查詢的項目與所對應欄數與列數。

同樣地能夠利用 MATCH 函數所回傳的欄數與第幾列,搭配 INDEX 函數來達到矩陣式的查詢,這樣即使資料不是在資料的最左欄,所以無法使用 VLOOKUP 函數,也能夠透過這兩種函數查詢到想要的資訊。

那麼 MATCH 是什麼樣的函數呢?它的運作邏輯是怎麼樣的呢?

MATCH 的運作原理就是能夠在單欄或單列當中,找出你查詢的項目是位於第幾項。

這裡要特別注意的是 MATCH 函數的查詢範圍只能夠是單欄或單列,所以它無法查詢含括整個表格的資料,除非搭配 INDEX 函數來做查詢。

本篇文章將為你詳細介紹 MATCH 函數的使用說明與實際用法,讓你透過它更快的從資料中篩選自己需要的結果。

MATCH函數說明

函數寫法

=MATCH (lookup_value,lookup_array, [match_type])

也就等同於

=MATCH (查詢項目,資料查詢範圍,比對方式)

從函數內可以看見 MATCH 函數內擁有3種參數,以下則是相關函數的說明。

  1. 「lookup_value」查詢項目:此參數就是你打算查詢的項目與依據,假如你想要找到某位同學的分數,那麼這位同學的名稱就是你的查詢項目,MATCH 函數會以此查詢對應的數值與項目。
  2. 「lookup_array」資料查詢範圍:設定查詢時對應的欄位與橫列範圍,函數將會從你選取的範圍內輸出所對應第幾位的編號。
  3. 「[match_type]」比對方式:此參數會以3種引數來代表不同的比對模式。

其中引數1指的就是尋找小於或等於 lookup_value 的最大值,引數0指的就是完全符合,引數-1則是尋找大於或等於查詢項目的最大值。

但是使用引數1的相似比對模式需要將資料查詢範圍以遞增的數值表示,引數-1則是需要以遞減的數值表示,不然就會出現N/A表示出現錯誤。

像是這張圖中的分數區間就是以遞增的數值表示。

接下來我們將會以微軟網頁版(OneDrive)的免費線上 EXCEL 表格做為示範操作。

並以圖中的分數區間表為範例,實際說明函數的各步驟將會如何達成我們所需要的結果。

1. 設定查詢項目

首先你需要將你所要查詢的項目輸入表格內,這樣函數才能依據你所要查詢的項目,對應到查詢範圍內進行搜尋。

像是下圖內,我們想要依照分數來做分數區間的查詢,那麼就得先輸入想查詢的分數來做為根據。

2. 設定 MATCH 函數

假設接下來如果要知道查詢的分數,在分數區間內的第幾列,請將滑鼠點擊分數區間的右側,並且點擊左上角的「插入」並點選「函數」,在類別的方面則選取「查閱與參照」之後點選 MATCH 即可。

如圖中紅色劃線處,就是所要點選的位置。

接下來 MATCH 函數當中的三個參數當中,第一項參數「lookup_value」查詢項目,可以點選 F3 的儲存格為參數,並使用逗點來隔開第二項參數。

這樣做的話往後查詢項目就可以隨著不同的分數來查詢到對應的數值,而不用每次調整 MATCH 函數的公式。

第二項參數「lookup_array」資料查詢範圍,則使用滑鼠圈選出分數區間的範圍,而因為 MATCH 函數只能唯一欄或一列,因此我們圈選分數區間的B3:B7儲存格,為查詢範圍設定。

如圖中紅色框選出來的位置,就是選取的查詢範圍。

第三項參數[match_type]比對方式,因為我們儲存格當中的分數並無法完全準確的對應到分數區間,因此我們採用引數1的相似比對模式,這樣此函數就能自動尋找小於或等於 lookup_value 的最大值,而得出來的最終數字3,就能夠知道此數值位於分數區間中的第3列。

那麼如果我們想要再透過分數區間,找出此分數的等第是多少,那麼我們就需要用到 INDEX 函數來輔助。

 

INDEX函數說明

函數寫法

=INDEX (array,row_num,[column_num])

也就等同於

=INDEX (資料查詢的範圍,查找的列數,查找的欄數)

從函數內我們可以看見有三項參數 

  1. 「array」資料查詢的範圍:你可以透過圈選一整列、一整欄、範圍內的表格當作你的資料查詢範圍。
  2. 「row_num」查找的列數:當選取了範圍後,INDEX 函數就能以你所查找的列數,來回傳對應的項目。
  3. 「[column_num]」查找的欄數:當選取了範圍後,INDEX 函數就能以你所查找的欄數,來回傳對應的項目。

而在這裡要注意的是,當資料查詢的範圍為同一列或同一欄,則只需要輸入一項對應的數值作為參數即可。

設定INDEX函數

那麼接下來如果希望以 INDEX 函數,使用上面 MATCH 函數所得出的欄位,來知道此分數會對應到幾等第的話。

首先在儲存格 F5 插入 INDEX 函數,並設定第一項參數資料查詢的範圍為 C3:C7。

因為我們只以等第這欄為查詢範圍,因此第二項參數查找的列數,只要設定為 F4 儲存格即可。

設定好函數之後,就能得到如圖中的表格,透過修改分數就能知道所對應的等第是多少。

最終在透過 MATCH 函數與 INDEX 函數的聯合使用下,就能夠在輸入分數的同時,迅速地得到我們想要查找的對應等第了。