2017-08-01

【Excel】VLOOKUP 関数でキー列よりも左側の値を取得する方法

Excel で表の検索をする際に最も利用されているのが VLOOKUP関数だと思います。
ただ、この関数には『検索範囲の左端にキーがなければならない』という制限があるため使用できない場面も少なくありません。

今回はそんな制限をちょっとしたテクニックで回避する方法をご紹介します。

VLOOKUP 関数の制限とその回避方法

冒頭で述べたとおり、VLOOKUPはキー列より左側の値を取得することはできません。

例えば以下の表を見てみます。「社員番号」と「端末ID」がユニークになっていてます。VLOOKUPを使用すればこの表から「社員番号」をキーに「氏名」を取得することはできますが、「端末ID」をキーに「氏名」を取得することはできません

この制限を回避するには以下の方法が考えられます。

  1. キー列(端末ID)を左側に移動する
  2. 取得する列(氏名)を右側に移動する。
  3. INDEXOFFSETなどの別の関数を利用する。

この内1.と2.の方法は検索用の別の表を用意したり、表自体のレイアウトを変えたりしなければならないため通常は3.のINDEXOFFSETがよく使われます。

そして、これらの方法の他にも今回ご紹介する方法を使用すればVLOOKUPを使用してキー列よりも左側に値を取得することができます

VLOOKUP 関数でキー列より左側を取得するには

VLOOKUPでキー列より左側を取得するには以下のように引数を指定します。

=VLOOKUP(キー値, IF({TRUE, FALSE}, キー列, 値列), 2, FALSE)

先程の表だと以下のようになります。

=VLOOKUP(E2,IF({TRUE,FALSE},C2:C6,B2:B6),2,FALSE)

E2が検索する「端末ID」、C2:C6が「端末ID」列、B2:B6が「氏名」列

以下のサンプル画像を見ても分かるように、「端末ID」をキーに「端末ID」より左側にある「氏名」をちゃんと取得できています

キー列より左側が取得できる仕組み

ポイントはVLOOKUPの第2引数で指定しているIF関数です。論理式を指定する第1引数に普段見慣れない{TRUE,FALSE}という指定をしています。この{ }で囲まれた定数は配列定数と言われ、その名の通り配列を定数で指定する記述方法です。

言葉では分かりづらいと思うので実際にどのように振る舞うか見てみましょう。

IF関数の部分をがどの様な計算結果になるか検証してみます。数式の計算過程を表示する方法は以下の記事に詳しく書いています。

この記事にある通りIF({TRUE,FALSE},~,~)の部分を選択してF9キーを押下します。すると選択した箇所が下図の様になります。

この部分を抜粋すると以下のようになります。

{
"PPG316","Russell Westbrook";
"PPG291","James Harden";
"PPG289","Isaiah Thomas";
"PPG280","Anthony Davis";
"PPG273","DeMar DeRozan"
}

これは一見何を意味するのか分かりづらいですが、IF関数の第2、第3引数に指定した要素からなる二次元配列である事を示しています。二次元配列と言うと馴染みがないかも知れませんが、行・列からなる Excel のシートにある『表』と同じです。つまり、このIF関数の結果は以下の表と同じ内容になります。

この表ならキーとなる「端末ID」の列が左側にあるのでVLOOKUPで「氏名」を取得出来るという仕組みになっています。

試しに先程の表から「端末ID」と「氏名」のみを取り出した表を作成して、そこから検索を行うVLOOKUPを挿入してみます。

もちろん結果は同じになります。そして第2引数に指定した検索範囲(A2:B6)を選択して、計算過程を表示するF9キー押下します。すると先程と同様の二次元配列が表示されます。

つまり、IF関数の第1引数に配列定数を指定した場合は、その要素と要素数に応じて第2引数、第3引数からなる多次元配列を返すという事がわかります。

今回の例では{TRUE,FALSE}と指定しているので、第2引数、第3引数の配列となりますが、例えば{FALSE,TRUE}と指定すれば第3引数、第2引数の配列になりますし、{TRUE,FALSE,TRUE}と指定すれば第2引数、第3引数、第2引数からなる三次元配列になります。

また、Excel ではFALSE0TRUE0以外の数値に置き換える事が出来るので、{TRUE,FALSE}であれば{1,0}と書く事も出来ます。こっちの方がシンプルでいいですね。僕はいつもこっちの書き方をしています。

ちなみに、配列定数を引数に取って多次元配列を返す関数はIF関数の他にCHOOSE関数があります。
先程の表でCHOOSE関数を以下のように指定しても結果は同じになります。

まとめ

IF関数やCHOOSE関数は第1引数に配列定数を指定することができ、その要素に応じた多次元配列を返すことが出来ます。配列を返すこの機能は今回紹介したVLOOKUP以外にもいろいろと使えそうです。

標準の機能で出来なくても関数を組み合わせることによっていろいろな事が実現可能です。何か壁にぶち当たったら頭を柔らかくして対応したいですね。

ご不明点などありましたらお気軽にお問合せくださいませ:)

HAYs

関連記事

コメント2件

  • 金原 圭蔵 より:

    とても素晴らしいです。何か調べているとき、こちらのサイトにきました。そこで、VLOOKUP 関数でキー列よりも左側の値を取得する方法に目が留まりました。びっくりです。今までINDEXとMATCHしか使ったことがなく驚きです。IF関数の第二引数はそもそも
    F9を押してみると配列定数になっている。IF関数の第一引数に{TRUE,FALSE}を入れセル範囲を配列定数に変換しそこを検索している訳ですね。私の知る限りこの方法はどのサイトにも載ってないと思います。私はエクセル95時代から使ってますが、知りませんでした。感激しましたのでコメントさせていただいた次第です。

    • HAYs より:

      お褒めの言葉ありがとうございます!お役に立てたようで何よりです!

  • コメントを残す

    メールアドレスが公開されることはありません。 が付いている欄は必須項目です