ある程度Excelを使いこなしている方なら、INDEX関数やMATCH関数を見聞きしたことがあるでしょう。
この2つの関数は、INDEX関数とMATCH関数の組み合わせはExcelの基本が出来ている方向けの応用関数です。とはいえ、マクロのように複雑な命令文があるわけではないので、覚えていて損はありません。
そこで今回の記事では、INDEX関数とMATCH関数の使い方について解説していきます。
ぜひ使い方をマスターして、業務効率化に役立ててくださいね。
目次
INDEX関数とは？
リストの中にあるデータを取り出すのに便利なのがINDEX関数です。
何万行もあるような大きな表からデータを転記したりするのに役立ちます。
まずはこのINDEX関数について、基本的な使い方をみていきましょう。
INDEX関数の使い方
大きな表から指定したセルのデータを、別の場所にも表示したい場面はよくありますよね。
そんなときはINDEX関数がおすすめです。セル内のデータが数値でもテキストでも抽出できるのがポイントです。
INDEX関数の使い方のコツは、これから説明する構文にあるように、表を「範囲」で設定し、データを取り出すセルを「行番号」と「列番号」で指定することです。
Excelがもともと持っているセル番号、たとえばB2など、データを取り出すセルを指定するわけではありません。
具体的には「範囲」で選択した表に対して、数値で行と列を指定します。
| INDEX(配列, 行番号, [列番号]) |
INDEX 関数の配列形式には、次の引数があります。
配列
セル範囲または配列定数を指定します。
配列が 1 行または 1 列のみの場合、それぞれ行番号または列番号を省略することができます。配列が複数行および複数列で構成され、行番号または列番号のどちらか一方しか指定されていない場合、配列の中にある行または列全体の配列が返されます。
行番号
列番号が存在する場合を除いて必ず指定してください。
その場合は、配列の中にある値を返す行を数値で指定します。
なお、行番号を省略する場合は列番号が必要になるので注意してください。
列番号
配列の中にあり、値を返す列を数値で指定します。 列番号は任意項目なので、省略する場合は行番号を指定してください。
行番号と列番号の両方の引数を使用している場合は、行番号と列番号の共通部分にあるセルの値が INDEX関数によって返されます。
なお、行番号と列番号には、配列内のセルを指定する必要があります。配列の範囲外のセルを指定すると、INDEX関数は #REF! エラーが表示されます。
また、行番号または列番号を 0 (ゼロ) に設定すると、列全体または行全体の値の配列がそれぞれ INDEX関数によって返されます。 配列として返される値を使用するには、INDEX関数を配列数式として入力します。
【注意】
Microsoft 365を使用している場合は、出力範囲の左上のセルに式を入力し ［Enter］ キーを押すと、式を動的な配列の数式として確定することができます。
それ以外の場合は最初に出力範囲を選択し、出力範囲の左上のセルに数式を入力し、Ctrl + Shift + Enter キーを押して確定し、従来の配列数式として数式を入力する必要があります。 自動で数式の先頭と末尾に中カッコが挿入されるので確認してください。
MATCH関数とは？
MATCH関数とは、指定した範囲内で探したい数値や文字列がどの位置にあるかを列・行番号で返す関数です。構文は次の通りです。
| MATCH(検索値, 検索範囲, [照合の種類]) |
上記のように、MATCH関数では引数を最大3つ使用します。
なお、引数とは、Excelの関数を使用する際に必要な情報です。関数が結果を返すための判断材料とイメージしてください。
続いて、関数名の後の括弧「()」内に入力します。次の章でさっそく使い方をみていきましょう。
※なお、任意の方向に動作し、既定で完全一致を返す MATCH関数の改善されたバージョンである新しいXMATCH関数というのもあります。
MATCH関数の使い方
MATCH関数の構成要素には次の引数があります。
検索値
検査範囲の中で照合する値を必ず指定してください。
たとえば、電話番号帳を使って友人の電話番号を調べるときには、検査値として友人の氏名を指定しますが、実際に検索するのは電話番号を使います。
文字列などの値を指定するときは「”文字列”」のように「”」ダブルクォーテーションで囲います。
また、検索値は第2引数である「検索範囲」の中から検索しますが、第3引数の照合の種類が「0」の場合、検査値に疑問符（?）やアスタリスク（*）などのワイルドカード文字が使用できます。そのため、検査値には値 (数値、文字列、または論理値)、またはこれらの値に対するセル参照を指定することが可能というわけです。
検索範囲
検索するセルの範囲を必ず指定してください。
照合の種類
照合の種類は省略可能です。 -1、0、1 の数値のいずれかを指定します。 照合の型には、検査範囲の中で検査値を探す方法を指定します。 この引数の既定値は 1 です。
VLOOKUPより簡単？INDEX関数とMATCH関数の組み合わせ
INDEX関数とMATCH関数を組み合わせることで、VLOOKUP関数よりもより詳細に検索することができるようになります。
それでは、INDEX関数とMATCH関数の組み合わせでなにができるかみてみましょう。
INDEX関数＋MATCH関数とは
INDEX関数＋MATCH関数とは、「条件に合うデータを抜き出す」ことができる関数の組み合わせです。文字であらわすと「MATCH関数で条件値に合う行を検索」し、「INDEX関数で抽出」する、です。
たとえば、INDEX+MATCH関数の利用シーンは
- 「社員ID」から「氏名」や「住所」を抽出する
- 「商品番号」をもとに「単価」を抜き出す
- 「製造ロットNo.」から「検収データ」を表示する
といった内容になります。
つまり、ユニークな（他にはない）値をもとに関連するデータや値を求めることです。
同じような動きをする関数にはVLOOKUP関数がありますが、場面によってはINDEX関数+MATCH関数でないとできないといったこともあり、これを機にぜひ覚えて頂きたい関数です。
組み合わせ方は3パターン
MATCH関数を入れるパターンは、次の通りです。
- INDEX関数の「行番号」
- INDEX関数の「列番号」
- INDEX関数の「行番号」と「列番号」
このうちよく使われるのが、一番上の「MATCH関数をINDEX関数の『行番号』に入れる」パターンです。
そして、この形だと「MATCH関数の検索は行（＝縦）方向」になります。
この使用頻度の高い、「行方向へ検索する」場合、構文は次の通りです。
| INDEX(配列,MATCH(検査値,検査範囲,照合の種類),列番号) |
このように、INDEX関数+MATCH関数に必要な項目（引数）は、「配列」、「検索値」、「検査範囲」、「照合の種類」、「列番号」の5種類です。
引数の入力ルール
1番目の配列には、4つのポイントがあります。
- 指定の形は「左上のセル番号:右下のセル番号」
- タイトル行は一般的に含めない
- 複数列複数行がよく使われるが、1列複数行も可能
- 数式をコピーする場合には「$」で絶対参照にする
たとえば、指定の形が「A4:E8」のケースです。
また、一般的にタイトル行は含めませんが、含めても構いません。そして、数式を他のセルにコピーして使う場合には、F4キーを1回押して「$」が4つ付く「絶対参照（コピーしても動かないように）」にします。
なお、配列の入力はマウスの左ドラッグ操作が簡単です。
2番目の検査値には、「検索したい値」を指定します。
・文字列、日付、時刻の場合
検査値を文字列や日付、時刻にする場合には、値を「”」ダブルクォーテーションで囲みます。
・検査値を数値やセル番号にする場合
そのまま入力すればOKです。なお、数式を他のセルにコピーする場合には、検査値もF4キーを1回押して「$」を2つ付けて絶対参照にします。
3番目の検査範囲には、「検査値を検索する範囲」を指定します。ポイントは配列同様に4つです。
- 指定の形は「上のセル番号:下のセル番号」
- 行の範囲を「配列」と合わせる
- 必ず１列で指定する
- 数式をコピーする場合には「$」で絶対参照にする
4番目の検索方法は、全部で3種類あります。
| 入力値 | 検索方法 | 条件 |
| 1 | 検査値以下の最大値で検索 | 検査範囲を昇順に並べ替えておくこと |
| 0 | 検査値との完全一致で検索 | なし |
| -1 | 検査値以上の最小値で検索 | 検査範囲を降順に並べ替えておくこと |
ほとんどの場合で「0」となり、これは「検査値との完全一致での検索」になります。
最後の列番号には、「配列の中で抽出したい列（列は横）の番号」を指定します。
列番号はあくまでも「配列における」で使用しているExcel自体の列番号ではありません。
つまり、「範囲」で指定した中で、「何番目の列がほしいか？」を入れればよいのです。
INDEX関数とMATCH関数と組み合わせて使う時、多くのケースで「数値」か、「数値の入ったセル番号」が入ります。ただし、MATCH関数を使うこともでき、その場合には列方向に検索することが可能です。
VLOOKUP関数との違いとは？
VLOOKUP関数は検査値に対して右に検索しますが、INDEX×MATCH関数は、検査値の左にも検索ができる、という違いがあります。
そのため、慣れれば、VLOOKUP関数より使いやすい関数なのです。
あわせて読みたい｜VLOOKUP関数とは？
最後にVLOOKUP関数をおさらいしておきましょう。
リストや表など、指定した範囲の内容を行ごとに検索する場合は、VLOOKUP関数を使用するのが一般的です。
たとえば、パソコン部品の価格を部品番号で検索するか、在庫のある店舗IDに基づいて店舗を検索するか、といった場合に重宝します。
その際に使用するVLOOKUP関数の構文は、次のようになります。
| VLOOKUP（検索値, 範囲, 列番号, 検索の型） |
前述した通り、VLOOKUP関数は、調べたい値 (結果) が、調べたい戻り値 (量) の左側に来るようにデータを並べることです。事前にデータを作る際にも、検索のキーとなる列を一番左に並べた経験のある方は多いでしょう。
VLOOKUP関数の構成要素には、次の4つの情報が必要になります。
- 検索する値、検索値とも呼ばれます。
- 検索値が含まれるセル範囲。 VLOOKUP が正常に機能するために、検索値は範囲の最初の列に必ず位置している必要があることに注意してください。 たとえば、検索値がセル C2 にある場合、範囲は C 列から始まる必要があります。
- 戻り値を含む範囲内の列番号。 たとえば、「B2:D11」を範囲として指定した場合、Bを最初の列、Cを2番目というように数えます。
- 4つめの引数［検索の型］は、検索する値が見つからない場合、どのように処理するかを決める引数です。「TRUE」または「FALSE」で指定し、省略した場合は「TRUE」と同じ扱いになります。
まとめ
いかがでしたでしょうか。
今回の記事では、INDEX関数とMATCH関数の組み合わせや、VLOOKUP関数のおさらいをしてきました。
Excel関数を使用すると、手作業とは比較にならないほど速く、正確にあらゆる計算を処理できます。
経理事務や営業事務などのオフィスワークの方々にとって、作業効率や正確性を向上させるうえで、関数の使用は必須です。
ただし、Excelには無数の関数が用意されていますし、Microsoft Officeのアップデートとともに新たな関数は次々と生まれます。
ですから、そのすべてを覚えることはできません。
まずは基本の関数を習得し、続いて、あなたの業務の内容に合わせて必要な関数を習得することをおすすめします。
本記事を参考に、ぜひ実務でお役立てくださいね！
