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

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

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

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

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

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

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

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

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

そして、これらの方法の他にも今回ご紹介する方法を使用すれば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関数の部分をがどの様な計算結果になるか検証してみます。数式の計算過程を表示する方法は以下の記事に詳しく書いています。

Dev-Clips

 

2 Pockets

Excelで数式の計算過程を表示する方法

Excelで作業しているとやたら長ったらしい数式ってたまにありますよね。『この数式は関数がいっぱい書いてあるけど何でこの値を返すんだろう？』とか『あれ？数式がエラーになっちゃったけど原因はどこだ？』とか、こんな経験ありませんか？そういった複雑な数式を分析するのは一苦労ですX( そんな場合はこのエントリーでご紹介する方法で業務効率化しましょう:)数式の途中経過表示はF9キーExcel でF9キーといえば真っ先に「再計算」が浮かびますよね。もっとも、デフォルトでは再計算は自動になっているので明示的にF9キーを押下して...

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

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

これは一見何を意味するのか分かりづらいですが、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 ではFALSEは0、TRUEは0以外の数値に置き換える事が出来るので、{TRUE,FALSE}であれば{1,0}と書く事も出来ます。こっちの方がシンプルでいいですね。僕はいつもこっちの書き方をしています。

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

まとめ

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

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

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

HAYs