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

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

 

 

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」の3つの条件で、該当する金額を求める式を入力しましょう。

 

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、SUMPRODUCT、ROWを組み合わせてAND条件で抽出

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

 

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

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

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

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

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

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

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

=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関数で行番号を取得します。

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

 

SUMPRODUCT関数の配列計算は、完全一致の値を返さない

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関数でワイルドカードで検索

複数列複数条件(AND)で値を抽出する場合の注意点

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

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

こちらの記事もご覧ください。⇒エクセルOFFSET関数の使い方 MATCH関数と組み合わせて可変するセル範囲に対応

 

関数の説明

INDEX関数

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

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

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

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

配列 セルの範囲を指定します。
行番号 配列内の値の行番号を、数値で指定します。
列番号 配列内の値の列番号を、数値で指定します。
解説

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

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

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

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

 

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

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

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

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

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

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

参照 1つまたは複数のセルの参照を指定します。
行番号 セル参照を返す行位置を数値で指定します。
列番号 セル参照を返す列位置を数値で指定します。
領域番号 任意です。行番号と列番号が交差する位置を返す参照の範囲を1つ選択します。最初の範囲は「1」、次は「2」というように番号で領域を指定します。
解説

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

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

 

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

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

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

結果は「38,000」です。

 

 

MATCH関数

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

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

MATCH関数の書式と引数

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

検査値 検索する値を指定します。検査値には、数値、文字列、または論理値、またはこれらの値に対するセル参照を指定できます。
検査範囲 検索するセルの範囲を指定します。
照合の型 -1、0、1の数値のいずれかを指定します。省略した場合は自動的に1が選択されます。
解説

照合の型

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

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

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

MATCH関数の使用例

 

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

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

「B15000」は「A2:A6」の範囲内で4番目なので「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([範囲])

範囲  行番号を調べたいセルまたはセル範囲を指定します。省略した場合、ROW関数が入力されているセルの行番号が返されます。

ROW関数の使用例

 

数式が入力されている行の行番号を調べます。

A2 =ROW()

A2の行番号は「2」です。

行番号「2」が返されます。

A3 =ROW(B5)

B5の行番号は「5」です。

行番号「5」が返されます。

A4 =ROW(B3:B6)

「B3:B6」の範囲の1行目の行番号を調べます。1行目はB3なので、行番号は「3」です。

行番号「3」が返されます。

 

 

SUMPRODUCT関数

引数として指定した配列の積を計算し、次にその和を返します。

SUMPRODUCT関数の書式と引数

SUMPRODUCT(配列 1, [配列 2], [配列 3], …)

配列 1 計算の対象となる要素を含む最初の配列引数を指定します。
[配列 2], [配列 3], …. 省略可能です。計算の対象となる要素を含む配列引数を指定します。

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列」を返します。

こちらの記事もご覧ください。⇒エクセルSUMPRODUCT関数で複数条件で抽出した数値を合計する

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

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