Note: The other languages of the website are Google-translated. Back to English
登入  \/ 
x
or
x
註冊  \/ 
x

or

如何在 Excel 中同時使用 INDEX 和 MATCH

使用 Excel 表格時,您可能會不斷發現需要查找值的情況。 在本教程中,我們將向您展示如何應用 INDEX 和 MATCH 函數的組合來進行水平和垂直查找、雙向查找、區分大小寫的查找以及滿足多個條件的查找。

INDEX 和 MATCH 函數在 Excel 中的作用是什麼

如何同時使用 INDEX 和 MATCH 函數


INDEX 和 MATCH 函數在 Excel 中的作用是什麼

在我們使用 INDEX 和 MATCH 函數之前,讓我們確保我們知道 INDEX 和 MATCH 如何幫助我們首先查找值。

Excel中INDEX函數的使用

INDEX Excel 中的函數返回特定範圍內給定位置的值。 INDEX 函數的語法如下:

=INDEX(array, row_num, [column_num])
  • 排列 (required) 是指您要從中返回值的範圍。
  • 行數 (必需,除非存在 column_num)指的是數組的行號。
  • 列數 (可選,但如果省略 row_num 則是必需的)指的是數組的列號。

例如,要知道 傑夫的期末考試成績,列表中的第 6 個學生,您可以像這樣使用 INDEX 函數:

=索引(E2:E11, 6) >>> 返回 60

excel索引匹配01

√ 注:範圍 E2:E11 是列出期末考試的地方,而數字 6 找到考試成績 6第一個學生。

下面我們來做一個小測試。 對於公式 =指數(B2:E2,3),它會返回什麼值? ---是的,它會回來 中國中, 3 給定範圍內的值。

現在我們應該知道 INDEX 函數可以完美地處理水平或垂直範圍。 但是如果我們需要它返回一個包含多個行和列的更大範圍內的值怎麼辦? 那麼,在這種情況下,我們應該同時應用行號和列號。 例如,要找出 艾米麗來自的國家 使用INDEX,我們可以在單元格中通過B8到E3定位行號為2,列號為11的值,如下所示:

=指數(B2:E11,8,3) >>> 返回 中國

excel索引匹配02

根據上面的例子, 關於 Excel 中的 INDEX 函數,您應該知道:

  • INDEX 函數可以處理垂直和水平範圍。
  • INDEX 函數不區分大小寫。
  • 在 INDEX 公式中,行號位於列號之前(如果您需要兩個數字)。

但是,對於一個非常大的多行多列的數據庫,我們應用精確的行號和列號的公式肯定不方便。 而這正是我們應該結合使用 MATCH 函數的時候。

現在,讓我們先了解一下 MATCH 函數的基礎知識。


Excel中MATCH函數的使用

Excel 中的 MATCH 函數返回一個數值,即給定範圍內特定項目的位置。 MATCH 函數的語法如下:

=MATCH(lookup_value, lookup_array, [match_type])
  • 查找數組 (必填)是指您希望 MATCH 搜索的單元格範圍。
  • 比賽類型 (可選的), 1, 0 or -1:
  • 1(默認),MATCH 將找到小於或等於 Lookup_Array中. 中的值 查找數組 必須按升序排列。
  • 0, MATCH 將找到第一個完全等於 Lookup_Array中. 中的值 查找數組 可以按任何順序排列。 (對於匹配類型設置為0的情況,可以使用通配符。)
  • -1, MATCH 將找到大於或等於 Lookup_Array中. 中的值 查找數組 必須按降序排列。

例如,要知道 Vera 在名單中的位置,您可以像這樣使用 MATCH 公式:

=MATCH("vera",C2:C11,0) >>> 返回 4

excel索引匹配03

√ 注意:MATCH 函數不區分大小寫。 結果“4”表示名稱“Vera”位於列表的第 4 位。 公式中的“0”是匹配類型,它將在查找數組中找到與查找值“Vera”完全相等的第一個值。

要知道 分數“96”在B2到E2行中的位置,您可以像這樣使用 MATCH:

=匹配(96,B2:E2,0,XNUMX) >>> 返回 4

excel索引匹配04

☞ 關於 Excel 中的 MATCH 函數,我們應該知道的事情:

  • MATCH 函數返回查找值在查找數組中的位置,而不是值本身。
  • MATCH 函數在重複的情況下返回第一個匹配項。
  • 就像 INDEX 函數一樣,MATCH 函數也可以處理垂直和水平範圍。
  • MATCH 也不區分大小寫。
  • 如果 MATCH 公式的查找值是文本形式,請將其括在引號中。

現在我們已經了解了Excel中INDEX和MATCH函數的基本用法,讓我們擼起袖子準備將這兩個函數結合起來。


如何同時使用 INDEX 和 MATCH 函數

在這一部分,我們將討論不同情況下使用 INDEX 和 MATCH 函數來滿足不同的需求。

組合 INDEX 和 MATCH 的示例

請參閱下面的示例以了解我們如何組合 INDEX 和 MATCH 函數:

例如,要知道 伊芙琳的期末考試成績,我們應該使用公式:

=INDEX(A2:D11,MATCH("evelyn",B2:B11,0),MATCH("final exam",A1:D1,0)) >>> 返回 90

excel索引匹配05

好吧,由於公式可能看起來很複雜,讓我們來看看它的每個部分。

excel索引匹配06

如上圖所示,大 INDEX 公式包含三個參數:

  • 排列: A2:D11 告訴 INDEX 通過從單元格返回匹配值 A2 至 D11.
  • 行數: 匹配(“伊芙琳”,B2:B11,0) 告訴 INDEX 值的確切行。
  • 關於MATCH公式,我們可以解釋為:以數值形式返回B2到B11單元格中第一個恰好等於“evelyn”的值的位置,即 5.
  • 列數: MATCH("期末考試",A1:D1,0) 告訴 INDEX 值的確切列。
  • 關於MATCH公式,我們可以解釋為:以數值形式返回A1到D1單元格中與“期末考試”完全相等的第一個值的位置,即 4.

所以,你可以看到這個大公式就像我們下面展示的一樣簡單:

=索引(A2:D11,5,4)

在示例中,我們使用了硬編碼值“evelyn”和“期末考試”。 然而,在如此大的公式中,我們不想要硬編碼的值,因為每次我們要搜索新的東西時,我們都必須更改它們。 在這種情況下,我們可以使用單元格引用使公式動態如下:

=索引(A2:D11,比賽(G2,B2:B11,0),比賽(F3,A1:D1,0))

excel索引匹配07


INDEX 和 MATCH 應用左查找

現在,假設您需要了解 Evelyn 的課程,我們如何使用 INDEX 和 MATCH 來知道答案? 如果你注意了,你應該注意到類列在名稱列的左側,這超出了另一個 Excel 強大的查找功能 VLOOKUP 的能力。

事實上,左查找能力恰好是INDEX和MATCH的組合優於VLOOKUP的方面之一。

要知道 伊芙琳的課堂,您需要做的就是將單元格 F3 中的值更改為“類”,並使用與上圖相同的公式,然後 INDEX 和 MATCH 函數將立即告訴您答案:

=INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0)) >>> 返回 A

excel索引匹配08

如果你有安裝領導 Kutools for Excel, 由我們團隊開發的專業 Excel 插件,您還可以使用其對指定值應用左查找 從右到左查找 只需點擊幾下即可。 要實現該功能,請訪問 庫工具 在你的 excel 標籤中,找到 公式 組,然後單擊 從右到左查找 在下拉列表中 超級查找. 你會看到一個這樣的彈出對話框:

excel索引匹配09

單擊此處了解使用 Kutools for Excel 應用左查找功能的具體步驟。


INDEX 和 MATCH 應用雙向查找

現在,您是否能夠使用動態查找值製作 INDEX 和 MATCH 組合公式來應用雙向查找? 讓我們練習在單元格 G3、G4 和 G5 中製作公式,如下所示:

excel索引匹配10

以下是答案:

單元格 G3: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F3,A1:D1,0))
單元格 G4: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F4,A1:D1,0))
單元格 G5: =INDEX(A2:D11,MATCH(G2,B2:B11,0),MATCH(F5,A1:D1,0))

√ 注意:應用公式後,您可以通過更改單元格 G2 中的名稱輕鬆獲取任何學生的信息。


INDEX 和 MATCH 應用區分大小寫的查找

從上面的例子我們知道INDEX和MATCH函數是不區分大小寫的。 但是,在您需要公式來區分大小寫字符的情況下,您可以添加 精確 函數到你的公式是這樣的:

=INDEX(return_range,MATCH(TRUE,EXACT("lookup_value1",range1),0),MATCH("lookup_value2",range2,0))
√ 注意:這是一個數組公式,需要輸入 Ctrl + Shift + Enter. 一對大括號將顯示在公式欄中。

例如,要知道 吉米的考試成績,使用這樣的函數:

excel索引匹配11

=INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),MATCH("final exam",A1:C1,0)) >>> 返回 86

或者您可以使用單元格引用:

=INDEX(A2:C11,MATCH(TRUE,EXACT(F2,A2:A11),0),MATCH(E3,A1:C1,0)) >>> 返回 86
注意:不要忘記輸入 Ctrl + Shift + Enter.


INDEX 和 MATCH 以應用具有多個條件的查找

在處理具有多個列和行標題的大型數據庫時,找到滿足多個條件的內容總是很棘手。 在這種情況下,請參閱以下公式以查找多個條件:

=INDEX(return_range,MATCH(1,(lookup_value1=range1)*(lookup_value2=range2)*(…),0))
注意:這是一個數組公式,需要您輸入 Ctrl + Shift + Enter. 一對大括號將顯示在公式欄中。

例如,要找到 來自印度的A班Coco的期末考試成績,公式如下:

excel索引匹配12

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0)) >>> 返回 88
√ 注意:不要忘記輸入 Ctrl + Shift + Enter.

那麼,如果你經常忘記使用怎麼辦 Ctrl + Shift + Enter 完成公式,使公式返回不正確的結果? 這裡我們有一個更複雜的公式,你可以用一個簡單的公式來完成 Enter 鍵:

=INDEX(return_range,MATCH(1,INDEX((lookup_value1=range1)*(lookup_value2=range2)*(…),0,1),0))

對於上面的相同示例,找到 來自印度的A班Coco的期末考試成績,只需要一個通常的公式 Enter 命中如下:

excel索引匹配13

=INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0)) >>> 返回 88

在這裡我們不會使用硬編碼值,因為在具有多個條件的情況下我們需要一個通用公式。 只有這樣,我們才能通過改變上例中 G2、G3、G4 單元格中的值,輕鬆得到我們想要的結果。

這款獨特的敏感免洗唇膜採用 Moisture WrapTM 技術和 Berry Mix ComplexTM 成分, Kutools for Excel ■ 多條件查找功能,您只需點擊幾下即可查找具有多個條件的特定值。 要實現該功能,請訪問 庫工具 在你的 excel 標籤中,找到 公式 組,然後單擊 多條件查找 在下拉列表中 超級查找. 然後您將看到一個彈出對話框,如下所示:

excel索引匹配14

單擊此處了解使用 Kutools for Excel 應用多條件查找功能的具體步驟。


INDEX 和 MATCH 在多列中應用查找

如果我們有一個 Excel 電子表格,其中不同的列共享一個標題,如下所示,我們如何使用 INDEX 和 MATCH 將每個學生的姓名與其班級匹配?

excel索引匹配15

在這裡,讓我向您展示如何使用我們的專業工具完成任務 Kutools for Excel。 隨著它 公式助手, 您可以按照以下步驟快速將學生與其班級進行匹配:

1. 選擇要應用該功能的目標單元格。

2。 在下面 庫工具 標籤,轉到 公式助手點擊此處成為Trail Hunter 公式助手 在下拉列表中。

excel索引匹配16

3。 選擇 從公式類型查找,然後點擊 索引並匹配多列.

excel索引匹配17

4. A. 點擊第一個 excel索引匹配圖標右側的按鈕 Lookup_col 選擇要從中返回值的單元格,即類名。 (您只能在此處選擇單列或單行。)
    灣點擊第二個 excel索引匹配圖標右側的按鈕 表_rng 選擇單元格以匹配所選中的值 Lookup_col,即學生的姓名。
    C。 點擊第三個 excel索引匹配圖標右側的按鈕 查找值 選擇要查找的單元格,即您要與其班級匹配的學生姓名。

excel索引匹配18

5. 單擊確定,您將看到目標單元格中顯示 Jimmy 的班級名稱。

excel索引匹配19

6. 現在您可以向下拖動填充手柄以填充其他學生的班級。

excel索引匹配20

點擊下載 Kutools for Excel 進行 30 天免費試用.



  • 超級公式欄 (輕鬆編輯多行文本和公式); 閱讀版式 (輕鬆讀取和編輯大量單元格); 粘貼到過濾範圍...
  • 合併單元格/行/列 和保存數據; 拆分單元格內容; 合併重複的行和總和/平均值...防止細胞重複; 比較範圍...
  • 選擇重複或唯一 行; 選擇空白行 (所有單元格都是空的); 超級查找和模糊查找 在許多工作簿中; 隨機選擇...
  • 確切的副本 多個單元格,無需更改公式參考; 自動創建參考 到多張紙; 插入項目符號,複選框等...
  • 收藏并快速插入公式,範圍,圖表和圖片; 加密單元 帶密碼 創建郵件列表 並發送電子郵件...
  • 提取文字,添加文本,按位置刪除, 刪除空間; 創建和打印分頁小計; 在單元格內容和註釋之間轉換...
  • 超級濾鏡 (將過濾方案保存並應用於其他工作表); 高級排序 按月/週/日,頻率及更多; 特殊過濾器 用粗體,斜體...
  • 結合工作簿和工作表; 根據關鍵列合併表; 將數據分割成多個工作表; 批量轉換xls,xlsx和PDF...
  • 數據透視表分組依據 週號,週幾等 顯示未鎖定的單元格 用不同的顏色 突出顯示具有公式/名稱的單元格...
kte選項卡201905
  • 在Word,Excel,PowerPoint中啟用選項卡式編輯和閱讀,發布者,Access,Visio和Project。
  • 在同一窗口的新選項卡中而不是在新窗口中打開並創建多個文檔。
  • 將您的工作效率提高 50%,每天為您減少數百次鼠標點擊!
officetab底部
Say something here...
symbols left.
You are guest
or post as a guest, but your post won't be published automatically.
Loading comment... The comment will be refreshed after 00:00.

Be the first to comment.