Excel の vlookup を使用した間接的

VLOOKUPを使用したい場合で、検索したいデータが別のシートにある場合は、INDIRECTを使用してそれらを組み合わせることができます。これは、単一の式で複数の範囲を定義するのに役立ちます。

上の例では、3 つの異なるスプレッドシートに月次データがあります。しかし、VLOOKUP + INDIRECT を 1 回行うだけで、複数のシートからすべての月の全商品の数量を取得できます。

 =VLOOKUP($A2,INDIRECT("'"&B$1&"'!"&"A:B"),2,FALSE)

この式を理解するには、次の 2 つの部分に分ける必要があります。

最初の部分には、行 1 の名前を使用してシートへの参照を作成する INDIRECT 関数があります。以下の例では、シート Jan の範囲 A:B を参照します。

データが存在する名前と範囲を使用してシートを参照するには、INDIRECT で構造体を作成する必要があります。

 =INDIRECT("'"&B$1&"'!"&"A:B")

数式を Feb 列に移動すると、INDIRECT の参照が Feb シートに移動します。

2 番目の部分では、VLOOKUP は INDIRECT によって返されたテーブル範囲アドレスを使用し、範囲内で指定された Col_index_num に基づいてその値を取得します。

大事なポイント

上記の数式では、シート名で範囲を参照するには正しい構造が必要です。 INDIRECT に以下の構造を入力すると、次のようになります。

 "'"&B$1&"'!"&"A:B"

彼は戻ってきます：

 "'Jan'!A:B"

代替方法

INDIRECT は揮発性関数です。スプレッドシートに変更があると更新されます。このため、CHOOSE の使用を検討してください。たとえば、CHOOSE では、VLOOKUP を使用して 3 つの数式を作成できます。

 =CHOOSE(B$1,VLOOKUP($A2,Jan!$A:$B,2,0),VLOOKUP($A2,Feb!$A:$B,2,0),VLOOKUP($A2,Mar!$A:$B,2,0))

先ほど述べたように、この数式には 3 つの VLOOKUP があり、CHOOSE を使用すると、いずれかの VLOOKUP から結果を取得するかを決定できます。

CHOOSE では B1 について言及しました。 1 行目には、SELECT から式の値を取得するために使用するインデックス番号があります。

たとえば、2 がある場合、CHOOSE は 2 番目の VLOOKUP の値を返します。 3番目からは3つあります。