vlookupに、Indirect と Match 関数を組み合わせる

質問

vlookup関数で、検索値にSheet1(Book1)から、範囲は、Sheet2(Book1)のセルにある名前のついているSheet1(Book2), そして、列番号は、指定した名前とマッチするSheet1(Book2)の列番号から、導き出そうとしています。
ところが、Matchで指定した範囲は、Indirect内の関数として認識されないようで、結果が得られません。

たとえば、
Book1のA1～E1にタイトルが入っています。(Index, Title, Book, Author, Frequency)
Book2には、
シート１: InputName
シート２: Output

シート２に以下の関数を入れてみましたが、うまくいきません。
ちなみに、VLOOKUPの列番号を Match関数の代わりに、5 とすると、結果オーライですが、Book1 の列順が変わっていることがあるので、Matchを使ってエラーを避けたいのですが、以下の関数のどこを直すべきでしょうか？
=IF(ISNA(VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$1400"),MATCH("Frequency",$A$2:$E$2,0),FALSE)),0,VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$1400"),MATCH("Frequency",$A$2:$E$2,0),FALSE))

よろしくお願いします。

回答No.3

grumpy_the_dwarf · ベストアンサー

> Book2には、シート List のみ。
> A1～E1には、Index, Title, Category, Author, Frequency とあります。

じゃぁ、indirect("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$1:$E$1")でしょう。

回答No.2

noname#204879

唐突に“寿限無”式を提示して「どこを直すべきでしょうか？」と尋ねるより、
１．Book1 に当該「タイトル」が入っている Sheet名
２．そのタイトルの下のレコードサンプル
３．Book2 のシート InputName、Output 上の関連するデータ例
等々を示して、何をしたいのかを簡潔に説明したら如何でしょうか？

回答者に提示式を解読しろというのは横着だと思います。

補足

ごめんなさい。例を付け加えます。
Book1には、シート InputName と Outputがあります。
InputNameには、A2に、Book2.xls; B2に、List　と入力されています。
Outputには、Index, Frequency, Categoryが、A1～C1にそれぞれ入力されています。そして、vlookup関数をB2以降に入れて、Book2のFrequencyに配置されている数値を出力させます。

Book2には、シート List のみ。
A1～E1には、Index, Title, Category, Author, Frequency とあります。

流れは、Book1のInputNameに入力されているファイル名とシート名を呼んで、それに対応するBook2を参照します。
対応するファイルによっては、Index, Category, Frequency, Title, Author, Remarks のように、列順が変わっていることがあります。

そこで、match関数との組み合わせに試行錯誤しています。
どうでしょうか？

回答No.1

grumpy_the_dwarf

> =vlookup(C2,indirect("'["&InputName!$A$2&"]"& InputName!$B
$2&"'!"&"$A$2:$E$1400"),match("Frequency",$A$2:$E$2,0),FALSE)

match関数の第2引数が「$A$2:$E$2」ということは、同じブックの同
じシートを参照しているはず。これがとても変です。

Book2にはImputNameというシートとOutputというシートがあって、
vlookup関数はBook2のOutputシートに書いてあるはずですね。そし
てBook2のImputNameシートには、A2に検索対象のブック名、B2検索
対象のシート名が書いてあるんですね。そこまではわかりました。
そうすると、検索対象となる範囲のしょっぱなに書いてある項目名
をチェックするためにmatch関数が参照すべきはindirect
("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$2")
じゃないでしょうか。

補足

自分の中でも混乱が深まってきましたので、シンプルにしてみました。

目的は、vlookの列番号をmatch関数で出力される値で置き換える。

Book1には、２つのシートがあります。
InputName => このシートのA2 に、参照するブック名; B2 に参照するブックに付帯するシート名（下記の例では、List)を入力します。
Output => vlookup 関数を使って、データを出力します。記載してある項目は、Index, Frequency, Categoryの３つ。Book2のFrequencyの数値の出力。

Book2 には、カタログデータが入っています。(Index, Title, Category, Author, Frequency)が、A1～E1に入っています。シート名は、"List".

Book1;sheet(Output)は、A1～C3にそれぞれ Index, Frequency, Category とあります。
そこで、Book1;sheet(Output) のB2以降に、Cに合致させるFrequencyをBook2から見つけ出す関数が入っています。

match を使わないのであれば、以下の関数で、数値８が得られました。
=IF(ISNA(VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),3,FALSE)),0,VLOOKUP(C2,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),3,FALSE))

ただし、Book2のカテゴリーリスト項目の位置が変更されていることがあるので、上記の関数にある列番号 3 に、match関数に置き換えたい訳です。

そこで、以下の関数にすると、"Frequency"にマッチする対象を Book1のA1～E1を参照してしまい、Book2を参照してくれません。ここで得られてしまうのは、Book1のFrequencyの位置、つまり、列２です。Book２の列５をMatchで出力したいのですが、、、。
=IF(ISNA(VLOOKUP(C3,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),MATCH("Frequency",$A$1:$E$1,0),FALSE)),0,VLOOKUP(C3,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),MATCH("Frequency",$A$1:$E$1,0),FALSE))

そこで、$A$1:$E$1 に、頂いたアドバイスのように再度 indirect を使って下記のように置き換えてみました。すると、０が出力されてしまいます。
=IF(ISNA(VLOOKUP(C3,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),MATCH("Frequency",INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$2"),0),FALSE)),0,VLOOKUP(C3,INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$C:$E"),MATCH("Frequency",INDIRECT("'["&InputName!$A$2&"]"& InputName!$B$2&"'!"&"$A$2:$E$2"),0),FALSE))

よろしくお願いします。