エクセルINDEX関数とMATCH関数で色々な複数条件で値を検索、抽出！

エクセルINDEX関数とMATCH関数を組み合わせて、複数条件に一致する値を検索、抽出する場合、方法は幾つかあります。ここでは、INDEX関数とMATCH関数を組み合わせて、複数条件に一致する値を抽出する方法を、色々紹介しています。ここでの方法をマスターすると、INDEX関数とMATCH関数を組み合わせて、複数条件AND（かつ）や、OR（または）で値を検索し、抽出できるようになります。複数列の条件でも、３つ条件でも、簡単に値を抽出できます。テクニックを取得して、より複雑な抽出ができるようになりましょう。

INDEX、MATCH、IFERRORを組み合わせてOR条件で抽出

エクセルINDEX関数、MATCH関数、IFERROR関数を組み合わせて、複数条件ORで値を抽出します。

商品№「A100022」、または「A221200」、または「B120001」である取扱店舗を抽出しましょう。

優先順位は「A221200」次に「B120001」最後に「A100022」とします。

E2に、式を入力しましょう。

E2　　=IFERROR(INDEX(B2:B7,MATCH(“A221200”,A2:A7,0),1),IFERROR(INDEX(B2:B7,MATCH(“B120001”,A2:A7,0),1),IFERROR(INDEX(A2,MATCH(“A100022″,A2:A7,0),1),”エラー”)))

「渋谷」が返されます。

この式は、一番欲しい商品「A221200」の、取扱店舗を最初に探します。

もし「A221200」がなかったら「B120001」を探し、その取扱店舗を返します。もし「B120001」がなかったら「A100022」を探し、その取扱店舗を返します。

この3つの商品№がなかったら、エラーになります。エラーになった場合は、IFERROR関数で指定した値「エラー」が表示されます。

INDEX、MATCH、IFERRORを組み合わせてAND条件で抽出

配列数式で、複数条件ANDで値を抽出します。

I2に、商品№「320001」かつ 、型番「A102」かつ、 ランク「A」の３つの条件で、該当する金額を求める式を入力しましょう。

I2　=INDEX(D2:D8,MATCH(F2&G2&H2,A2:A8&B2:B8&C2:C8,0),1))

複数条件に一致する値が無い場合、エラーになります。エラー値を表示させたくない場合は、IFERROR関数を使います。

I2　=IFERROR(INDEX(D2:D8,MATCH(F2&G2&H2,A2:A8&B2:B8&C2:C8,0),1),”エラー”)

この式では、エラー値「#VALUE」になります。

この式を、Shift+Ctrl+Enter で確定しましょう。

I2　｛=IFERROR(INDEX(D2:D8,MATCH(F2&G2&H2,A2:A8&B2:B8&C2:C8,0),1),”エラー”)｝

式が{ }で囲まれます。

金額「52,000」が返されました。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使って検索、抽出する方法

INDEX、SUMPRODUCT、ROWを組み合わせてAND条件で抽出

エクセルINDEX関数、SUMPRODUCT関数、ROW関数を組み合わせて、ANDの複数条件で値を抽出しましょう。

SUMPRODUCT関数、配列計算を使って複数条件で値を抽出する方法(その１)

＝SUMPRODUCT((配列の条件1)*(配列の条件2)*(配列の条件3)…)

L2に、商品№「20002」かつ、 型番「A102」かつ 、個数「525」の３つの条件で、該当する金額を求める式を入力しましょう。

L2　=INDEX(D1:D7,SUMPRODUCT((A2:A7=I2)*(B2:B7=J2)*(C2:C7=K2)*ROW(A2:A7)),1)

ISUMPRODUCT関数で複数条件を指定します。指定した条件式は*で繋ぎ、ROW関数で行番号を取得します。

金額「8,000」が返されます。

SUMPRODUCT関数、配列計算を使って複数条件で値を抽出する方法(その２)

=SUMPRODUCT((配列の条件1)*(配列の条件2)*(配列の条件3),配列1,配列2,配列3…)

L3に、商品№「20002」かつ 、型番「B201」かつ 、個数「235」、複数列の条件に該当する金額を求める式を入力しましょう。

L3　=INDEX(D1:D7,SUMPRODUCT((A2:A7=I3)*(B2:B7=J3)*(C2:C7=K3),ROW(A2:A7)),1)

金額「12,000」が返されます。

SUMPRODUCT関数で条件式を*で繋ぎ、ROW関数で行番号を取得します。

SUMPRODUCT関数の配列計算を使って、AND(かつ)の複数条件で値を取得する場合は、その値が必ず範囲の中にある時です。

SUMPRODUCT関数の配列計算を使って、AND(かつ)の複数条件で値を取得する場合、全ての条件に合致しなくても値を返してしまいます。

値が必ず範囲の中にない場合は、他の方法で抽出しましょう。

こちらの記事もご覧ください。⇒エクセルINDEX関数とMATCH関数を使ってOR（または）の複数条件で値を取得する

INDEX、SUMPRODUCT、ROW、MATCHを組み合わせてAND条件で抽出

エクセルINDEX関数、SUMPRODUCT関数、ROW関数、MATCH関数を組み合わせて、ANDの複数条件で値を抽出しましょう。

L2に、商品№「20002」かつ、 型番「B201」で、 ランクの中から「Cﾗﾝｸ」、複数列の条件に該当する「単価」を求める式を入力しましょう。

ランクに入力されているのは「単価」です。

L2　=INDEX(A1:G7,SUMPRODUCT((A2:A7=I2)*(B2:B7=J2)*ROW(A2:A7)),MATCH(K2,A1:G1,0))

SUMPRODUCT関数で条件式を*で繋ぎ、ROW関数で行番号を取得します。MATCH関数で、ランク「Cﾗﾝｸ」の列番号を取得します。

金額「250」を返します。

こちらの記事もご覧ください。⇒エクセル関数INDEXとMATCHで複数条件で検索、抽出！

SUMPRODUCT関数とMATCH関数とでは、一致する値がない場合の返す値が違います

SUMPRODUCT関数の条件式で、枝番「C101」一致する値がない場合の式、L3に入力しましょう。

L3　=INDEX(A1:G7,SUMPRODUCT((A2:A7=I3)*(B2:B7=”C101″)*ROW(A2:A7)),MATCH(K3,A1:G1,0))

「370」が返されました！

枝番「C101」に一致する値がなくても、値を返してしまいます！

MATCH関数の条件式で、「Dﾗﾝｸ」に一致する値がない場合の式を、L4に入力しましょう。

L4　=INDEX(A1:G7,SUMPRODUCT((A2:A7=I4)*(B2:B7=J4)*ROW(A2:A7)),MATCH(K4,A1:G1,0))

結果は「#N/A」です。

MATCH関数の条件式では、一致する値がなかった場合「エラー値」を返します。

こちらの記事もご覧ください。⇒エクセルINDEXとMATCH関数でワイルドカードで検索

SUMPRODUCT関数とMATCH関数とでは、一致する値がない場合、返す値が違ってきます。SUMPRODUCT関数は、完全に一致する値がなくても値を返してしまします。

MATCH関数は完全に一致する値がなかった場合、「エラー値」を返します。SUMPRODUCT関数の配列計算を使っての複数条件(AND)では、完全に一致する値が検索する範囲にある時に使いましょう。

INDEX関数

エクセルINDEX関数は、行番号と列番号が交差する位置にあるセルの値を返す関数です。エクセルINDEX関数には、配列形式とセル範囲形式の2つの使い方があります。

配列形式は、指定した範囲からある値を返します。セル範囲形式は指定した範囲からセルの参照を返します。

INDEX関数、配列形式の書式と引数

INDEX(配列, 行番号, [列番号])

配列：　　配列が1行または1列の場合、行番号または列番号を省略できます。

行番号：　行番号を省略した場合は、必ず列番号を指定します。

列番号：　列番号を省略した場合は、必ず行番号を指定します。

INDEX関数、配列形式の使用例

G2に式を入力しましょう。

G2　=INDEX(A1:D5,4,4)

範囲を「A1:D5」の中で、上から４番目の行、左から４番目の列の値を返します。

結果は「68,000」になります。

INDEX関数、セル範囲形式の書式と引数

INDEX(参照, 行番号, [列番号], [領域番号])

参照：　複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、１つの範囲と範囲の間をを半角のコンマ（,）で区切ります。各領域が1行または1列の場合、行番号または列番号はそれぞれ省略できます。

INDEX関数、セル範囲形式の使用例

F2に式を入力しましょう。

F2　=INDEX((A2:C3,A6:C7),2,3,2)

領域番号は「2」です。２番目の領域「A6:C7」が選択されます。２番目の「A6:C7」の領域、上から２番目の行、左から３番目の列の値を返します。

結果は「38,000」です。

MATCH関数

指定した範囲内から検査値を探しだし、その値の相対的な位置を返します。

こちらの記事もご覧ください。⇒エクセルVLOOKUP関数とMATCH関数で値を検索！VLOOKUPの弱点を克服

MATCH関数の書式と引数

MATCH(検査値, 検査範囲, [照合の型])

照合の型

「１」：検査値以下の最大の値を返します。この場合、検査範囲を昇順の並べ替える必要があります。

「０」：検査値と一致する一番最初の値を返します。行の場合は左から、列の場合は上から最初の値を返します。

「-1」：検査値以上の最小の値が検索されます。この場合、検査範囲を降順に並べ替える必要があります。

MATCH関数の使用例

F2に、製造№「B15000」の位置を返す式を入力しましょう。

F2　=MATCH(“B15000”,A2:A6,0)

「B15000」は「A2:A6」の範囲内で４番目なので「4」の値を返します。

結果は「4」になります。

IFERROR関数

数式がエラーの場合、指定した値を返します。それ以外は数式を返します。

こちらの記事もご覧ください。⇒エクセルIFERROR関数でVLOOKUP関数のエラーを回避する方法

IFERROR関数の書式と引数

IFERROR(値, エラーの場合の値)

IFERROR関数の使用例

D2に式を入力しましょう。

D2　=IFERROR(B2/C2,”エラー”)

B2/C2の数式の結果は「エラー」にはなりません。B2/C2の数式の結果「0.5」を返します。

D3に式を入力します。

D3　=IFERROR(B3/C3,”未設定”)

結果は、B3/C3の数式はエラー値「#DIV/0!」です。「未設定」が表示されます。

こちらの記事もご覧ください。⇒VLOOKUP関数のエラーをエクセルIF関数で回避する方法

ROW関数

指定した配列の行番号を返します。

ROW関数の書式と引数

ROW関数の使用例

SUMPRODUCT関数で合計を求める使用例

F2に金額を求める式を入力しましょう。

F2　=SUMPRODUCT(C2:C9,D2:D9,E2:E9)

金額は、「20,254」になります。

この式を解説すると

F2　＝C2*D2*E2+C3*D3*E3*+C4*D4*E4*+C5*D5*E5*+C6*D6*E6*+C7*D7*E7*+C8*D8*E8+C9*D9*E9

上の式になります。

J2にSUMPRODUCT関数で「東京 かつ りんごの合計金額」を求めてみます。

J2　=SUMPRODUCT((A2:A9=H2)*(B2:B9=I2),C2:C9,D2:D9)

合計金額は、「6,400」になります。

この式を解説すると

J2　＝C2*D2+C6*D6+C8*D8

「A2:A9」の範囲で「東京」、B2:B9の範囲で「りんご」となる列の積、「C列＊D列」を返します。

INDEX、MATCHを組み合わせて複数列の条件で値を抽出

エクセルINDEX関数とMATCH関数の複数条件で、OR(または)を使う場合は、１行または１列のセルの範囲で、AまたはBまたはC、以下複数の条件を優先順位で抽出したい時です。AND(かつ)を使う場合は、２つ以上の複数条件で、完全一致する値のみ抽出したい時です。INDEX関数とMATCH関数を使うと、複数列の条件でも値を抽出することができます。