リストに対してセル内の最初に一致する値を取得する

キーワードのリストがある場合、特定のセルに含まれる複数の値の中から最初に出現するキーワードを取得するには、INDEX 関数とMATCH 関数に加え、AGGREGATE 関数およびSEARCH 関数を組み合わせた数式を使用する必要があります。

リスト内でセルの値と最初に一致するものを取得するには？

上記の表のように、キーワードリストに対してセル内で最初に一致したキーワードを取得するには、完全一致ではなく部分一致を行う必要があります。そのためには、SEARCH 関数でセル内に出現する各キーワードの位置を数値として求め、その結果を AGGREGATE 関数に渡します。その後、AGGREGATE 関数のfunction_num を15 に、ref2 引数を1 に設定することで最小値を取得します。次に 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+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),1),SEARCH($E$5:$E$7,B5),0))

• SEARCH($E$5:$E$7,B5):SEARCH 関数は、範囲 $E$5:$E$7 内の各キーワードを検索し、見つかった場合はその位置を数値で返します。見つからない場合は#VALUE!エラーを返します。結果は次のような配列になります：{15;11;#VALUE!}。
• AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1)=AGGREGATE(15,6,){15;11;#VALUE!},1): 関数番号が15、オプションが6に設定されたAGGREGATE 関数は、第2 引数（ref2）で指定された配列からエラー値を無視して最小値を返します。そのため、このスニペットは11を返します。
• MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)=MATCH()11,{15;11;#VALUE!},0):MATCH 関数のmatch_type 引数が0のため、完全一致が実行され、配列 {15;11;#VALUE!} 内における値 11の位置が返されます。したがって、この関数は2を返します。
• INDEX($E$5:$E$7,)MATCH()AGGREGATE(15,6,)SEARCH($E$5:$E$7,B5),1),SEARCH($E$5:$E$7,B5)) = INDEX($E$5:$E$7,2):その後、INDEX 関数は範囲 $E$5:$E$7の2 番目の値を返します。これはbbbです。

備考

• セル内にキーワードが存在しない場合、#NUM!エラーが表示されます。
• この数式は大文字と小文字を区別しません。大文字と小文字を区別する検索を行うには、SEARCH 関数をFIND 関数に置き換えてください。

関連関数

関連数式