Excel（エクセル）でVLOOKUP関数で2つの表など複数範囲で検索する方法

Introduction

Ver.　all　2016　2019　2021　2024　365

エクセルでVLOOKUP関数を使用時に、2つの表を切り替えてデータを検索する方法です。

INDIRECT関数を使用します。

今回は2つの表範囲を切り替えますが、やり方は同じで3つ以上の複数の表を切り替えることもできます。

 

VLOOKUPで2つの表から検索する方法

作業の内容

今回は下の表を使用します。

左上の「営業所」と「商品区分」を入力したら、右下2つの表を参照して「発注日」が表示されるようにします。

その２つの表を判別するために、表の範囲に別々の名前を付けておきます。

表の名前は今回は「関東」と「関東以外」と付けます。

下の図を見て、動作の流れをつかんでください。

営業所に「関東」か「関東以外」を選ぶ　→　商品区分に「普通」「冷蔵」「冷凍」「大型」のどれかを選ぶ　→　対応する表を参照　→　指定された商品区分の発注日を表示する。

名前の定義

数式を作成する前に、複数の表の参照範囲に別々の名前を付ける必要があります。

まず、１つの表のデータ範囲を選択して、左上の［名前ボックス］をクリック。

そこに定義する名前を入力して【Enter】キーを押します。
※名前は検索で使用する文字列と同一にします。

今回は「関東」と入力して【Enter】。

これだけで、その範囲に名前が定義されます。

同じように2つめの表にも名前を付けてあげます。

今回は「関東以外」にします。

３つや４つの表を使用する場合は、同じようにして違う名前を定義しましょう。

定義した名前を削除したい場合は、［数式］タブ　→　［名前の管理］を押して、

表示された画面で対象の名前を選択して［削除］をクリックです。

すぐ左のボタンで、名前の変更もできます。

VLOOKUP関数を使用する

では、表に名前を付けたところで、数式を作成していきます。

関数を入れるセルを選択して、

「VLOOKUP関数」の引数画面を表示して、下のように入力します。

［検索値］・・・C6
［範囲］・・・INDIRECT(C5)
［列番号］・・・2
［検索方法］・・・FALSE

と入力。

ポイントは［範囲］の場所です。

INDIRECT関数を使用して、C5に入力された営業所の名前と、先ほど定義した表の名前を一致させて範囲を指定しています。

そして、その範囲の中で、「商品区分」で入力されたデータと一致した2列目を引っ張ってきます。

---

INDIRECT関数・・・指定された文字列の参照を返す

=INDIRECT（参照文字列,参照形式［省略可］）

• 参照文字列・・・セル参照や、文字列、定義された名前を指定する
• 参照形式・・・［参照文字列］で指定したセル参照を「TRUE」か「FALSE」で指定する。省略するかTRUEの場合はA1形式、FALSEの場合はR1C1形式になる。

---

完成する式は下のようになります。

=VLOOKUP(C6,INDIRECT(C5),2,FALSE)

結果ですが、最初は「#REF!」エラーが表示されます。

これは、上の場所に何も入力されていなのが原因です。

データを入力すれば、結果は表示されます。

もし、何も入力されていない時にエラーが表示されないようにしたいときはIFERROR関数を使用しましょう。

=IFERROR(VLOOKUP(C6,INDIRECT(C5),2,FALSE),"")

出来上がった関数をIFERROR関数で囲んであげて、エラーの場合は空欄を表示するようにした式です。

また、入力部分は毎回入力するよりも、リストから選べるようにした方が良いかもしれません。

［データ］タブ　→　［データの入力規則］をクリック。

表示された画面で、［入力値の種類］に［リスト］を選び、［元の値］の場所に選ぶ項目を「,」で区切って入力します。

まとめ

参照する範囲に名前を定義して、その名前をINDIRECT関数で参照するという方法です。

INDIRECT関数は今回のような「名前」だけではなく、セルに入力されている文字列を参照することもできます。

上手く利用できることがあるので覚えておきたいところ。