XLOOKUPに負けない！INDEX & MATCH関数の動的活用術
導入
INDEX関数は長年にわたってExcelユーザーに支持されてきた古いながらも強力な関数です。その最大の特徴は、データの位置関係に依存せずに、任意の値を柔軟に取得できる点です。特に、MATCH関数と組み合わせることで動的な参照を実現でき、多くの業務で効率化を図るツールとして活用されてきました。しかし、XLOOKUPの登場により、INDEX関数の役割は次第に薄れていると感じる方もいるかもしれません。これは事実ですが、INDEX関数は配列を想像しながら式を組み立てるという思考回路は、スピル関数やPower Queryを扱う現代Excelにおいて非常に役に立ちます。
本資料では、INDEX関数とMATCH関数の基本的な使い方から、その組み合わせで可能になる高度な活用例、さらにはXLOOKUPによる代替方法までを詳しく解説します。これを通じて、INDEX関数の可能性を再評価し、どのように現代のExcel作業においても有効活用できるかを見つめ直していきます。また、XLOOKUPが提供する新たな利便性についても触れ、適材適所で両者を使い分けるための指針を提供します。
INDEX、MATCH関数のおさらい
INDEX関数は、特定の配列または、範囲から、指定した行番号と列番号で指定された要素の配列を返す関数です。ここで「返す」とは、数学でいうところの関数で計算結果を出すという意味です。
関数の入力方法は、
=INDEX(配列または範囲, 行番号, 列番号)
です。引数が3つあります。ここで引数とは、入力項目という意味です。行または列が1つしかない場合は、引数を省略できます。
次に使用例を示します。下の例では、テニスのグランドスラムの優勝者を年ごとに並べています。この例の中でB１セルには
=INDEX(B4:E14,2,3)
を入力しています。B4:E14は、優勝者の名前の配列です。その中で、2行目（第1引数）、3列（第2引数）の交差点の値（ここではあえて値と言います）を返す式となっています。この表で、2行目は2015年、3列目はウィンブルドンでつまり2015年のウィンブルドン優勝者であるノバク・ジョコビッチが返されます。ここでのポイントは、行番号と列番号はExcelシート内の番号ではなく、配列の中の番号ということです。つまりD5セルを表す、５行４列ではないということです。
MATCH関数
次にMATCH関数です。MATCH関数は任意の行範囲や列範囲の中で、指定した値を検索し、その範囲内での位置を返してくれる関数です。
関数の入力方法は、
＝MATCH(検査値, 検査範囲, [照合の型])
です。3つ目の引数は、省略可能で省略した場合は、「完全一致」が適用されます。以下はINDEX関数の例と同様のテニスのグランドスラムの優勝者を年ごとに並べた表です。今、B1セルには
=MATCH("ウィンブルドン",B3:E3)
という式を入れています。「ウィンブルドン」を検索値（第1引数）、グランスラムの名前を並べたB3:E3を検索範囲（第2引数）に選択します。そして、第3引数として完全一致（つまり、0）を指定すると、3を返します。つまり、ウィンブルドンは、横に並べたグランドスラムの中で3番目に出てきているということです。
INDEX関数とMATCH関数の組み合わせ
上記から
=INDEX(B4:E14,2,3)
の第3引数は
=MATCH("ウィンブルドン",A3:E3)に置換しても同じ結果が出ることがわかります。なぜなら
=MATCH("ウィンブルドン",A3:E3)
の答えは3で
=INDEX(B4:E14,2,3)
の第3引数も同じ値だからです。
同様に
=INDEX(B4:E14,2,3)
の第2引数もMATCH関数に置き換えることができます。つまり、
=MATCH("2015年",A4:A14,0)
です。ここで特筆すべきはMATCH関数が縦方向にも使えるという点です。
これらを合わせると
=INDEX(B4:E14,2,3)
は
=INDEX(B4:E14,MATCH("2015年",A4:A14,0),MATCH("ウィンブルドン",B3:E3,0))
と書き直すことができます。
動的な式の導入
次にこの式は動的にすることができます。この式で検索値を式の外に出してセルに入力し、そのセルをMATCH関数の引数に設定します。これをすると、検索値を式の外で変更することができ、動的なモジュールを作ることができます。例えば、開催年をＥ2のセルに置き、グランドスラム名をD2に置き換えます。そして、それぞれのセルに入力規則を設定してプルダウンをつけてあげれば、検索モジュールが出来上がります。（式はB1に入力されています。）
このように、INDEX関数とMATCH関数を使うと、自由度の高い検索機能を実装できます。また、上記の例のように、引数を関数に置き換えて、さらにその関数の引数を関数外のセルに出すというのは、動的なEXCELファイルと作るうえでとても大事な考え方です。ちなみにこの機能はXLOOKUPでも表現することは可能です。
INDEX関数とMATCH関数の可能性
ここまでINDEX関数とMATCH関数の基本的な使い方を説明してきました。ここからは、この2つの関数の組み合わせで広がる可能性について書いていきたいと思います。
INDEX関数の戻り値とは
INDEX関数とMATCH関数の組み合わせの可能性を探るために大事なのは、INDEX関数が何なのかをしっかりと理解することです。ここで原点に立ち返ってINDEX関数がどういうものなのか、Microsoft公式の説明を引用します。
------------------引用----------------------
INDEX 関数には、次の 2 つの使い方があります。
§ 指定したセルまたはセルの配列の値を求める場合は、「配列形式」を参照してください。
§ 指定したセルの参照を求める場合は、「セル範囲形式」を参照してください。
配列形式
説明
行番号と列番号で指定されるテーブルまたは配列の要素の値を返します。
INDEX 関数の 1 番目の引数が配列定数のときは、配列形式を使います。
セル範囲形式
説明
指定された行と列が交差する位置にあるセルの参照を返します。 隣接しない複数のセル範囲を指定した場合、その中から任意の領域を選択できます。
(出典)
https://support.microsoft.com/ja-jp/office/index-%E9%96%A2%E6%95%B0-a5dcf0dd-996d-40a4-a822-b56b061328bd
--------------------------------------------
ここで、配列とは例えば{1,2,3,4;21,34,45,56}などの実際の数字の羅列（この例では、2行4列の行列）で、セル範囲とはA1:B4のような表現を指します。そして、ここでわかるのは、「INDEX、MATCH関数のおさらい」の段で使用したのは2段目の「セル範囲形式」であるということです。
では、セル範囲形式の説明に書かれている文言をよりよく読んでみましょう。
「指定された行と列が交差する位置にあるセルの参照を返します。」
そうです。この形式では「セルの参照」を返しているのです。これは何を意味しているかというと、前述のグランドスラムの例を用いるならば、「2024年」、「ウィンブルドン」という引数に対して「カルロス・アルカラス」という「値」を戻り値として返しているように見えますが、実際は「D14」というセルを返しており、D14にカルロス・アルカラスが入力されているので、最終的にカルロス・アルカラスが表示されていることです。この特性を理解することこそがINDEX関数の可能性を広げることになります。
範囲の指定にINDEX関数を使う
それでは、INDEX関数とMATCH関数のさらなる可能性に目を向けて行きましょう。
前述の通り、INDEX関数は、セルの参照を返します。ここでは、セルの参照をセルの場所と呼びたいと思います。ここで思い出したいのは、範囲を指定するときには、
「セルの場所①:セルの場所②」
と書くということです。そして、セルの場所というのは、前述の通り、INDEX関数で表現ができます。つまり、上記の範囲は、
「INDEX関数の戻り値①:INDEX関数の戻り値②」
と書き換えることができます。
例えば、以下の例で、4行1列目のセルから4行5列目までの範囲を参照したい場合は、
通常は、A5:E5
と書きます。
しかし、これはINDEX関数を用いても記述することができます。
それぞれのセルの場所を書き換えると、
A5 -> INDEX(A5:L5, 1, 1)
E5 -> INDEX(A5:L5, 1, 5)
となります。さらに、ここにMATCH関数を組み込んでみましょう。
A5 -> INDEX(A5:L5, 1, MATCH(“1月1日”, A4:L4, 0))
E5 -> INDEX(A5:L5, 1, MATCH(“5月1日”, A4:L4, 0))
となります。では、次にMATCH関数の引数を式の外に出してあげましょう。ここでは、A2に1月をB2に5月を入力して、その2つを参照する式に変更します。
A5 -> INDEX(A5:L5, 1, MATCH(A2, A4:L4, 0))
E5 -> INDEX(A5:L5, 1, MATCH(B2, A4:L4, 0))
となります。つまりA5:E5は最終的に
INDEX(A5:L5, 1, MATCH(A2, A4:L4, 0)) : INDEX(A5:L5, 1, MATCH(B2, A4:L4, 0))
と書き換える事ができるということです。
さらにMATCH関数の引数で式外にあるA2、B2の値を変えることで、INDEX関数の返す値が変わるという、自由度を獲得できました。このケースで、A2、B2に入力規則を設定するとこのファイルを利用する人にとってやさしい仕組みになります。
さて、ここまでの内容で気づいた方もいらっしゃるかと思いますが、この方法は、範囲を引数にする関数に入れ込むことができます。例えば、SUM関数です。上の例で、1月から5月までの数値の累計を計算したい場合は、
SUM(INDEX(A5:L5, 1, MATCH(A2, A4:L4, 0)) : INDEX(A5:L5, 1, MATCH(B2, A4:L4, 0)))
となります。計算結果は、A8とA9にある通り、合致しており、上記の式が、１月～５月までの数字を正しく足し上げていることが確認できます。
繰り返しになりますが、起点となる1月という引数と5月という引数は、式外のセルA2、B2に入力しています。つまり、A2 とB2の入力内容を変えれば、簡単にSUM式の参照範囲を変えることができるわけです。想像してみてください。マネジメントに表やグラフを見せながら説明しているときに、「1月から5月までの合計はわかったけど1月から3月は？」と聞かれたときにその場でSUM式を入力し直すのは時間がかかりますし焦りませんか？さらに他の期間の数字も見たいと言われたら、さらに焦ります。そういうときに、ここで紹介した式を作っておき、A2、B2に入力規則を設定し1月から12月までの値がプルダウンで選択できるようにしておけば、SUM式を入力し直すことなく、マネジメントの要望を瞬時に叶えることができるようになります。
いかがでしょうか。この自由度の高さには、ワクワクしませんか？これがINDEX関数とMATCH関数の可能性の1つです。
ただし、これも実はXLOOKUPでも実装できてしまうのです。関数が進化していることは喜ばしいことですが、古き良きINDEX、MATCHを愛する身としては寂しいものです。ここで、XLOOKUPの同様の特性についてもご説明します。再度、Microsoft公式のページを見てみましょう。
---------引用---------
書式
XLOOKUP 関数は、範囲または配列を検索し、最初に見つかった一致に対応する項目を返します。 一致するものがない場合、XLOOKUP は最も近い (近似) 一致を返します。
（中略）
戻り配列
返す配列または範囲
---------
ということで、XLOOKUPで返されるのは、値ではなく範囲です。この点はVLOOKUPと異なる点です。なぜならVLOOKUPは戻り値として範囲ではなく「値」を返すためです。
前述の例を用いるならば具体的には以下のような式となります。
=SUM(XLOOKUP(A2,A4:L4,A5:L5):XLOOKUP(B2,A4:L4,A5:L5))
INDEX、MATCHの計算と同じ結果になることが確認できました。
ここで特筆すべきは、今回使用したXLOOKUPの検索範囲・戻り範囲が列方向（横に伸びている）範囲という点です。VLOOKUPでは行方向（縦に伸びる）範囲だけでした。列方向を引数に指定するには、HLOOKUPという別の関数が必要でした。XLOOKUPは両方の特性を持っているということです。イメージとしてはドラゴンクエストでいう、魔法使いと僧侶の両方の技を覚える賢者のような存在がXLOOKUPということになります。
上記の通り、SUM関数やAVERAGE関数など範囲をして計算する関数とINDEX関数やXLOOKUP関数を用いると動的な計算範囲を作ることができる特性はとても強力です。特に長期の実績予測などのモデルにおいて感応度分析を行うときにもとても便利です。例えば、製薬業界において将来薬価改定が予想されるが商品があるが、具体的にいつ改定されるかわからないときに、複数のパターンを見たいということがあります。想定通りのベースケース、想定よりも早いストレスケース、想定よりも遅いアップサイドケースなどです。それらを計算するのに毎回、式の参照範囲の変更や、新しいシートを作るのは労力がかかり、データ量は多くなり、かつ同じようなシートや式が乱立し混乱を招きます。それよりも今回紹介したように計算範囲のラベルとなる日付をプルダウン式のインプットとして外だしすれば、素早く、かつ、量力なくそれぞれのケースの計算ができるようになります。
COUNTA関数と組み合わせる
次にINDEX関数の第２、３引数が数値である点に着目して、COUNTA関数との組み合わせで可能になる範囲選択について紹介したいと思います。
以下のスクリーンショットのようなデータテーブルを想定します。縦にデータがならび、XLOOKUPのキーとなるようなデータがなく、今後もデータが追加されていくようなデータテーブルです。このテーブルの一番下の行のデータを別のシートで表示したい場合、どうすればよいでしょうか。
これはINDEX関数とCOUNTA関数の組み合わせで実現が可能です。特にテーブルにおいて列を構造化参照することでデータが新しく入力された際も参照対象が動的に動き、常に最新の情報を得ることができます。ここで構造化参照とは、Excelのテーブル機能において、テーブルや列の名前を使用してセル参照を省略する機能です。テーブル内の列を参照する数式を立てると、テーブルを作成した際に自動的に定義された名前が自動的に利用されるので、データをテーブルかしておけば、特別な操作をしなくても関数に適用されます。
さて、一番下の段のデータを取得する方法ですが、まずは動的な設定をしない方法で取得する方法を考えます。今回は、15行目が最終行なので戻り値として返したいのは
A15:C15
となります。
これをINDEX関数とCOUNTA関数を用いて表現します。INDEX関数の第２引数を考えてみましょう。第２引数は配列内の行番号でした。この表において、配列内の行番号は行方向のデータ数と合致します。このテーブルの名前を「テーブル」とした場合、これは
COUNTA(テーブル[日付])
となります。従い、A15は
INDEX(テーブル[日付],COUNTA(テーブル[日付]))
同様にC15は
INDEX(テーブル[販売数],COUNTA(テーブル[日付]))
となります。よって、A15:C15は以下のように変換できます。
=INDEX(テーブル[日付],COUNTA(テーブル[日付])):INDEX(テーブル[販売数],COUNTA(テーブル[日付]))
このように表現を変えると、構造化参照なので静的であった範囲の参照が動的になり、結果としてテーブルに新たにデータが足されていったとしても常に最終行を取得することができます。
こちらに関しては、XLOOKUP関数にはできないことです。例としては、日時で累計実績がテーブルに追加されているケースです。累計実績はダッシュボード上でその遷移をグラフなどにして見ることが多いと思いますが、その日の累計実績だけを見たいというケースも出てきます。そういったときにこのINDEX関数とCOUNTA関数の組み合わせが役に立ちます。
まとめ
INDEX関数とMATCH関数はその自由度の高さから多くの業務に役立つツールです。特に、データの動的な参照や範囲の柔軟な設定、さらには検索値が存在しないケースでも利用できる点が特徴です。一方で、XLOOKUPはそのシンプルさと多機能性から、新規ユーザーにとって使いやすい選択肢となります。
本資料を通じて、両者の特性を理解し、目的に応じて適切に使い分けるための知識を深めていただければ幸いです。