從列表中獲取單元格中的第一個匹配值

假設您有一個關鍵字列表，要從特定單元格中獲取出現的第一個關鍵字，而該單元格包含其他多個值，則需要使用 INDEX 和 MATCH 公式，並借助 AGGREGATE 和 SEARCH 函數來實現。

如何從列表中獲取單元格中的第一個匹配值？

要在單元格中根據如上表所示的關鍵字列表獲取第一個匹配的關鍵字，這意味著我們需要執行包含匹配而不是精確匹配。為此，您可以使用 SEARCH 函數將關鍵字在單元格中出現的位置以數字形式輸入到 AGGREGATE 函數中。然後，通過將 AGGREGATE 的 function_num 設置為 15，ref2 參數設置為 1，AGGREGATE 將獲得最小的數字。然後使用 MATCH 定位第一個最小值，並將位置號傳遞給 INDEX 以獲取該位置的值。

通用語法

=INDEX(keyword_rng,MATCH(AGGREGATE(15,6,SEARCH(keyword_rng,lookup_cell),1),SEARCH(keyword_rng,lookup_cell),0))

√ 注意：這是一個陣列公式，需要您使用 Ctrl + Shift + Enter 進行輸入。

要獲取單元格 B5 中相對於關鍵字列出現的第一個匹配關鍵字，請在單元格 C5 中複製或輸入以下公式，然後按 Ctrl + Shift + Enter 獲取結果：

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5),0))

√ 注意：上面的美元符號 ($) 表示絕對引用，這意味著當您將公式移動或複製到其他單元格時，公式中的 keyword_rng 不會改變。但是，由於希望 lookup_cell 是動態的，因此沒有添加美元符號。輸入公式後，向下拖動填充柄以將公式應用於下方的單元格。

公式的解釋

=INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5): SEARCH 函數返回範圍 $E$5:$E$7 中每個關鍵字在單元格 B5 中找到時的位置（以數字表示），如果未找到則返回 #VALUE! 錯誤。結果將是類似這樣的陣列：{15;11;#VALUE!}。
• AGGREGATE(15,6,AGGREGATE(15,6,SEARCH($E$5:$E$7,B5),1),1) = AGGREGATE(15,6,AGGREGATE(15,6,{15;11;#VALUE!},1),1): 使用 function_num 為 15 和 選項為 6 的 AGGREGATE 函數將根據 ref2 參數 1 返回陣列中的最小值，同時忽略錯誤值。因此，該片段將返回 11。
• MATCH(MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0),0) = MATCH(MATCH(11,,{15;11;#VALUE!},0),0): match_type 為 0 強制 MATCH 函數執行精確匹配，並返回 11 在陣列 {15;11;#VALUE!} 中的位置。因此，函數將返回 2。
• INDEX($E$5:$E$7,MATCH(AGGREGATE(15,6,SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)SEARCH($E$5:$E$7,B5),0)) = INDEX($E$5:$E$7,2): INDEX 函數然後返回範圍 $E$5:$E$7 中的第 2 個值，即 bbb。

備註

• 如果單元格中沒有關鍵字，將返回 #NUM! 錯誤。
• 該公式不區分大小寫。若要執行區分大小寫的匹配，可以簡單地將 SEARCH 函數替換為 FIND。

相關函數

相關公式