ExcelのVLOOKUPとINDIRECT関数を使って表を切り替えて検索する方法

Excelで通常のVLOOKUP関数では一つの表を検索対象としますが、実はINDIRECT関数を使うことで複数の表を検索対象に切り替えることができます。
そこでこの記事ではExcelのVLOOKUPとINDIRECT関数を使って表を切り替えて検索する方法を紹介します。

会社では部署ごとに検索対象を変更したり、店舗ごとに表を変えたいこともありますよね。

応用としてとても便利な関数のため、是非この記事を参考にしていきましょう。

VLOOKUPで通常の使い方をした場合

まずはVLOOKUP関数で通常の使い方を紹介します。

関東地方の表の中から、指定されたNOの件名を呼び出します。

上の画像のように、1つの表を参照するときにVLOOKUPはとても便利な関数です。

しかし、他の地方が他の表に分けられているときなどはひと工夫が必要となります。

 

VLOOKUPとINDIRECTで表を切り替える

ここからは、関東地方と四国地方が別の表として存在した場合の数式を紹介します。

まずは以下のように下準備をします。

次に、VLOOKUPとINDIRECT関数を使用して以下のように入力します。

今はエラーになっていても気にしなくてかまいません。

数式は以下のようになります。

B3のセルを使用することで、地方を後から切り替えても自動で反映してくれるようになります。

また、もしVLOOKUP関数の使い方に不安がある場合は先に「【Excel関数】VLOOKUPを初心者向けに解説」の記事を見てみましょう。

 

名前の定義を使用した使い方

表を切り替えるためには、表に名前をつける必要があります。やり方は2つあるのですが、まずはシンプルに名前を定義する方法を解説します。名前を定義する表、今回はD4からE10を選択し、Excelの左上の場所に「関東」と入力することで名前を定義することができます。

続いて2つ目の表を四国にします。

これで地方の名前を切り替えても反映するはずです。

 

表をテーブルにした使い方

先ほどは名前を定義しましたが、実務的に考えるとテーブル化するのもおすすめです。

まずはD6のセルなど、関東地方の表を選択し、Ctrl+Tを押します。このようなCtrl+Tのような入力をショートカットと呼ぶのですが、時短になるので是非使っていきましょう。

続いて範囲をD3からE10に修正します。

表がテーブル化しました。同じテーブルを選択した後、「テーブルデザイン」タブでテーブル名を「関東」に設定します。

四国も同じようにテーブル化し、名前を設定しましょう。

 

 

INDIRECTを使わなかった場合の注意点

2つの関数を使用してふと疑問に思うのが、「そもそもINDIRECTを使わず直接セルを指定すればいいのでは?」という疑問。

やってみると分かるのですが、エラーになってしまいます。

INDIRECTを使用しない場合、四国や関東の名前がただの文字列として認識されるため、テーブル名などのように認識してくれなくなります。そのため必ずINDIRECT関数を一緒に使うことが必要です。

 

表の切り替えでエラーを防ぐ対処法

今回のようにVLOOKUPやINDIRECT関数を使用していると、エラーになる場合が想定されます。

実際に仕事で使うのであればエラーにならないようにしたいものなので、対処法を紹介します。

 

プルダウンで表の選択

例えば地方を関東と四国しか設定していないのに、九州を呼び出そうとするとエラーになります。

そこで、指定された名前以外を入力するエラーを防ぐ方法です。

これはプルダウン設定で簡単に予防できます。B3のセルを選択し、「データ」タブの「データの入力規則」を選択します。

続いて、「設定」の中にある入力値の種類を「リスト」にし、元の値に「関東,四国」と入力したらOKとします。

先ほどの設定でプルダウンからの入力が可能となりました。

また、プルダウン設定はさらに便利な応用もあります。

以下の記事にまとめていますので見てみましょう。

 

エラーの非表示

予期せるエラーが出た時に非表示にする方法も紹介します。

数式は長くなるのですが、通常の数式の最初にIFERRORを追記し、最後にも,””を追加します。

数式は以下のようになります。

また、Excelでは様々なエラーが発生しますので、もしエラーになることがあれば以下の記事も見てみましょう。

関数のまとめ

今回はExcelのVLOOKUPとINDIRECT関数を使って表を切り替えて検索する方法について紹介しました。

VLOOKUPだけでもとても便利な関数ですが、INDIRECTを使用することで複数の表を切り替えることができ、とても実用的になります。

部署ごとに表を切り替えたり、ランクごとの集計など使い道は多くあります。

 

Excelの機能を使いこなすと、仕事の効率化から大きな時間短縮に繋がります。

仕事術のスキルが増えるとプライベートの時間を時間を増やすことができますので、当サイトで得た知識を是非活用していきましょう。
以下のExcel記事もおすすめです。