Note: The other languages of the website are Google-translated. Back to English

跨多列的 INDEX 和 MATCH

要通過跨多列匹配來查找值,基於 INDEXMATCH 包含的功能 MMULT, 移調 COLUMN 會幫你一個忙。

索引匹配多列 1

如何通過跨多列匹配來查找值?

填寫 每個學生對應的班級 如上表所示,其中 信息跨多列列出,您可以先使用 MMULT、TRANSPOSE 和 COLUMN 函數的技巧來生成矩陣數組。 然後 MATCH 函數將為您提供查找值的位置,該位置將提供給 INDEX 以檢索您在數組中查找的值。

通用語法

=INDEX(return_range,(MATCH(1,MMULT(--(lookup_array=lookup_value),TRANSPOSE(COLUMN(lookup_array)^0)),0)))

√ 注意:這是一個數組公式,需要輸入 按Ctrl + 轉移 + Enter.

  • 返回範圍: 您希望公式從中返回類信息的範圍。 這裡指的是班級範圍。
  • 查找值: 公式用於查找其對應類信息的值。 這裡指給定的名字。
  • 查找數組: 單元格的範圍 Lookup_Array中 被列出; 與值進行比較的範圍 Lookup_Array中. 這裡指的是名稱範圍。
  • 匹配類型 0: 強制 MATCH 找到第一個完全等於 Lookup_Array中.

為了找到 吉米班, 請複製或在H5單元格中輸入以下公式,然後按 按Ctrl + 轉移 + Enter 得到結果:

=索引($ B $ 5:$ B $ 7,(匹配(1,MMULT(--(5 加元:7 美元=G5),轉置(列(5 加元:7 美元)^0)),0)))

√ 注意:上面的美元符號($)表示絕對引用,這意味著當您將公式移動或複製到其他單元格時,公式中的名稱和類別範圍不會改變。 請注意,不應向表示查找值的單元格引用添加美元符號,因為在將其複製到其他單元格時,您希望它是相對的。 輸入公式後,向下拖動填充柄以將公式應用於以下單元格。

索引匹配多列 2

公式說明

=INDEX($B$5:$B$7,(MATCH(1,MMULT(--($C$5:$E$7=G5),TRANSPOSE(COLUMN($C$5:$E$7)^0)),0)))

  • --($C$5:$E$7=G5): 此段檢查範圍內的每個值 5 加元:7 美元 如果它們等於單元格 G5 中的值,並生成一個 TRUE 和 FALSE 數組,如下所示:
    {真,假,假;假,假,假;假,假,假}.
    然後雙重否定會將 TRUE 和 FALSE 轉換為 1 和 0,以產生如下所示的數組:
    {1,0,0; 0,0,0; 0,0,0}.
  • 列($C$5:$E$7): COLUMN 函數返回範圍的列號 5 加元:7 美元 在這樣的數組中: 3,4,5 {}.
  • 移調(列($C$5:$E$7)^0) = 移調(3,4,5 {}^0): 將次冪提升到 0 後,數組 {3,4,5} 中的所有數字都將轉換為 1:{1,1,1}。 TRANSPOSE 函數然後將列數組轉換為行數組,如下所示: {1; 1; 1}.
  • 多(--($C$5:$E$7=G5),移調(列($C$5:$E$7)^0)) = 多({1,0,0; 0,0,0; 0,0,0},{1; 1; 1}): MMULT 函數返回兩個數組的矩陣乘積,如下所示: {1; 0; 0}.
  • 匹配(1,多(--($C$5:$E$7=G5),移調(列($C$5:$E$7)^0)),0) = 匹配(1,{1; 0; 0},0): 匹配類型 0 強制 MATCH 函數返回第一個匹配項的位置 1 在數組中 {1; 0; 0},這是 1.
  • 指數($ B $ 5:$ B $ 7,(匹配(1,多(--($C$5:$E$7=G5),移調(列($C$5:$E$7)^0)),0))) = 指數($ B $ 5:$ B $ 7,1): INDEX 函數返回 1類範圍內的 st 值 $ B $ 5:$ B $ 7,這是 A.

要通過跨多列匹配輕鬆查找值,您還可以使用我們的專業 Excel 加載項 Kutools 為 Excel. 請參閱此處的說明以完成任務.


相關功能

Excel INDEX函數

Excel INDEX 函數根據范圍或數組中的給定位置返回顯示值。

Excel MATCH功能

Excel MATCH 函數在單元格範圍內搜索特定值,並返回該值的相對位置。

Excel MMULT 函數

Excel MMULT 函數返回兩個數組的矩陣乘積。 數組結果的行數與array1 相同,列數與array2 相同。

Excel TRANSPOSE 函數

Excel TRANSPOSE 函數旋轉區域或數組的方向。 例如,它可以將水平排列的表格旋轉到垂直排列的表格,反之亦然。

Excel COLUMN函數

COLUMN 函數返回公式出現的列數或返回給定引用的列數。 例如,公式 =COLUMN(BD) 返回 56。


相關公式

使用 INDEX 和 MATCH 進行多條件查找

在 Excel 電子表格中處理具有多個列和行標題的大型數據庫時,找到滿足多個條件的內容總是很棘手。 在這種情況下,您可以使用帶有 INDEX 和 MATCH 函數的數組公式。

使用 INDEX 和 MATCH 進行雙向查找

要在 Excel 中跨行和列搜索某些內容,或者我們說在特定行和列的交叉處查找值,我們可以使用 INDEX 和 MATCH 函數的幫助。

查找具有多個條件的最匹配值

在某些情況下,您可能需要根據多個條件查找最接近或近似的匹配值。 結合 INDEX、MATCH 和 IF 函數,您可以在 Excel 中快速完成。


最佳辦公效率工具

Kutools for Excel-幫助您從人群中脫穎而出

您想快速,完美地完成日常工作嗎? Kutools for Excel具有300個強大的高級功能(合併工作簿,按顏色求和,拆分單元格內容,轉換日期等),並為您節省80%的時間。

  • 專為1500個工作方案而設計,可幫助您解決80%的Excel問題。
  • 每天減少數千次鍵盤和鼠標的點擊,減輕您疲倦的眼睛和手的疲勞。
  • 在3分鐘內成為Excel專家。 不再需要記住任何痛苦的公式和VBA代碼。
  • 30天無限制免費試用。 60天退款保證。 免費升級和支持2年。
Excel功能區(已安裝Kutools for Excel)

Office選項卡-在Microsoft Office(包括Excel)中啟用選項卡式閱讀和編輯

  • 一秒鐘即可在數十個打開的文檔之間切換!
  • 每天為您減少數百次鼠標單擊,告別鼠標手。
  • 查看和編輯多個文檔時,可將您的工作效率提高50%。
  • 像Chrome,Firefox和新的Internet Explorer一樣,為Office(包括Excel)帶來高效選項卡。
Excel的屏幕截圖(已安裝Office選項卡)
按評論排序
留言 (0)
還沒有評分。 成為第一位評論!
這裡還沒有評論
留下你的意見
以訪客身份發帖
×
評價此帖子:
0   字符
推薦地點