Excel表格中怎麼使用INDEX函式和 MATCH函式

2023-08-21 19:18:48

INDEX 和 MATCH 是 Excel 中比較常用的兩個查詢函式，堪稱資料查詢的好兄弟，這兩個函式組合，能夠完成 VLOOKUP 函式和 HLOOKUP 函式的全部查詢功能。

使用 MATCH 函式精確位置

想知道某個資料是一列或一行資料中的第幾個，則可以使用查詢函式中的 MATCH 函式，因為它可以輕鬆確定某個資料在其所屬行或列中的位置。

在 MATCH 函式中，第 3 引數“[match_type]”可以為 1、0 或 -1。

使用 MATCH 函式查詢資料時，將會在資料區域中按從左到右，或者從上到下的順序查詢指定的資料，當找到匹配資料後，再返回資料所在的位置。

例如，下圖所示表格中有兩列資料，現在需要找出 A 列資料是否與 C 列中的資料重複，重複的資料在 C 列中處於什麼位置，此時就可以使用 MATCH 函式輕鬆解決。

如果要判斷下表中A2單元格中的資料是否與C列重複，則可在E單元格中輸入公式“=MATCH(A2,$C$2 : $C$10,0)”，按【Enter】鍵即可。

向下填充公式，可查詢到其他員工編號是否重複。

如果希望返回的結果為指定的資料，則可結合 IF 函式和 ISNA 函式。

例如，將上圖返回的數字轉換為“是”，將 #N/A 錯誤值轉為“否”，只需要在 E2 單元格中輸入公式“=IF(ISNA(MATCH(A2,$C$2 : $C$10,0))," 否 "," 是 ")”，然後向下填充公式即可。

MATCH 函式很少單獨使用，一般都會與其他函式配合使用。

在使用 MATCH 函式時，還需要注意以下幾點。

（1）MATCH 函式返回匹配值在 lookup_array 中的位置，而非其值本身。例如，MATCH(1,{2,3,1},0) 返回 3，即“1”在陣列 {2,3,1} 中的相對位置。

（2）匹配文字值時，MATCH 函式不區分大小寫字母。

（3）如果 match_type 為 0 且 lookup_value 為文字字串，則可在 lookup_value 引數中使用萬用字元問號（?）和星號（*）。問號匹配任意單個字元，星號匹配任意一串字元。如果要查詢實際的問號或星號，那麼就在字元前鍵入波形符（~）。

INDEX函式用於返回表或區域中的值或對值的引用，它有陣列和引用兩種形式。

1 陣列形式

INDEX 函式的陣列形式有 3 個引數，分別用來指定儲存資料的區域、提取第幾行的資料和提取第幾列的資料。

在下圖所示表格的 H2 單元格中輸入公式“=INDEX(A2 : F11,5,4)”，按【Enter】鍵即可。

當第 1 引數的資料區域只有 1 行或 1列時，可以只為 INDEX 函式設定兩個引數，這兩個引數分別用於指定資料區域和返回區域在該區域中的位置。

例如，在 A列中顯示了“銷售 1 部”的前十名銷售人員，如果要查詢從上到下第5個銷售人員，則需要在C2單元格中輸入公式“=INDEX(A2 : A11,5)”，按【Enter】鍵即可。

如果需要提取資料區域中的某行或某列資料，則需要將第 2 引數“row_num”或第 3 引數“[column_num]”設定為 0（零）。

例如，在“銷售排名”表格中提取銷售 3 部前十名的銷售人員的姓名，就需要先選擇存放結果的 H2 : H11 單元格區域，輸入公式“=INDEX(A2 : F11,0,4)”，按【Ctrl Shift Enter】組合鍵，將公式轉換為陣列公式即可。

2 引用形式

INDEX 函式的引用形式有 4 個引數，第 1 引數可以由多個單元格區域組成，第 2 引數和第 3引數與陣列形式中的第 2 引數和第 3 引數相同，第 4 引數用來指定需要返回第幾個區域中的單元格。

INDEX 函式的引用形式與陣列形式的區別在於，引用形式可以有多個選擇區域，而且可以指定返回第幾個選擇區域。

當然，只有一個區域時，兩種形式沒有區別。

在資料查詢中，MATCH 函式單獨使用的情況並不多，大多數情況下都是與其他函式搭配使用，特別是與 INDEX 函式的搭配，被稱為是一對黃金組合，能夠實現大多數情況下的資料查詢，大大提高資料查詢效率。

例如，下圖為某公司員工資訊表，要查詢某員工的聯絡電話，除可以使用前面講解的 LOOKUP 函式外，還可以使用 INDEX 和 MATCH 函式來實現。

如果要查詢“王雪佳”的聯絡電話，則只需要在 I2 單元格中輸入“王雪佳”，再在 J2 單元格中輸入公式“=INDEX(G:G,MATCH(I2,B:B,0))”，按【Enter】鍵即可。

使用 INDEX 和 MATCH 函式組合查詢非常靈活，不受原始資料列順序的影響。

例如，要根據電話號碼查詢對應的員工姓名，在下圖所示表格的 I2 單元格中輸入需要查詢的電話號碼，再在 J2 單元格中輸入公式“=INDEX(B:B,MATCH(I2,G:G,0))”，按【Enter】鍵即可查詢出已知聯絡電話對應的員工姓名。