エクセルINDEXとMATCH関数の組み合わせ!複数条件OR(または)で値を抽出

エクセルINDEX関数とMATCH関数を組み合わせて、複数条件で検索する方法を紹介しています。INDEX関数とMATCH関数を組み合わせて、複数条件OR(または)で検索して値を抽出する場合、最初見つけた値を抽出します。式の左側から値を探していきます。複数条件OR(または)で検索する式は、優先順位で値を抽出したい場合に役立ちます。INDEX関数とMATCH関数を組み合わせて、複数条件OR(または)での抽出をマスターして、仕事アップしましょう。

 

 

INDEX、MATCH、IFERRORを組み合わせて、OR条件で値を取得

IFERROR関数、INDEX関数、MATCH関数で、複数条件OR(または)で、値を取得してみましょう。

 

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

エリアが「東京」、または「神奈川」、または「福岡」だったら商品№を返します。

F2 =IFERROR(INDEX(B2:B10,MATCH(E2,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E3,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E4,A2:A10,0),1),”エラー”)))

「12005」を返します。

F2の式は、式の左から検索して、条件を満たす値を抽出します。

最初に「東京」、次に「神奈川」、次に「福岡」の順番に探していき、探す途中で条件に満たす値があったら、その値を返します。

検査範囲「A2:A10」には、「東京」、「神奈川」、「福岡」の3つともあります。

優先順位「東京」、「神奈川」、「福岡」の順で見つかった値から返していきます。

最初に「東京」が見つかったので、「東京」の商品№を返します。

次に、F5に、エリアが「愛媛」、または「京都」、または「熊本」だったら商品№を返す式を入力しましょう。

F5 =IFERROR(INDEX(B2:B10,MATCH(E5,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E6,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E7,A2:A10,0),1),”エラー”)))

「32000」を返します。

「愛媛」は「A2:A9」の範囲にありません。次に「京都」を探します。「京都」はあります。

F5には「32000」が返されます。

次は、F8に、エリアが「奈良」、または「広島」、または「山口」だったら商品№を返す式を入力しましょう。

F8 =IFERROR(INDEX(B2:B10,MATCH(E8,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E9,A2:A10,0),1),IFERROR(INDEX(B2:B10,MATCH(E10,A2:A10,0),1),”エラー”)))

「エラー」を返します。

最初に「奈良」を、次に「広島」を、その次に「山口」を探します。「奈良」「広島」「山口」がないので、エラー値「#DIV/0!」になります。

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

 

複数条件OR(または)で抽出される値の優先順位

INDEX関数とMATCH関数で、複数条件OR(または)で検索して値を抽出する場合、最初見つけた値を抽出します。

式の左側から検索していくので、左側から見つかった値を返します。

関数の説明

INDEX関数

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

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

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

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

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

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

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

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

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

 

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

G2 =INDEX(B2:D5,2,2)

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

結果は「650」を返します。

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

 

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

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

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

参照: 複数の範囲を指定する場合は、複数指定した範囲全体ををかっこ () で囲み、1つの範囲と範囲の間をを半角のコンマ(,)で区切ります。

各領域が1行または1列の場合、行番号または列番号はそれぞれ省略できます。

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

 

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

G2 =INDEX((A2:D3,A5:D7),2,4,2)

セルの範囲は「A2:D3」と「A5:D7」で、領域番号を2に指定しましたので、2番目の領域「A5:D7」が選択されます。「A5:D7」の上から2番目の行、左から4番目の列の値を返します。

結果は「900」を返します。

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

 

MATCH関数

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

MATCH関数の書式と引数

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

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

照合の型

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

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

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

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

MATCH関数の使用例

 

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

G2 =MATCH(32000,B2:B6,0)

「B2:B6」の範囲内で「32000」の値を探します。「32000」は「B2:B6」の範囲内で3番目なので「3」の値を返します。

結果は「3」です。

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

 

IFERROR関数

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

IFERROR関数の書式と引数

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

エラーかどうかチェックします。
エラーの場合の値 数式がエラーと判断された場合に返す値を指定します。

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

IFERROR関数の使用例

 

D2に、B2/C2がエラーの場合「エラー」を返す式を入力しましょう。

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

結果は「エラー」でないのでB2/C2の数式の結果「1.2」を返します。

D3に、B3/C3がエラーの場合「エラー」を返す式を入力します。

D3 =IFERROR(B3/C3,”エラー”)

結果は、エラー値「#DIV/0!」になります。エラーの場合に指定した値「エラー」が表示されます。

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

 

INDEX、MATCHを使ってAND条件で値を取得できます

エクセルINDEX関数とMATCH関数の複数条件、2つ以上で値を取得する場合は、1行または1列のセルの範囲で、MATCH関数の検査値、AまたはBまたはC・・・と複数の条件を指定したいときです。

OR(または)の複数条件で値を取得できましたが、INDEX関数とMATCH関数では、AND(かつ)の検索もできます。他の関数と組み合わせることによって、ORまたは、ANDの複数条件で値を取得できるようになります。