Excelでリストの中から、3つの条件を満たす値を求める方法です。
先日、職場でちょっと難しい課題にぶつかってしまいました。
リストの中から3つの条件を満たす値を求めるには？ VLOOKUP関数や、MATCH関数とINDEX関数を組み合わせて・・・といろいろ頭を悩ませました。
リストは毎回可変するし、縦と横だけならいいのですが、A列とB列で一致するものを求めて行を確定しなければなりません。
1行目の項目も毎回変わるので求める列をMATCH関数で検索するしかありません。
SUMPRODUCT関数・INDEX関数・MATCH関数を使って求める
こういう時は、SUMPRODUCT関数を使うと便利です。
もちろん、INDEX関数とMATCH関数も使います。
セル［B16］には、下のように数式を入力しました。
=INDEX(A1:G11,SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11)),MATCH(B15,A1:G1,0))
INDEX関数の関数の引数ダイアログボックスです。
［配列］にはリストの範囲ですね。
［行番号］はSUMPRODUCT関数とROW関数で求めます。［列番号］はMATCH関数で求めます。
INDEX関数の［行番号］は、以下のようにして求めています。
SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11))
このSUMPRODUCT関数って面白いです。
私は知らなくてビックリしました。
SUMPRODUCT 関数 - Office サポート（Microsoft）
参考INDEX関数とMATCH関数のみで求めたほうが簡単だと思います。
列を追加してMATCH関数とINDEX関数で求める
A列とB列をくっつけた値をC列に表示すれば、MATCH関数とINDEX関数を組み合わせて求めることもできますね。
2つのセルの値を結合して表示する方法は、以下の記事で解説しています。
-
複数の文字列を結合してひとつの文字列にするには
Excelで1つのセルに他のセルの文字列を結合した文字列を表示する方法を紹介します。 5つの方法を紹介しますので、使用環境に合った方法で操作 ...
MATCH関数とINDEX関数については、以下のページで解説しています。
-
行と列が交差するセルの値を求める（INDEX関数とMATCH・XMATCH関数）
Excelで下のようなマトリックス表があって、このマトリックス表の行と列が交差する値を、関数を使って自動的に求める方法です。 早見表などでも ...
セル［B16］に以下の数式を入力します。求める値は同じになります。
=INDEX(A1:H11,MATCH(B13&B14,C1:C11,0),MATCH(B15,A1:H1,0))
XMATCH関数を使用
Microsoft 365のExcelとExcel2021で使用できる［XMATCH］関数を使用するなら、以下のようになります。
以下の画像は、Excel2021です。
XMATCH 関数 - Microsoft サポート（Microsoft）
=INDEX(A1:H11,XMATCH(B13&B14,C1:C11),XMATCH(B15,A1:H1))
参考MATCH関数とXMATCH関数の違いです。
=MATCH(検査値,検査範囲,照合の種類)
MATCH関数の［照合の種類］は、検査値と完全一致を求めるのであれば［0］を指定します。
=XMATCH(検査値,検査範囲,［一致モード］,［検索モード］)
XMATCH関数は、［一致モード］の既定が完全一致なので省略できます。また、［検索モード］も既定値が先頭から末尾への検索なので、これも省略できます。
MATCH関数とINDEX関数を組み合わせて求める
A列とB列をくっつけた値をC列に表示しなくても、MATCH関数とINDEX関数を組み合わせて求めることができます！
下の式で求めることができますね！いろいろ勉強させていただきました。
=INDEX(A1:G11,MATCH(B13&B14,INDEX(A1:A11&B1:B11,),),MATCH(B15,A1:G1,0))
INDEX関数の［行番号］は、以下のようにして求めています。これで求めることができます。
MATCH(B13&B14,INDEX(A1:A11&B1:B11,),)
XMATCH関数を使用すると、以下のようになります。
=INDEX(A1:G11,XMATCH(B13&B14,INDEX(A1:A11&B1:B11,),),XMATCH(B15,A1:G1))
データ範囲が可変したり、検索する文字列がリストにない場合など、その時の用途によって使い分けてみてください。
エラー表示の対処には、IFERROR関数が便利です。
-
エラーの場合の値を指定できるIFERROR関数
Excel2007からの関数ですが、エラー値を表示せずにエラーの場合の値を指定することができる関数があります。 下の表は、E列で［販売単価］ ...
-
VLOOKUP関数のエラー回避はIFERROR関数で
Excelでデータを参照する場合、VLOOKUP関数を使います。 下の左の表、セル［C2］からセル［C7］には、VLOOKUP関数が入力され ...
リスト範囲は、名前を付けておくと便利です。
また、リスト範囲が可変する場合は名前定義の参照範囲に数式を入力して範囲が変化しても対応できるようにしておくと便利です。