【Excel関数】Index関数とMatch関数でデータを抽出する(条件が複数・条件に合うデータが複数の場合)

この記事では、Index関数とMatch関数を使って表から検索対象のデータを抽出する方法についてご説明します。

今回は条件が複数の場合について詳しくお話していきます。

ただし、条件に合うデータが複数ある場合はいくつか手間が必要なので、その点についても詳しくご説明します。

【動画】Index関数とMatch関数を使って表から検索対象のデータを抽出する実際の動き

本題に入る前に、まずは次の動画をご覧ください。

顧客名に該当するデータを左側の表からIndex関数とMatch関数を使って抽出し、該当したデータを右側の表に出力しています。

今回のデータは、該当する顧客のデータが複数あるため、Index関数とMatch関数を使って複数あるデータに該当する項番(A列)を抜き出してユニークで並べて(J列)、その項番を元にさらにIndex関数とMatch関数を使って右の表に出力しています。

また、検索条件が複数(今回は条件が2つ)あり、その2つの条件を満たす店舗データを右側の表に出力しています。

ちなみに、ユニークで項番をJ列に並べるのにUnique関数を使用していません。(Unique関数はOffice365から使用可能。使用しているのはINDEX関数とMATCH関数、COUNTIF関数です)

Excelファイルの例

今回は次のExcelファイルを用意しました。

以上のExcelファイルは、左の表(A列からH列)にいくつかの店舗データが存在しており、セルA3に入力された顧客名と、セルA6に入力された商品名に該当する店舗データを抽出して右側の表(K列からR列)に出力するExcelファイルです。

セルA3にD列の顧客名のどれかを入力し、次にセルA6にE列の商品名のどれかを入力すると、入力された顧客名と商品名に合致する店舗のデータが右側の表に出力されます。

前提

今回の記事は、以下のポイントを前提として説明していきます。

【前提①】一意の値が入っている列が表に存在していること

検索値に該当するデータが表の中のどの行に存在するのかを特定するために、一意の値が入っている列が表に存在していることが前提になります。

今回用意したExcelファイルだと、A列が一意の値が入っている列になります。

【前提②】条件に合うデータが表の中に複数ある場合

今回の記事は、条件に合うデータが表の中に複数あることが前提の内容となっています。

【前提③】検索条件は複数

今回の記事は、検索条件は複数(今回は条件が2つ)であることが前提の内容となっています。

以上、3つの前提を踏まえてご説明していきます。

Excelファイルの作成の流れ

今回用意したExcelファイルの作成の手順

①Index関数とMatch関数でキーとなるセルの値を抽出

【STEP.1】左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込む

セルI9に以下の計算式を入力します。

【STEP.2】STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

I9のセルを、最後の行位置(セルI28)までコピーします。

②合致したデータが何行目にあるかをユニークでリストアップ

【STEP.3】STEP.1のセル「I9」の値を、その右隣りのセルから参照する

J9のセルに、I9のセルを参照する計算式を入力します。

【STEP.4】I列の値をユニークで取得する計算式をセル「J10」に埋め込む

セルJ10に以下の計算式を入力します。

【STEP.5】STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

J10のセルを、最後の行位置(セルJ28)までコピーします。

③顧客名に該当する店舗のデータを抽出して表に出力する

【STEP.6】STEP.3のセルの値を条件に、合致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込む

セルK9に以下の計算式を入力します。

【STEP.7】STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

K9のセルを、最後の行位置(セルK28)までコピーします。

以上で手順は完了です。

Excelファイル作成の解説

ここからは先ほどお話したExcelファイルの作成の流れについて詳しく説明していきます。

①Index関数とMatch関数でキーとなるセルの値を抽出

【STEP.1】左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込む

左の表の、再右列の右隣の列の1行目に、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込みます。

埋め込んだ計算式は次の通りです。

①Match関数

Match関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。

検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。

Match関数の1つ目の引数には検索文字列を指定します。

今回はセルA3とセルA6に入力された文字列のデータを検索したいので、1つ目の引数にこのセルA3とセルA6の値を指定しています。

ちなみにセルA3とセルA6の2つを条件にしたいので、セルA3とセルA6を「&」の文字で結合させて1つ目の引数に指定しています。

なお、「A3」「A6」ではなく「A$3」「A$6」と記述しているのは、行3と行6の位置を絶対参照するためです。

絶対参照とは、セルの参照位置がずれないように参照する、という意味です。

今回はこのMatch関数使った計算式をセルI25までコピーして使いたいのですが、絶対参照に設定しておかないと、3の値が変わってしまいます(ずれてしまいます)

下の画像は、試しにセルI9の計算式を一つ下のセルにコピーした時の画像です。

絶対参照に設定しておかないと、セルA4とセルA7を参照してしまいます。(参照位置が一つずれる)

絶対参照に設定しておくと、セルの参照位置はずれません(セルA3とセルA6を参照したまま)

一つ下の位置にコピーしてもA3とA6のセルを参照したい場合は「A$3&$A$6」と記述する必要があります。

検索文字列のセルを参照する場合は絶対参照するように記述します。

Match関数の2つ目の引数には検索文字列を検索するセルの範囲を指定します。

今回は検索するセルの範囲が左の表の中なので、セルA9からH28を検索範囲として2つ目の引数に「D9:D$28&E9:E$28」を指定しています。

今回は条件が2つあるので、顧客名を検索する先のセルD9からセルD28(「D9:D$28」)を、商品名を検索する先のセルE9からセルE28(「E9:E$28」)を「&」で結合させて2つ目の引数に指定しています。

なお、セルの指定を「D28」「E28」ではなく「D$28」「E$28」と記述しているのは、セルD25とセルE25の行位置を絶対参照するためです。

絶対参照については先ほどの説明した通り、セルの参照位置がずれずに参照する指定方法ですが、「D28」「E28」の行のセル位置はずらしたくないので絶対参照で指定します。

絶対参照で指定しないと、セルをコピーした時にセルの行の位置がずれてしまいます。

Match関数の3つ目の引数には照合する種類を指定します。

この照合する種類には3つあります。

今回は完全に一致する値を検索したいので0を指定しています。

②Index関数

Index関数は、行と列を指定したセルの値を取得します。

今回は、行はMatch関数で取得した行位置、列はA列の値(項番)を取得したいので1を指定しています。

Index関数の1つ目の引数には左の表のセル範囲を指定します。

Index関数の2つ目の引数にはMatch関数で取得した行位置を指定します。

Match関数により、検索した文字列が何行目にあるのかが特定できるので、その何行目かを2つ目の引数に指定します。

Index関数の3つ目の引数には左の表のセル範囲を指定します。

列はA列の値(項番)を取得するために1を指定しています。

③IFERROR関数

IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。

今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。

エラーではない場合は1つ目の引数の値をセルに出力します。

IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。

今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。

IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。

今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。

【STEP.2】STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

STEP.1の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。

コピーが完了すると、コピーしたセルに値が出力されます。

コピー元とそのコピーしたセルには、3、9の値が出力されています。

この値は、A3の検索文字列がD9からD28の中に存在していて、かつ、EセルA6の検索文字列がE9からE28の中に存在している行の項番を指します。

これで検索文字列「自然食品スーパー」と「ナポリタンの素」が表に存在する項番が特定できました。

ただし、項番3と項番19は複数あるので、次は3、19の値をユニークで取り出します。

②合致したデータが何行目にあるかをユニークでリストアップ

【STEP.3】STEP.1のセル「I9」の値を、その右隣りのセルから参照する

STEP.1のセル「I9」の値を、その右隣りのセルから参照します。

今回例で挙げたExcelファイルで言うと、セル「I9」の右隣りのセルは「J9」になります。

セル「J9」に、左隣のセル「I9」の値が出力されました。

この値をベースに、次のSTEP.4ではJ列のセルにI列のセルの値をユニークで取得していきます。

【STEP.4】I列の値をユニークで取得する計算式をセル「J10」に埋め込む

セルJ7の一つ下のセルに、検索値に該当する項番を抽出するIndex関数とMatch関数を埋め込みます。

埋め込んだ計算式は次の通りです。

①COUNTIF関数

COUNTIF関数は、検索する値が検索先にいくつ存在するのかを数えて、その数を返す関数です。

今回はセルJ6の値が、セルI9からセルI28の中にいくつあるのかを数えてその数を返します。

1つ目の引数には、検索する値のセルを指定します。

セルJ9の値がいくつあるのかを数えるために、引数にセルJ9を指定します。

2つ目の引数には、検索範囲のセルを指定します。

今回は、セルJ9の値をセルI9からセルI28の範囲で検索するので、I$9:I$28と指定しています。(指定は絶対参照で)

②MATCH関数

MATCH関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。

今回は、COUNTIF関数が対象とするセルの範囲を対象に、COUNTIF関数が0を返すセルが何行目にあるのかを探して、特定後にその行数を返します。

検索文字列に対してCOUNTIFが0を返す行位置が特定できれば、検索文字列でない文字列が入ったセルの行位置、つまり検索文字列以外の値が入ったセルが特定できるので、ユニークな値を取得することができます。

MACTH関数の1つ目の引数には検索文字列を指定します。

今回は1つ目の引数に0を指定しています。

なぜ0を指定しているのかというと、セルI9からI28のセルの中からJ9以外の値を探すためです。

値が0の場合は、J6の値以外の値であることを指します。

セルI9からI28のセルの中からJ6以外の値が見つかったらその値をJ列のセルに出力します。

MACTH関数の2つ目の引数には、COUNTIF関数の戻り値を指定します。

COUNTIF関数の戻り値はCOUNTIF関数が検索した値がいくつあるのかを数えた数ですが、その戻り値が0である場合は第1引数で指定した0と値が一致するので、もしCOUNTIF関数の戻り値が0の場合はその0を返すセルの位置を返します。

例えば(I9からI11までのセルの値全てがJ9と一致していて)セルI12の値がセルJ9の値と一致していなければ、19を返します。(セルI9から数えて下に4行目の意)

MACTH関数の3つ目の引数には照合する種類を指定します。

この照合する種類には3つあります。

今回は完全に一致する値を検索したいので0を指定しています。

③INDEX関数

Index関数は、行と列を指定したセルの値を取得します。

また、Match関数で取得した行位置が空白の場合は、そのまま空白を返します。

今回は、行はMatch関数で取得した行位置を指定しています。

ただし、今回はIndex関数に列を指定していません。

なぜなら今回は(複数の列ではなく)I列の1列だけを対象にしているからです。

1列だけを対象にする場合は列番号の指定は省略することができます。(第3引数は無し)

INDEX関数の1つ目の引数には、セルI9からセルI28を指定します。(範囲の指定)

INDEX関数の2つ目の引数には、Match関数で取得した行位置を指定します。

④IFERROR関数

IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。

今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。

エラーではない場合は1つ目の引数の値をセルに出力します。

IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。

今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。

IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。

今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。

【STEP.5】STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。

STEP.4の計算式が埋め込まれたセルを、表の最後の行位置までコピーすることで、セルI9からセルI28までの値から重複しないユニークな値を抽出することができました。

以上の画像では「自然食品スーパー」と「ナポリタンの素」を検索値として、左の表にD列に「スーパーショップ」かつE列に「ナポリタンの素」が含まれる行がどの行に存在するのかを特定した結果、項番が3と19であることが分かりました。

次に、D列に「スーパーショップ」かつ、E列に「ナポリタンの素」を含んでいる店舗のデータを右の表に出力させるために、次のSTEPに進みます。

③顧客名に該当する店舗のデータを抽出して表に出力する

【STEP.6】STEP.3のセルの値を条件に、合致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込む

STEP.3のセルの値を条件に、一致する店舗データを抽出するIndex関数とMatch関数をSTEP.3のセルの右のセルに埋め込みます。

埋め込んだ計算式は次の通りです。

①Match関数

Match関数は、検索文字列を、検索したい範囲のセルの中から検索し、何行目にあるのかを検索する関数です。

検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。

Match関数の1つ目の引数には検索文字列を指定します。

今回はセルJ6のデータを検索したいので、1つ目の引数にこのセルJ6を指定しています。

検索して見つかった場合は、検索したセル範囲の上から数えて何行目かを返します。

Match関数の2つ目の引数には検索文字列を検索するセルの範囲を指定します。

今回はJ9の項番と一致する項番をセルA9からA28までの間で探したいので、2つ目の引数に「A$9:A$28」を指定しています。

なお、セルA9からA28の指定を「A9:A28」ではなく「A$9:A$28」と記述しているのは、行9と行28の位置を絶対参照するためです。

絶対参照とは、セルの参照位置がずれないように参照する、という意味です。

今回はこのMatch関数使った計算式をセルK28までコピーして使いたいのですが、絶対参照に設定しておかないと、行9と行28の位置が変わってしまいます(ずれてしまいます)

絶対参照に設定しておくと、セルの参照位置はずれません(セルA9からA28を参照したまま)

Match関数の3つ目の引数には照合する種類を指定します。

この照合する種類には3つあります。

今回は完全に一致する値を検索したいので0を指定しています。

②Index関数

Index関数は、行と列を指定したセルの値を取得します。

今回は、行はMatch関数で取得した行位置、列は左の表の列全てを対象に取得したいので0を指定しています。

Index関数の1つ目の引数には左の表のセル範囲を指定します。

Index関数の2つ目の引数にはMatch関数で取得した行位置を指定します。

Match関数により、検索した文字列が何行目にあるのかが特定できるので、その何行目かを2つ目の引数に指定します。

Index関数の3つ目の引数には左の表のセル範囲を指定します。

今回は引数に0を指定しています。

引数が0の場合、第1引数のセルの列範囲のデータを対象にします。

第1引数のセルがAからHの列を対象としているので、AからHの列を対象にデータを取得します。

③IFERROR関数

IFERROR関数は、計算式がエラーを返す場合はそのセルに何かしらの値をセルに表示させる関数です。

今回は計算式がエラーを返す場合は空白を表示させるよう「””」をIFERROR関数の2つ目の引数に指定しています。

エラーではない場合は1つ目の引数の値をセルに出力します。

IFERROR関数の1つ目の引数にはエラーが出るか判定させる値を指定します。

今回はIndex関数のエラー判定を行うため1つ目の引数に指定しています。

IFERROR関数の2つ目の引数には、1つ目のセルの値がエラーの場合にそのままエラーは表示させずに、その代わりに表示させる値を指定します。

今回は、1つ目のセルの値がエラーの場合は空白「””」を表示させるよう、2つ目の引数に空白「””」を指定しています。

【STEP.7】STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーする

STEP.6の計算式が埋め込まれたセルを、表の最後の行位置までコピーします。

今回例で挙げたExcelファイルで言うと、セル「K9」のセルを「K28」までコピーします。

コピーすると、J列の値と一致するA列の値の店舗データが右の表に出力されました。
(⇒項番が3、19(顧客名が自然食品スーパー、かつ商品名がナポリタンの素)の店舗データ)

検証

セルA3に顧客名を入力すると、左側の表にある該当する顧客の店舗データが右側に出力されるのかを検証してみます。

検証した結果は、次の通りです。

テスト①：顧客名が自然食品スーパーでかつ、商品名がナポリタンの素を選んだ場合

顧客名が自然食品スーパーでかつ、商品名がナポリタンの素を選んだ場合、顧客名が自然食品スーパーでかつ、商品名がナポリタンの素の店舗データを右側の表に出力しています。

テスト②：顧客名がオーガニックフードでかつ、商品名が焼きおにぎりを選んだ場合

顧客名がオーガニックフードでかつ、商品名が焼きおにぎりを選んだ場合、顧客名がオーガニックフードでかつ、商品名が焼きおにぎりの店舗データを右側の表に出力しています。

テスト③：顧客名が自然食品スーパーでかつ、商品名が焼きおにぎりを選んだ場合

顧客名が自然食品スーパーでかつ、商品名が焼きおにぎりを選んだ場合、顧客名が自然食品スーパーでかつ、商品名が焼きおにぎりの店舗データを右側の表に出力しています。

最後に

本記事では、Index関数とMatch関数を使って表から検索対象のデータを抽出する方法についてご説明しました。

Index関数とMatch関数を組み合わせて使うことで、表から検索の対象データを抽出することができます。

なお今回の内容は、取得する条件が複数で、条件に合うデータが複数ある場合です。

例えば、表から「自然食品スーパー」「ナポリタンの素」という顧客名と商品名に該当する店舗データを抽出したい場合に、表には「自然食品スーパー」「ナポリタンの素」という顧客名と商品名の店舗データが複数存在する場合そのすべてを抽出する、といったケースです。

以上のようなデータを抽出したい場合は、詳しくは本記事の内容を参考にしてみてくださいね。

Excelのスキル向上やExcelの基礎知識をしっかりと学びたいなら

Excelのスキルを習得したい、Excelの基礎知識をもっと理解したい、そう考えているなら「無期限サポート付きExcel講座【すごい改善】」がおすすめです。

Excelのスキルの基礎を身につけるなら【すごい改善】で無期限サポート付きがあるので、これで「Excelのスキルや基礎」を学ぶのにおすすめですよ。

→　受講後、何度でも無期限でメールで質問できるアフターサポートがついているExcelマスター講座はこちら