エクセルINDEX関数とMATCH関数を組み合わせて、条件に一致する値を抽出する方法はよく知られていますが、複数条件AND（かつ）の条件はどうでしょうか？ 複数条件ANDで検索できると、より複雑な値の抽出ができるようになります。ここでは、INDEX関数とMATCH関数を組み合わせて、AND（かつ）の複数条件で値を検索、抽出する方法を、いくつか紹介しています。INDEX関数とMATCH関数を使うと、２つのAND条件だけでなく、3つの条件でも抽出できます。VLOOKUPにはできない条件付きの検索、抽出が出来るようになります。複数列の条件でも、簡単に値を抽出できます。
目次
INDEXとMATCHを組み合わせてAND条件で抽出
配列数式で複数条件を設定します。
下の表を使って、説明していきましょう。
INDEX関数の配列に１列を指定
J2に、エリア「東京」、かつ 商品№「20002」、かつ 枝番「A205」の、３つの条件に該当する単価を求める式を入力しましょう。
MATCH関数の式は、複数の検査値と範囲を、それぞれ＆で結合しています。
J2 =INDEX(E2:E9,MATCH(G2&H2&I2,A2:A9&B2:B9&C2:C9,0),1)
この式では、エラー値「#VALUE」になります。
この式を、Shift+Ctrl+Enter で確定しましょう。
J2 ｛=INDEX(E2:E9,MATCH(G2&H2&I2,A2:A9&B2:B9&C2:C9,0),1)｝
式が{ }で囲まれます。
単価「500」が返されました！
こちらの記事もご覧ください。⇒エクセルVLOOKUP関数とMATCH関数で値を検索！VLOOKUPの弱点を克服
INDEX関数の配列にセルの範囲を指定
J3に、エリア「東京」、かつ 商品№「20002」、かつ 枝番「A205」に、該当する単価を求める式を入力しましょう。
INDEX関数の列番号は、「単価」を返す「５」を指定します。
J3 =INDEX(A2:E9,MATCH(G3&H3&I3,A2:A9&B2:B9&C2:C9,0),5)
この式を、Shift+Ctrl+Enter で確定しましょう。
J3 ｛=INDEX(A2:E9,MATCH(G3&H3&I3,A2:A9&B2:B9&C2:C9,0),5)｝
単価「500」が返されました！
INDEX関数でエラーになる場合IFERROR関数を使用
J4に、エリア「東京」、かつ 商品№「20002」、かつ 枝番「C200」に、該当する単価を求める式を入力しましょう。
J4 =INDEX(E2:E9,MATCH(G4&H4&I4,A2:A9&B2:B9&C2:C9,0),1)
この式を、Shift+Ctrl+Enter で確定しましょう。
J4 ｛=INDEX(E2:E9,MATCH(G4&H4&I4,A2:A9&B2:B9&C2:C9,0),1)｝
エリア「東京」、かつ 商品№「20002」、かつ 枝番「C200」の、条件をすべて満たす単価はないので、エラー値「#N/A」が返されます。
このような場合に、エラー値「#N/A」を表示させない、他の値を表示させる方法があります。
それにはIFERROR関数を使います。
J5に、IFERROR関数を使った式を入力しましょう。
J5 =IFERROR(INDEX(E2:E9,MATCH(G5&H5&I5,A2:A9&B2:B9&C2:C9,0),1),”エラー”)
この式を、Shift+Ctrl+Enter で確定しましょう。
J5 ｛=IFERROR(INDEX(E2:E9,MATCH(G5&H5&I5,A2:A9&B2:B9&C2:C9,0),1),”エラー”)｝
「エラー」が表示されます。
エクセルINDEX関数とMATCH関数を使用して、配列数式での複数条件、AND(かつ)で抽出する場合は、必ず式を、Shift+Ctrl+Enter で確定しましょう。
INDEX、SUMPRODUCT、ROWを組み合わせてAND条件で抽出
エクセルINDEX関数、SUMPRODUCT関数、ROW関数を組み合わせて、AND(かつ)の複数条件で値を取得します。
下の表を使って、説明していきましょう。
SUMPRODUCT関数の配列計算を使って、AND条件で抽出(その１)
＝SUMPRODUCT((配列の条件1)*(配列の条件2)*(配列の条件3)…)
I2に、商品№「20002」、かつ 枝番「A205」、かつ 単価「500」の、複数列の条件に該当する個数を求める式を入力しましょう。
SUMPRODUCT関数で条件式を*で繋ぎ、ROW関数で行番号を取得します。
I2 =INDEX(D1:D7,SUMPRODUCT((A2:A7=F2)*(B2:B7=G2)*(C2:C7=H2)*ROW(A2:A7)))
個数「85」を返します。
SUMPRODUCT関数の配列計算を使って、AND条件で抽出(その２）
=SUMPRODUCT((配列の条件1)*(配列の条件2)*(配列の条件3),配列1,配列2,配列3…)
I3に、商品№「20002」、かつ 枝番「B202」、かつ 単価「900」に、複数列の条件に該当する個数を求める式を入力しましょう。
I3 =INDEX(D1:D7,SUMPRODUCT((A2:A7=F3)*(B2:B7=G3)*(C2:C7=H3),ROW(A2:A7)))
個数「38」を返します。
SUMPRODUCT関数の配列計算を使って、INDEX関数、ROW関数を組み合わせて、AND(かつ)の複数条件で値を取得する場合、全ての条件に合致しなくても値を返してしまいます。
SUMPRODUCT関数の配列計算を使ってAND(かつ)の複数条件で値を取得する場合は、その値が必ず範囲の中にある時です。
全ての条件に一致しなくても値を返してしまうので注意しましょう。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って検索、抽出する方法
INDEX、SUMPRODUCT、ROW、MATCHで、AND条件で抽出
エクセルINDEX関数、SUMPRODUCT関数、ROW関数、MATCH関数を組み合わせて、AND(かつ)の複数条件で値を取得します。
下の表を使って、説明していきましょう。
K2に、商品№「20002」、かつ 枝番「A205」、かつ 型の中から「型B」に、該当する単価を求める式を入力しましょう。
K2 =INDEX(A1:F7,SUMPRODUCT((A2:A7=H2)*(B2:B7=I2)*ROW(A2:A7)),MATCH(J2,A1:F1,0))
単価「550」を返します。
SUMPRODUCT関数の条件式で一致する値がない場合
K3に、商品№「20002」、かつ 枝番「D205」かつ 、型の中から「型B」に、該当する単価を求める式を入力しましょう。
K3 =INDEX(A1:F7,SUMPRODUCT((A2:A7=H3)*(B2:B7=I3)*ROW(A2:A7)),MATCH(J3,A1:F1,0))
単価「550」を返しましたが、枝番「D205」はありません！
SUMPRODUCT関数の配列計算では、複数条件で全てが一致していなくても値を返しています。
こちらの記事もご覧ください。⇒エクセル関数INDEXとMATCHで複数条件で検索、抽出！
MATCH関数の条件式で一致する値がない場合
K4に、商品№「20002」かつ 、枝番「A205」かつ 、型の中から「型E」に、該当する単価を求める式を入力します。
K4 =INDEX(A1:F7,SUMPRODUCT((A2:A7=H4)*(B2:B7=I4)*ROW(A2:A7)),MATCH(J4,A1:F1,0))
商品№「20002」と枝番「A205」はありますが、「型E」に一致する値はありません。
エラー値「#N/A」が返されます。
MATCH関数の条件式で一致する値がない場合は、「エラー値」が返されます。
こちらの記事もご覧ください。⇒エクセルINDEXとMATCH関数でワイルドカードで検索
SUMPRODUCT関数とMATCH関数を組み合わせて、複数条件ANDで抽出する場合、SUMPRODUCT関数では全ての条件が一致していなくても値を返していますが、MATCH関数の条件式では一致する値がない時は、「エラー値」が返されます。
SUMPRODUCT関数とMATCH関数では返す値が違ってきます。
こちらの記事もご覧ください。⇒エクセルOFFSET関数の使い方 MATCH関数と組み合わせて可変するセル範囲に対応
INDEX関数
エクセルINDEX関数は、行番号と列番号が交差する位置にあるセルの値を返す関数です。エクセルINDEX関数には、配列形式とセル範囲形式の2つの使い方があります。
配列形式は、指定した範囲からある値を返します。セル範囲形式は指定した範囲からセルの参照を返します。
INDEX関数、配列形式の書式と引数
INDEX(配列, 行番号, [列番号])
| 配列 | セルの範囲を指定します。 |
| 行番号 | 値を返す行を数値で指定します。 |
| 列番号 | 値を返す列を数値で指定します。 |
配列： 配列が１行または１列の場合、行番号または列番号を省略できます。
行番号： 行番号を省略した場合は、必ず列番号を指定します。
列番号： 列番号を省略した場合は、必ず行番号を指定します。
INDEX関数、配列形式の使用例
G2に式を入力しましょう。
G2 =INDEX(B2:D5,3,3)
範囲を「B2:D5」で、上から３番目の行、左から３番目の列の値を返します。
結果は「850」です。
INDEX関数、セル範囲形式の書式と引数
INDEX(参照, 行番号, [列番号], [領域番号])
| 参照 | 1つまたは複数のセルの参照を指定します。 |
| 行番号 | セル参照を返す行位置を数値で指定します。 |
| 列番号 | セル参照を返す列位置を数値で指定します。 |
| 領域番号 | 任意です。行番号と列番号が交差する位置を返す参照の範囲を1つ選択します。最初の範囲は「１」、次は「２」というように番号で領域を指定します。 |
参照： 複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、１つの範囲と範囲の間をを半角のコンマ（,）で区切ります。各領域が１行または１列の場合、行番号または列番号はそれぞれ省略できます。
INDEX関数、セル配列形式の使用例
G2に式を入力しましょう。
G2 =INDEX((A2:D3,A5:D7),2,4,2)
範囲「A2:D3」と「A5:D7」で、領域番号は「2」です。２番目の領域「A5:D7」が選択されます。「A5:D7」の上から２番目の行、左から４番目の列の値を返します。
結果は「900」です。
MATCH関数
指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。
MATCH関数の書式と引数
MATCH(検査値, 検査範囲, [照合の型])
| 検査値 | 検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。 |
| 検査範囲 | 検索するセルの範囲を指定します。 |
| 照合の型 | -1、0、1の数値のいずれかを指定します。省略した場合は自動的に１が選択されます。 |
照合の型
「１」：検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。
「０」：検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。
「-1」：検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。
MATCH関数の使用例
G2に式を入力しましょう。
G2 =MATCH(10025,A2:A6,0)
「A2:A6」の範囲内で「10025」の値を探します。「10025」は「A2:A6」の範囲内で２番目なので「２」の値を返します。
結果は「２」です。
ROW関数
指定した配列の行番号を返します。
ROW関数の書式と引数
| 範囲 | 行番号を調べたいセルまたはセル範囲を指定します。省略した場合、ROW関数が入力されているセルの行番号が返されます。範囲には複数の範囲を指定することはできません。 |
ROW関数の使用例
A2に式を入力しましょう。
A2 =ROW()-1
この式を「A3:A6」にコピーすると、A2:A6に連続した番号が表示されます。
ROW関数の範囲を省略した場合、ROWが入力されているセルの行番号が返されます。
A2に「=ROW()」または「=ROW(A2)」で求めた行番号「２」から１を引いて１を表示させ、その式を下にコピーすることで、並び替えても順番が変わらない番号を振ることができます。
SUMPRODUCT関数
引数として指定した配列の積を計算し、次にその和を返します。
SUMPRODUCT関数の書式と引数
SUMPRODUCT(配列 1, [配列 2], [配列 3], …)
| 配列 1 | 計算の対象となる要素を含む最初の配列引数を指定します。 |
| [配列 2], [配列 3], …. | 省略可能です。計算の対象となる要素を含む配列引数を指定します。 |
SUMPRODUCT関数で合計を求める使用例
A7に、商品№「A2:A5」の合計金額を求める式を入力しましょう。
A7 =SUMPRODUCT(B2:B5,C2:C5)
合計金額「2,100,000」になります。
この式を解説すると
A7 ＝B2*C2+B3*C3+B4*C4+B5*C5
上の式のようになり合計金額「2,100,000」になります。
SUMPRODUCT関数で数を求める使用例
G2に、エリア「東京」かつ 商品№「12005」の数を求める式を入力しましょう。
G2 =SUMPRODUCT((A2:A9=”東京”)*(B2:B9=12005))
結果は、「３」です。
SUMPRODUCTは配列の計算ができる関数です。条件と条件を「*」で繋いでAND条件の件数を求めることができます。「東京」かつ「12005」は３つあるので３を返します。
こちらの記事もご覧ください。⇒エクセルSUMPRODUCT関数で複数条件で抽出した数値を合計する
IFERROR関数
数式がエラーの場合、指定した値を返します。それ以外は数式を返します。
IFERROR関数の書式と引数
IFERROR(値, エラーの場合の値)
| 値 | エラーかどうかチェックする値を指定します。 |
| エラーの場合の値 | 数式がエラーと判断された場合に返す値を指定します。 |
IFERROR関数の使用例
D2に、「B2*C2」がエラーの場合「エラー」を返す式を入力しましょう。
D2 =IFERROR(B2*C2,”エラー”)
結果はエラーにはなりません。「B2*C2」の数式の結果「600,000」を返します。
D3に、「B3*C3」がエラーの場合「エラー」を返す式を入力します。
D3 =IFERROR(B3*C3,”エラー”)
結果は、エラー値「 #VALUE!」です。エラー値の場合に指定した値「エラー」を返します。
INDEXとMATCHを組み合わせて条件ANDの抽出する時の注意
配列数式を使って、エクセルINDEX関数とMATCH関数の組み合わせで、複数条件AND（かつ）で値を抽出する場合、条件に一致する値がなかったらエラー値が返されます。それに対し、SUMPRODUCT関数を使用してAND(かつ)の複数条件で値を取得する場合、全ての条件に合致しなくても値を返してしまいます。
エクセルSUMPRODUCT関数は、配列計算を使ってAND（かつ）での複数条件を指定できる便利な関数ですが、多くのデータを処理する場合は、配列数式を使って全ての複数条件に一致した値を抽出をすることをお勧めします。
こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使ってOR（または）の複数条件で値を取得する
