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

特定のキーワードリストがあり、そのリストの中から特定のセルに最初に出現したキーワードを取得したい場合、そのセルには他のいくつかの値も含まれているため、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を押してください：

=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内の各キーワードが見つかった場合、その位置を数値で返し、見つからない場合は#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関数は完全一致を行い、配列{15;11;#VALUE!}内の11の位置を返します。したがって、この関数は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関数に置き換えることができます。

関連する関数

関連する数式