INDEX＋MATCH関数を複数条件にする方法｜全て一致を抽出

ここでは、INDEX＋MATCH関数で「複数条件のすべてに一致するデータを抜き出す方法」を解説します。

なお、「どれかを満たす」のOR条件の数式の作り方は、次のページで紹介しています。

⇒ INDEX＋MATCH関数で複数条件にORで一致したデータを抜き出す方法

まずは、やりたいことを例を含めて確認します。

１－１．INDEX＋MATCH関数を「すべての条件に一致なら抽出」の形の複数条件にしたい

まず、やりたいことは「INDEX＋MATCH関数を『すべての条件に一致なら抽出』の形の複数条件にする」です。

ポイントは、次の2つです。

条件の指定はMATCH関数の「検査値」で行いますが、指定できる条件は基本的に一つなので、これを複数条件にできるようにします。

そして、「全ての条件に一致なら抽出（AND条件）」の形にします。なお、「どれかに一致なら抽出（OR条件）」の形は以下のページで解説しています。

⇒ INDEX＋MATCH関数で複数条件にORで一致したデータを抜き出す方法

１－２．例で確認

一つ例を見てみましょう。

上の表は、「商品販売実績の一覧」です。販売日や販売先、売れた商品名や個数、単価などが入力されています。

例えばこの表から、「『4月2日』に『タオル』を販売した相手先のデータ」を抜き出してみます。

この場合条件値は、「販売日の列が『4/2』」と「商品名の列が『タオル』」の2つです。

結果、「4/2にA社にタオルを3つ売った」というデータが抽出できています。

このように「INDEX+MATCH関数で『複数条件全て一致』の数式」は、「複数条件のどれにも当てはまるデータを抽出」できます。

次に、「数式の形」と「普段と違う点」を確認します。

２－１．数式の形

まず、数式の形は次の通りです。

「検査値1と検査範囲1」、「検査値2と検査範囲2」はセットです。

条件の数だけこのセットが増えます。

２－２．普段と違う点は３つ

普段のINDEX+MATCH関数と違う点は、次の３つです。

まず、「検査値も検査範囲も『&』でつないで複数指定」します。

次に、「検査値1に対して検査範囲1」、「検査値2に対して検査範囲2」…というように順番を合わせます。

そして数式が出来上がったら、「数式の前後に『{ }』を付けて『配列数式』」にします。ただしこれは手入力ではなく、ある一定の操作で付けます。

では、実際に数式を作ってみます。

先ほどの例を使い、条件も「販売日が『4月2日（セルA11の値）』」で「商品名が『タオル（セルB11の値）』」の2つにし、数式をセルA15に入れていきます。

以下手順です。

（１）以下の数式をコピーし、セルに貼り付ける

【コピーする数式】

（２）「配列」の文字を消し、元の表全体を指定後、F4キーを1回押す

※ F4キーを押すことで「$」が4つ付きます

（３）「検査値1」の文字を消し、一つ目の条件が入ったセルを指定後、F4キーを1回押す

※ F4キーを押すことで「$」が2つ付きます

（４）「検査値2」の文字を消し、二つ目の条件が入ったセルを指定後、F4キーを1回押す

（５）「検査範囲1」の文字を消し、１つ目の検査範囲を指定後、F4キーを1回押す

（６）「検査範囲2」の文字を消し、２つ目の検査範囲を指定後、F4キーを1回押す

（７）「照合の種類」の文字を消し、「0」を入れる

（８）「列番号」の文字を消し、欲しい値の列番号を入れる

（９）「Ctrlキー」と「Shiftキー」を押しながらEnterキーを押す

（10）他のセルに数式をコピーし、列番号を変えた後、（９）と同じ操作をすれば完了

セルA15に入れた数式は、次のようになります。

{=INDEX($A$3:$F$7,MATCH($A$11&$B$11,$A$3:$A$7&$C$3:$C$7,0),2)}

先ほど触れた以外の、数式作成のポイントは次の通りです。

F4キーを押して「$」を付けるのは、他のセルに数式をコピーした時に「配列」や「検査値」がズレないよう「絶対参照」にするためです。

また、これはMATCH関数の基本ですが、「照合の種類は基本『0』」です。

そして、「{ }」を付ける操作「Ctrl+Shift+Enter」を忘れると#VALUE!エラーになります。

逆に#VALUE!エラーになったら「操作を忘れたかな？」と思いましょう。

次に、INDEX+MATCH関数の複数条件での抽出結果を「別シートに表示」させる方法です。

５－１．別シート参照させる引数は

まず、「結果を表示させるシート」と「元の表があるシート」を別にする場合、特定の引数は「元の表（結果の表示とは別）のシート」を参照させる必要があります。

それは、次の赤字の所です。

このように、「配列」と「検査範囲」は、「結果を表示させるシートとは別のシートの範囲」を指定する必要があります。

５－２．数式の例

では数式が実際にどのようになるか、例を見てみます。

まずは例の状況です。

上は、「元の表」シートにある商品販売実績表です。

そして上が、「抽出結果」シートにある抽出結果を表示させる表です。

このような状態の場合、セルA8に入れる数式は次のようになります。

上のように、別シートを参照する引数には「シート名!」が付きます。なお、シート名によっては「'シート名'!」となることもあります。

５－３．引数で別シートを指定するコツ

引数で別シートを指定するコツを、引数「配列」の指定を例に見てみます。

以下手順です。

（１）「配列」の文字を消した後、参照したいシートをクリックする

（２）「配列」に指定する範囲を選択する

このように、指定したいシートをクリックすることで「シート名は自動で入力」されます。

なお、検査値を指定する場合には「抽出結果」シートをクリックする、検査範囲を指定する場合には「もとの表」シートをクリックする、ということを繰り返します。

やってみるととても簡単です。

条件が３つ以上の場合、数式は以下の通りです。

【コピーする数式】

【コピーする数式】

検査値と検査範囲のセットが増えただけで、数式の作り方は一緒です。

コピーしセルに貼り付けて使ってください。

以上、参考になれば幸いです。

次のページでは、INDEX＋MATCH関数で複数条件のどれかに当てはまればという、OR条件の指定の仕方を解説します。よかったら参照してください。

→ 次のページへはこちら

INDEX＋MATCH関数の、他の記事へのリンクはこちらです。

• INDEX＋MATCH関数その他の記事へのリンクはこちら

• ◎基本的な使い方
• ◎空白以外を詰めて全部抽出する方法
• ◎条件値指定のポイント
• ◎含む条件の指定の仕方
• ◎AND（どれにも当てはまる）で指定する方法
• ◎OR（どれかに当てはまる）で指定する方法
• ◎２番目、３番目以降の値を抽出する方法
• ◎横方向を検索し値を抽出する方法
• ◎別シートからマッチングしたデータを抽出する方法
• ◎別ファイルからデータを抽出する方法
• ◎範囲条件に合うデータを全部抽出する方法
• ◎含む条件に合うデータを全部抽出する方法
• ◎OR条件に合うデータを全部抽出する方法
• ◎#N/Aエラーが起こる原因と回避法
• ◎うまくいかない、おかしい時の対処法

関数の解説記事一覧へはこちら