【INDEX】【XMATCH】これぞExcelの醍醐味？！ 表から欲しいデータを取り出す！

今回は、Excelを使うなら絶対にマスターしてほしいINDEX関数です。
これを知らずして「Excelって便利〜」とか言うなよ！ てくらいワタシは多用する関数なので、熱く語っちゃうぞ。

INDEX関数｜表の行・列の見出しからデータを探す

INDEX関数は、見出しから探したいデータを検索して返してくれる関数です。

ん？ 似たような関数がほかにもあったような？
その通り、XLOOKUP関数も指定した範囲から条件に合うデータを返してくれる関数でした。

しかし、XLOOKUP関数だと、検索範囲と返す範囲を縦か横かでしか指定できないんですね。
VLOOKUPやHLOOKUPを使っていた世代だと「いや、縦も横も検索できるなんてスゴいよアンタ！」とは思うのですが。
しかし、Excelだとついつい行にも列にも見出しがある表を作りがちなので、「縦も横も探したい…」となることも多いのです。

…ホントはこのマトリックスタイプは、データを扱ううえではあまり筋のよろしくない形式なのですが（上の例程度ならさほど問題ないけど）、何しろExcelがマトリックスが得意なうえに人間にとっても視認性がよろしいので、今日も世界中でマトリックスタイプのデータは続々と量産されていることでしょう。
それに対応できるのがINDEX関数です。

イメージとしては、こちらをご覧ください。

太い枠線で囲った部分が「配列（データを検索するセルの範囲のこと）」。
配列の左端が行番号を探す範囲。
配列の上端が列番号を探す範囲。
となります。
上の画像で赤線とピンクで示した箇所が、「行を◯番と指定、列を◯番と指定、それが交差した箇所のデータを返す」というINDEX関数の探し方・返し方になります。

先程の例に戻りましょう。
この表から、例えば商品ID「P-003」の「在庫数」を探したいとします。

「P-003」は4行目、「在庫数」は4列目にあります。
（列名はアルファベット表記ですが、こういうときの検索では数字で◯番目となります。）
この場合の式はこんなカンジ。

表の中から無事にデータを取り出せました。

INDEX関数は、XMATCH関数と合わせて使ってこそ！

だがしかし！
これくらいの例なら「えーと、P-003は、いち、に、…4行目ですね」と数えることも可能ですが、何千何万行になるデータ相手ではそんなことやってられません。

そこで使用するのがXMATCH関数です。

こちらのG2のセルに、「P-003」の「在庫数」を表示させましょう。

まずはG2のセルに「=INDEX(」と配列を入力します。
カンマを打つと「次は行番号を指定しなさいよ」と教えてくれます。

ここでXMATCH関数の出番です。
「P-003」ってヤツを配列の左端から探したいので、XMATCHで検索値を「P-003」が入力されているF2セルを指定し、検索範囲を配列の左端に指定します。

同じように列番号もXMATCH関数で探します。
検索値は「在庫数」が入っているセルを、検索範囲は配列の上端を指定します。

こうすると何がいいかというと、検索したい条件を「P-001」の「商品名」に変えたいわってときに、XMATCH関数の検索値として指定したセルを弄るだけで済むことです。行や列の番号を数え直して数式を修正する必要はないわけですね。

また、ほかのデータも表から引っ張ってきたいというときも楽ちん。
元の数式の相対参照・絶対参照を上手いこと設定してやって、コピーするだけです。

INDEX関数は便利だけどXMATCH関数とセットでないと真価を発揮しない。
XMATC関数も単体で使用することはあまりない。
というように、セットで覚えてほしい関数です。

かつてはMATCH関数というものを使っておったのじゃよ…

実はXMATCH関数も比較的新しい関数です。

XMATCH関数誕生前は何を使っていたかというと、MATCH関数というヤツを頼りにしていました。
「X〜関数」みたいな名称の関数は、割と新しい関数なんですよ。これ以上バージョンアップしたら、名称どうするんだろね。

MATCH関数も検索値と検索範囲を指定するという関数なのですが、ちょっと異なるのは「照合の種類」てものを指定しなくちゃいけないこと。検索値と完全に一致するものを、あるいは最大値または最小値を探すのか、ていう指定が必要なのです。
これをミスると思うような返しにならなくて「？？？」となることがたまにありました。

ホントはXMATCH関数でも「完全一致を探すのか」「検索する方向は」なんてことを指定できます。
できますが、省略可能です。
そんなもん、元々「完全一致」を「先頭から末尾に向かって」検索するというつもりでデータを整備しときゃいいのさ。

しかしMATCH関数は照合の種類の指定を省略できないから、引数がひとつ多くなるんですね。

引数は少ない方が視認性がよろしいので、XMATCH誕生以降はMATCH関数を使ったことがありません。

INDEX+XMATCH関数を使いこなせない人は、結構いる…？

ワタシは「この世のExcelにマトリックスデータがある限り」とばかりにINDEX+XMATCHを多用しているのですが、割とこの関数の組み合わせを敬遠している人はいて、Excelでゴリゴリに作業しない人には「うわぁ、またワケのわからんことしている…」という扱いを受けます。

となってしまった要因のひとつは、MATCH関数の引数の多さとワケわからなさだったんじゃないかなあ。と、勝手に推測しています。

説明すると、マトリックスの中から行と列を指定するだけだから「ああ、なるほど」と理解してもらえるのですが、いざMATCH関数を書くと、最後の「照合の種類」がピンとこなくて「？？？」となる人が多い。
「いいから0（完全一致）を入れておいてください」と言っても「なんだそれ…」となる気分はわからなくもないし、出来上がった数式に謎の「0」が入ると「なんだこれ…」となる気分も理解できなくもないです。

こうしてExcelほんのり触る勢が敬遠しているうちにXMATCH関数が誕生してワケわからん引数は不要になったわけですが、この辺で躓いた人がXMATCHが爆誕したからといってExcelゴリゴリ勢に転換するでもなく、なんとなくここら辺がExcelの得意・不得意の分岐点なのかなー…なんてことを感じます。

INDEX関数、複数の検索をしたい場合はどうする？

と、INDEX+XMATCH関数は便利で最強なのですが、割と困る場面もあります。

例えば次のようなデータで「会社別のクラウド事業の売上高を比較したい」なんてニーズがあったとします。

「えーと、クラウド事業を検索したいから配列はC列からで…、あ、でもそうすると会社別に検索できないな…」となります。

こんなとき、やり方はいろいろありますが、ワタシはもうあんまり考えても仕方ないや〜ってことで、作業用の行や列を追加します。
んで、そこに検索したい条件を全部織り込んでしまう。

今回は会社別・事業部別にデータを拾いたいので、作業列を「会社名_事業部名」としました。
要素の真ん中に「_」を入れているのは視認性が良くなったりと扱いやすくなるからですが、なくても機能はするのでお好みで。

んで、検索結果を表示させるために、こんな箱を用意しますが。

こっちの箱にも作業用にこんな仕込みをしておきます。

こうすると、配列の左端は「作業」のセルを検索させりゃいいってことで、INDEX+XMATCHが使いやすくなります。

おや、謎の「#N/A」なんて表記が出ましたね。
これは「該当するデータがないよ（Not Available）」という意味のエラーです。
ついでにこの解消方法についても紹介しておきましょう。

IFERROR関数｜もしエラーの場合は

エラー表示になったということは何かしらの誤りがあるのでそれを解消せねばならんのですが、今回の場合は、単にTヨタやR天やGーグルにはクラウド事業がないというだけなので、「データがなければゼロでいいや」で済ませることにします。

その前にエラー表示について。
該当のセルの左上に緑の三角がついています。
これは「エラーだよ」と教えてくれる表示で、こんなメッセージが出ます。

メッセージ自体は大したことは記載されていないので役に立ったことはないのですが、「エラーを無視する」を選択すると緑の三角が消えます。
見た目に鬱陶しい場合は「エラーを無視する」で緑の三角を消してもいいし、そもそもこの緑の三角が出ないように設定することも可能ですが、ワタシはよっぽど人様にお見せする資料でもなければ緑の三角はきちんと出して残しておきたい派です。
なぜなら、たまにぽろっと出るエラーがあったりするときに見つけやすいからです。

それはともかくとして、今回はこのエラーが出るセルを「0」という表示に変更しましょう。

エラーに関してよく使う関数は、IFERROR関数です。

作ってエラーになっちゃった数式をひとつめの引数に持ってきて、２つ目の引数を「0」とするだけです。

なお、「0 」ではなく空欄にしたい場合は「””」を返す値にします。

「これ、エラーになっちゃうか〜〜。ま、大したエラーじゃないからゼロ（非表示）にしとくか！」てときに便利な関数なので、覚えておいてください。

おわりに

• INDEX関数とXMATCH関数はセットで使えるようにしてね
• INDEX関数を使いづらい配列は、作業用に行や列の追加も検討してみてね
• ちょっとしたエラーはIFERROR関数でごまかしちゃえ

繰り返しになりますが、マトリックスデータがわんさか出てくるExcelでは、INDEX関数は避けて通れません。
慣れるとそんなに難しい関数ではないので、ぜひ習得してください。