複数の条件で最も近い一致値を検索

場合によっては、複数の条件に基づいて最も近いまたは近似の一致値を検索する必要があるかもしれません。INDEX関数、MATCH関数、IF関数を組み合わせることで、Excelですばやくこれを実行できます。

複数の条件で最も近い一致値を検索するにはどうすればよいですか？

以下のスクリーンショットに示すように、「専攻がコンピュータ」で「勤務経験が15年」という2つの条件に基づいて適切な人を見つける必要があります。

注意: これが正しく機能するためには、重複する専攻がある場合、それらの重複する専攻の勤務経験は昇順で並べ替えておく必要があります。

1. 結果を出力する空白のセルを選択し、以下の数式をコピーして貼り付け、Ctrl + Shift + Enterキーを押して結果を得ます。

=INDEX(D3:D8,MATCH(G5,IF(B3:B8=G4,C3:C8),1))

注: この数式では、

• D3:D8は、探している結果を含む列範囲です。
• G5には、検索値を基にする2番目の条件（経験年数15）が含まれています。
• G4には、検索値を基にする最初の条件（コンピュータ）が含まれています。
• B3:B8は、最初の条件に一致するセルの範囲です。
• C3:C8は、2番目の条件に一致するセルの範囲です。
• 数字の1は近似一致を意味し、正確な値が見つからない場合、検索値よりも小さい最大の値を見つけます。
• この数式は、Ctrl + Shift + Enterキーを使用して配列数式として入力する必要があります。

この数式の仕組み

この数式はいくつかの要素に分解することができます：

• IF(B3:B8=G4,C3:C8): IF関数はここでは{9;13;FALSE;FALSE;FALSE;FALSE}という結果を返します。これは、B3:B8の値をG4の値と照合した結果です。一致する場合は対応する値を返し、そうでない場合はFALSEを返します。ここでは2つの一致と4つの不一致が見つかりました。
• 配列数式 =MATCH(G5,{9;13;FALSE;FALSE;FALSE;FALSE},1): MATCH関数は、G5にある値15がC3:C8の範囲内でどの位置にあるかを探します。15が見つからないため、次に小さい値である13に一致します。したがって、ここで得られる結果は2です。
• そして、=INDEX(D3:D8,2): INDEX関数は、D3:D8の範囲内の2番目のセルの値を返します。したがって、最終的な結果はAmyです。

関連する関数

関連記事