【Excel・エクセル】MATCH関数とINDEX関数で複数条件に合うセルを抽出する

もりの

複数の条件に一致するデータを抽出したいんだ。

やまの

MATCH関数とINDEX関数の組み合わせで、できるんよ。

今回のポイント
  • 複数条件を指定するなら、条件を「&」でつなげる
  • =INDEX(セル範囲,MATCH(条件1&条件2,条件範囲1&条件範囲2,0),列番号)
  • 配列数式にするので、エンターキーではなく「Ctrl + Shift + エンターキー」

スポンサーリンク

 MATCH×INDEX複数条件の練習ファイルダウンロード

もりの
 
 

一緒にやってみませんか?

【Excel・エクセル】MATCH関数とINDEX関数のおさらい

MATCH関数とは?

MATCH関数は、値が範囲の中の何行目(何列目)にあるかが分かる関数です。

MATCH関数の書き方

=MATCH(検査値,検査範囲,照合の種類)

検査値この値は
検査範囲この中の何番目?
照合の種類0:完全一致、-1:以上、1:以下

たとえばセルE2の値がセルA2~A6の中の何行目にあるかは、「=MATCH(E2,A2:A6,0)」で分かります。

MATCH関数で行数を出した画像
MATCH関数の使い方
やまの

詳しくは、こっちのページで解説しているよ。

INDEX関数とは?

INDEX関数は、範囲の中で、指定した位置にある値が分かる関数です。

2つの書き方がありますが、今回は以下の書き方で使います。

INDEX関数の書き方(範囲1つ)

=INDEX(配列,行番号,列番号)

INDEX次の位置にある値を教えて
配列この範囲の
行番号この位置(上から数えて何行目か)
配列が1列の時は、省略できる
列番号この位置(左から数えて何列目か)
配列が1行の時は、省略できる

例えばセルA1~D5の中で、4行3列目にある値を知りたい場合、「=INDEX(A1:D5,4,3)」となります。

INDEX関数の範囲が一つの場合
範囲が一つの場合
もりの

INDEX関数は、こっちで詳しく教えてもらったよ!

MATCH関数とINDEX関数の組み合わせ方

このMATCH関数とINDEX関数を組み合わせると、VLOOKUP関数のように条件に合うデータを抽出できます。

INDEX関数の行番号を、MATCH関数で指定するんです。

書き方は、こんな感じ。

2つを組み合わせた書き方

=INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号)

やまの

そして今回は、この条件を複数指定する式を見ていくよ。

【Excel・エクセル】MATCH関数とINDEX関数で複数条件を指定する

この表から「女性」かつ「10代」、2つの条件に合う評価を、MATCH関数とINDEX関数を組み合わせて出したいと思います。

MATCH関数とINDEX関数を組み合わせて複数条件に合うセルを抽出する練習問題
複数条件に合うデータを取り出したい

やり方は簡単、条件にあたるMATCH関数の検査値を&でつなげて、検査範囲も同じように&でつなげます。

そして配列数式にすればいいのです!

もりの

とても簡単そうには聞こえんのですよ。

やまの

{=INDEX(A1:C7,MATCH(A10&B10,A1:A7&B1:B7,0),3)}で、求められるよ。説明するね。

ステップ1

答えを出したいセルに、「=INDEX(」と入力してください。

「=INDEX(」まで入力した画像
まずは関数名

ステップ2

配列はVLOOKUP関数の範囲のようなもの。

元になる表を範囲選択すればOKです。

そして「,(カンマ)」

配列を指定した画像
データベースを範囲選択

ステップ3

行番号はMATCH関数で表します。

「MATCH(」と入力しましょう。

「MATCH(」まで入力した画像
MATCH関数の出番だぜ

ステップ4

ここで1つ目の条件を指定します。

条件が書かれたセルA10をクリックします。

1つ目の条件を指定した画像
1つ目の条件を指定

ステップ5

2つ目の条件を&でつなげます。

「&」と入力して、2目の条件セルB10をクリックしてください。

複数条件を&でつなげた画像
複数条件を&でつなげる
もりの

「,」も忘れないようにしなきゃ。

ステップ6

検査範囲は、先ほど指定した検査値(条件)と同じように指定します。

まず1つ目の条件(女性)が書かれた範囲、セルA1~A7までをドラッグします。

1つ目の条件の範囲を指定した画像
1つ目の条件(性別)の範囲を指定

ステップ7

2つ目の条件範囲を&でつなげます。

「&」を入力して、2つ目の条件(10代)が書かれた範囲、セルB1~B7をドラッグしてください。

そして「,(カンマ)」

2つ目の条件範囲を&でつなげた画像
2つ目の条件範囲を&でつなげる

ステップ8

照合の種類は「0」でOKです。

照合の種類を0にした画像
照合の種類は「0」でOK

ステップ9

これでMATCH関数は終わりなので、カッコを閉じます。

INDEX関数は続くので「,」も忘れずに。

MATCH終了のカッコを書いた画像
MATCHはここまで

ステップ10

最後に抽出したい列番号です。

今回知りたいのは評価、評価は最初にドラッグした配列の3列目にあるので、ここでは「3」と入力します。

これでINDEX関数も終わりなので、カッコを閉じます。

列番号を指定した画像
最後に列番号
もりのの笑った顔
もりの

これで完成だね!エンターキーを押せば……

やまのの困った顔
やまの

あ、エンターキーは押さないで!

そのままエンターキーを押すと、#VALUE!のエラーになってしまいました。

#VALUE!のエラーになった画像
#VALUE!のエラーになってしまった
やまのの困った顔
やまの

この式を計算するには、配列数式しないといかんのよ。

ステップ11

とはいえ、配列数式のやり方はめっちゃ簡単。

いつものエンターキーではなく、Ctrl + Shift + エンターキーを押すだけ!

もりの

CtrlキーとShiftキーも一緒に押すだけなんだね。

完成!

無事、性別と年代、二つの条件に一致する評価が抽出できました!

MATCH関数とINDEX関数で複数条件に合うデータが抽出できた画像
複数条件に合うデータが抽出できた!

式を見てみると、「{}」で囲まれていますね。

これが、配列数式にしたよって目印なんです。

やまの

さらに&でつなげれば、3つ以上の条件も指定できるんよ。

これで、MATCH関数とINDEX関数で複数条件を指定できますね!
それでは、お疲れさまでした!

今回のおさらい
  • 複数条件を指定するなら、条件を「&」でつなげる
  • =INDEX(セル範囲,MATCH(条件1&条件2,条件範囲1&条件範囲2,0),列番号)
  • 配列数式にするので、エンターキーではなく「Ctrl + Shift + エンターキー」