特定条件に合致した値を返すINDEX関数MATCH関数の組み合わせ-GSS/Excel/関数

特定の条件に合致した値を抽出するINDEX関数とMATCH関数の組み合わせについて説明していきます。

よくVLOOKUP関数を使って条件に合致した値を取り出すということをされるのですが、VLOOKUP関数にはデメリットもあります。

そのあたりも説明していきますね。

使い方

=INDEX（抽出したい範囲,MATCH(検索値,検索範囲,照合の種類)）

演習問題を使って具体的に説明していきます。

正解例

[AE8]=INDEX(Z7:Z15,MATCH(AD8,AB7:AB15,0))

今回の問題は「1位のメンバーが一番最初に案件獲得をした日付とクライアント名をすべて抽出する。日付はYYYY/MM/DDの形式で表示する」です。

特定のデータリストから1位になっている人の名前を抽出するので、INDEX関数とMATCH関数の組み合わせを使います。

データリストはスプレッドシートの[Z7:AB15]の範囲です。

[AD8]に記載されているのはランク。1位を示しています。[AE8]にはメンバーをいれる列があります。ここに先ほどの数式をいれると、名前がでます。

まず関数内の一つ目の指定は、「抽出したい範囲（参照したい範囲）」です。

メンバーの名前をいれたいので、[Z7:Z15]を入れます。

次に「検索値」です。こちらは、[AD8]に入っている「1」が検索値になるので、[AD8]と入れます。

次に、検索範囲を入れます。検索範囲というのは、検索値が含まれている列を指定します。今回は、[AB7:AB15]までが検索範囲になります。

最後の「照合の種類」は、手前のMATCH関数の検索値と検索範囲の称号の種類を表します。

1の場合、検索値以下の最大値

0の場合、検索値に一致する値のみ

-1の場合、検索値以上の最小値

上記のような種類があり、検索値と一致するものを抽出したい、条件一致のみを見たい場合は、0を入れれば問題ありません。今回も0をいれます。

まとめると[AE8]=INDEX(Z7:Z15,MATCH(AD8,AB7:AB15,0)) になります。

VLOOKUP関数とINDEX関数＋MATCH関数の組み合わせの違い

条件に合致した値をデータリストの中から抽出する方法にVLOOKUP関数があります。VLOOKUP関数は認知度も高く使っている人も多いと思います。

ただ私はVLOOKUP関数はほとんど使いません。デメリットがあるからです。

VLOOKUP関数のデメリットとは、数式の中で、「列番号」を指定するために起こることです。

例えばスプレッドシートを使っていくうちに、データリストで列を追加したくなることってありませんか？

日付と売り上げが並んでいるデータリストがあり、日付の隣に曜日がでるように列を追加したとします。別のシートなどで、特定の日付の売り上げを参照できるようにVLOOKUP関数でデータを作っていたとします。

すると、検索値から2番目のデータを持ってくるようにVLOOKUP関数で指定していた場合、日付列の隣が曜日の列に変わってしまうので、売り上げの値が参照できなくなってしまいます。

実際にやってみましょう。

実演

結果がでる[F]列にはそれぞれ、VLOOKUP関数とINDEX関数とMATCH関数の組み合わせの2種類で関数が入っています。

[F2]=INDEX(B2:B5,MATCH(E2,A2:A5,0))

[F3]=VLOOKUP(E3,A2:B5,2)

どちらも「100000」と出ていて間違っていないことがわかります。

この状態で、曜日の列を追加してみます。

B列に曜日を追加しました。

するとどうでしょうか。結果を示すセルで、INDEX関数＋MATCH関数の組み合わせは「100000」のままですが、VLOOKUP関数で記述したものは「火曜日」になってしまっています。

これはVLOOKUP関数の数式が、検索値がある列から数えて2番目の値が「100000」から「火曜日」になってしまったため、抽出結果が変わってしまったということです。

スプレッドシートやエクセルファイルは、情報を追加削除しながら使いやすくなるように改変していくことが多いので、もしVLOOKUP関数ですべての抽出を行ってしまうと、列の追加削除のたびに指定する列番号を変える必要が出てしまいます。

最初からINDEX関数MATCH関数でつくっておけば、列が追加削除されても、検索対象と参照範囲さえ消えなければ抽出され続けるので、とても楽です。

まとめ

• INDEX関数とMATCH関数の組み合わせの使い所
  • データベース、データリストの中から特定条件に一致するデータを抽出したいとき
  • データの列を追加削除することが多くVLOOKUP関数を使いたくないとき
• 構文
  • =INDEX(抽出したい範囲,MATCH(検索値,検索範囲,照合の種類))
• 例文
  • =INDEX(B2:B5,MATCH(E2,A2:A5,0))