3つの条件を満たす値を求めるには

投稿日：2017年1月7日更新日：2021年1月8日

Excelでリストの中から、3つの条件を満たす値を求める方法です。

先日、職場でちょっと難しい課題にぶつかってしまいました。リストの中から3つの条件を満たす値を求めるには？ VLOOKUP関数や、MATCH関数とINDEX関数を組み合わせて・・・といろいろ頭を悩ませました。

リストは毎回可変するし、縦と横だけならいいのですが、A列とB列で一致するものを求めて行を確定しなければなりません。

一行目の項目も毎回変わるので求める列をMATCH関数で検索するしかありません。

SUMPRODUCT関数・INDEX関数・MATCH関数を使って求める

こういう時は、SUMPRODUCT関数を使うと便利です。

もちろん、INDEX関数とMATCH関数も使います。

セルB16には、下のように数式を入力しました。

=INDEX(A1:G11,SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11)),MATCH(B15,1:1,0))

INDEX関数の関数の引数ダイアログボックスです。

［配列］にはリストの範囲ですね。

［行番号］はSUMPRODUCT関数とROW関数で求めます。［列番号］はMATCH関数で求めます。

行番号は、以下のようにして求めています。

SUMPRODUCT((A2:A11=B13)*(B2:B11=B14)*ROW(A2:A11))

このSUMPRODUCT関数って面白いです。

私は知らなくてビックリしました。

A列とB列をくっつけた値をC列に表示すれば、MATCH関数とINDEX関数を組み合わせて求めることもできますね。

2つのセルの値を結合して表示する方法は、以下の記事で解説しています。

: 複数の文字列を結合してひとつの文字列にするには

Excelで1つのセルに他のセルの文字列を結合した文字列を表示する方法を紹介します。 5つの方法を紹介しますので、使用環境に合った方法で操作 ...

続きを見る

MATCH関数とINDEX関数については、以下のページで解説しています。

: 行と列が交差するセルの値を求める（INDEX関数とMATCH・XMATCH関数）

Excelで下のようなマトリックス表があって、このマトリックス表の行と列が交差する値を、関数を使って自動的に求める方法です。早見表などでも ...

続きを見る

求める値は同じになります。

A列とB列をくっつけた値をC列に表示しなくても、MATCH関数とINDEX関数を組み合わせて求めることができます！

下の式で求めることができますね！いろいろ勉強させていただきました。

=INDEX(A2:G11,MATCH(B13&B14,INDEX(A2:A11&B2:B11,),),MATCH(B15,1:1,0))

データ範囲が可変したり、検索する文字列がリストにない場合など、その時の用途によって使い分けてみてください。

エラー表示の対処には、IFERROR関数が便利です。

: エラーの場合の値を指定できるIFERROR関数

Excel2007からの関数ですが、エラー値を表示せずにエラーの場合の値を指定することができる関数があります。下の表は、E列で［販売単価］ ...

続きを見る

: VLOOKUP関数のエラー回避はIFERROR関数で

Excelでデータを参照する場合、VLOOKUP関数を使います。下の左の表、セル［C2］からセル［C7］には、VLOOKUP関数が入力され ...

続きを見る

リスト範囲は、名前を付けておくと便利です。

また、リスト範囲が可変する場合は名前定義の参照範囲に数式を入力して範囲が変化しても対応できるようにしておくと便利です。