INDEX関数で縦横にスピらせる

  1. Home
  2. Excel
  3. Excel Tips

先日の名古屋セミナーで、常連の佐々木さんと雑談していました。

佐「XLOOKUP関数って縦にスピらないじゃないですか」
田「そうですね～(Array of Arrayの件かな？)」
佐「でも、なんとかしたくて」
田「ふむ(LAMBDA使ったの？あれ難しいよ)」
佐「ネットで調べたらINDEX+MATCHでやる方法が書いてあって」
田「へぇ、珍しいですね」
佐「できたんですけど、意味が分からなくて…」

みたいな話でした。なので、今回はINDEX+MATCHで縦横にスピらせるやり方を解説します。

目的が異なる

まず、しっかり認識していただきたいことがあります。下図のように、XLOOKUP関数を単体で縦横にスピらせることはできません。

なので、XLOOKUP関数の結果を縦横にスピらせるなら、下図のように超難しくなります。

これは、XLOOKUP関数の結果を縦横にスピらせるのが目的です。XLOOKUP関数を使うのが前提なんです。でも、XLOOKUP関数を使わないで、別の関数で同じ結果を得ることも可能です。なので今回は、INDEX+MATCHで同じ結果を得る方法です。間違っても「どっちがいい」など意味のない対比はしないでください。ネットでは、たびたび「VLOOKUP vs INDEX+MATCH」みたいな議論を見かけますが、目にするたびに「くだらない」って感じます。両者には、それぞれメリットとデメリットがあります。どちらか一方が他方を上回るような話ではありません。ケースバイケースで使い分ければいいだけです。

INDEX関数の動作

まずは、INDEX関数について。簡単なケースで動作を確認しましょう。

INDEX関数の引数は「INDEX(範囲,行,列)」です。引数「範囲」内で、引数「行」と引数「列」が交差するセルを返します。これが基本的な動作です。では、引数に配列を指定してみましょう。

引数に配列を指定すると、INDEX関数はスピります。もちろん、これは行も同様です。

非連続の「行」や「列」でもOKです。

配列を指定している「{2,3,4}」や「{4;1;6}」で、各要素の区切りが「,」や「;」のように異なりますが、ここはあまり悩まないでください。ちなみに「,」で区切られた配列は横方向の"一次元配列"で、「;」で区切られてるのは縦方向の"二次元配列"を表しています。上の2例は、基本中の基本中ですから、確実にイメージできるまで先に進まないでください。

さて、ここでは、下図のような結果を得たいとします。

ということは、下図のような式を作れればいいです。

この、ふたつの配列を作ってくれるのがMATCH関数[*1(下記参照)]です。

MATCH関数は配列を返す

MATCH関数の引数は「MATCH(検査値,範囲,0)」です。最後の「0」は"完全一致で探す"という意味です。ここは他にも「1」と「-1」を指定できますが、普通は「0」です。毎回必ず「0」を指定すると覚えていいです。簡単なケースで確認しましょう。

では、引数「検査値」(ここではセルF2)に、複数のセルを指定します。

MATCH関数は、引数「検査値」に複数のセルを指定するとスピります。そして、その結果は配列です。今回は「{6;1;3}」です。これ、INDEX関数に指定したいやつですよね。では最後に、横方向の配列を作ります。これも考え方は一緒です。

今回でしたら「{2,3,4}」のように、数式内に配列を直接記述してもいいのですが、ここは汎用性を考慮してMATCH関数で調べます。欲しい結果は下図です。

それぞれの配列を、MATCH関数で作ります。

以上です。ネット上でよく見かける「VLOOKUP関数はダメだ！使えない！絶対INDEX+MATCHの方がいい！」と主張するみなさん、そんなに「INDEX+MATCH」がお好きでしたら、これくらいは軽くやってくださいね。みなさんの大好きなINDEX関数とMATCH関数ですから。

(補足)

(*1) 私はよくMATCH関数を使うのですが、ときどき「田中さんはXMATCH関数を使わないんですか？」と聞かれます。う～ん、あまり使いませんね。てゆーか、使った記憶がありませんｗ 理由は明白です。ほとんどのケースは、従来のMATCH関数で事足りるからです。必ずしも新しい関数が優れているとは限りません。XMATCH関数でなければ実現できない場合は使いますけど、MATCH関数で済むのなら、それでいいじゃないですか。「いやいや、同じ結果なら新しい関数の方がいいっしょ」と言うのなら、もうSUM関数は使わないでAGGREGATE関数を使ってくださいね。どちらも合計を計算できます。