Vlookup・Xlookup等でシート参照する時はIndirect関数がおススメ！その理由は？

今回のテーマはIndirect関数です。
多くの人はあまり使うことのない関数かもしれません。
しかし、この関数を知っておくとVlookup（Index＆Match）・Xlookupで検索する時に、とある事にすごく役立ちます。

今回は郵便局から提供されている、郵便番号を住所から抽出する想定で説明していきます。

どんなところで役立つのか。そのオススメポイントをご紹介いたします。

Indirect関数とは？

指定される文字列への参照を返します。セル参照はすぐに計算され、結果としてセルの内容が表示されます。 INDIRECT 関数を使うと、数式自体を変更しないで、数式内で使用しているセル参照を変更することができます。
Microsoft サポート引用

式はINDIRECT(参照文字列, [参照形式])となっています。
重要なのは参照文字列で、参照形式は、A1形式もしくはR1C1形式のどちらかを選択するようになっていますが、ほとんどの方はA1形式と思いますので、ここは入力不要です。

※A1＝R1C1でセルを指しています。
Row（行）1 Column（列）1なので＝セルA1です。

正直Indirect関数の説明はわかりづらいと思いますので、「Indirect関数が役立つところとは？」の説明で詳しくご紹介いたします。

以下の２つのポイントがあります。

例えば他のシートを参照する時には「Sheet1!」、テーブル参照であればテーブル１［［#すべて],［項目名］］といったようにセルに設定する必要があります。
これを関数の式内に設定するのではなく、Indirect関数を使うと、別のセルもしくはシートに設定しておくことができます。

下図がサンプルです。
B2セル（郵便番号テーブルの表全体）とC2セル（郵便番号テーブルの住所を連結した項目）に郵便番号マスタのシートから参照したいテーブルの情報をそれぞれ入力しています。

郵便番号マスタのシートはこちらです。
こちらは日本郵便局さんが提供している、郵便番号のCSVデータをインポートしたものです。
※Column16は私がSheet1の住所をヒットさせるために追記したものです。

本題に戻りまして、通常郵便番号の情報を取得しようとすると、以下のような式になります。

=INDEX(テーブル_KEN_ALL[#すべて],MATCH([@番地前抜き出し],テーブル_KEN_ALL[[#すべて],[Column16]],0),3)

Indirect関数を使うとこのようになります。

=INDEX(INDIRECT($B$2),MATCH([@番地前抜き出し],INDIRECT($C$2),0),3)

関数の式がシンプルで見やすくなったかと思います。
見やすくなった理由はセルB2とC2にそれぞれ、参照元のテーブルの文字列を設定しています。

続いてはもう一つのポイント「間違えて参照元を消しても復旧が簡単」をご紹介いたします。

Index関数・Vlookup・Xlookupなどの別のシートにあるものを検索する関数の大敵は対象のシート、テーブルを削除してしまう事です。

削除すると、＃REF!というエラーになります。計算する参照元が削除されたために起こるエラーです。
Indirectを使わないケースですと、仮に参照元のシートを復活させても、式は入力しなおさなくてはなりません。

しかし、Indirect関数を使用していると、その必要がありません。

Indirect使用しないケース

参照元シート削除後

参照元シートインポート後

Indirectを使用するケース

参照元シート削除後

参照元シートインポート後

以上２つ目のポイント「間違えて参照元を消しても復旧が簡単」でした。

いかがでしたでしょうか。

今回のケースは出力したいデータが検索値よりも左側にあるので、Vlookupは使えませんでしたが、Xlookupなど検索をする関数においては同じような効力を発揮します。特に郵便番号マスタのようにデータがとても多い場合はIndirect関数はおススメです。

ぜひ活用してみて下さい。