エクセル MATCH関数でファイル名等の値をセルから持ってくる方法を教えてください

質問

エクセル初心者です。
MATCH関数で、別ファイルの値を取得しようとしていますが
うまくいかないのでどなたか教えていただけませんでしょうか？

下記のように直接ファイル名やシート名を記述するとうまく
いくのですが、

[式]
　=MATCH(C7,[test.xls]テストシート!C1:C100,0)

ファイル名とシート名を変えることがあるので、

　セルB5　→　test.xls
　セルC5　→　テストシート
として、B5とC5を用いて記述しようと、&や""を使っていろいろと
式に入れてみたのですが、うまくいきません。
途中まではうまくいきそうになっても、C1:C100　の範囲指定で
tesx.xlsではなくて自分自身の　C1:C100　を参照したりと、
思った結果になりません。

どなたか、教えていただけると助かります。
宜しくお願いいたします。

回答No.3

imogasi

既回答者には質問のいみが判ったようだが、私にはもやもやして、はっきり意図がわかりにくい。
ーー
こう書いたらどうだ。
Ｃ７でファイルを選択する（決定する）文字列か数値を入れる。
下記で言えば大阪や神戸など。コードでもありえる。
例
Ｅ１：Ｆ4
Ｅ列　　Ｆ列
指定語　ファイル名
大阪　　A.xls
神戸 　B.xls
京都 　C.xls
奈良 　D.xls
それで参照するブックを選択する。
そしてそのブック内の、決まったシートの決まったセルの値を参照したい。
と言うことかな。（シート名まで指定はＦ列の書き方を工夫すれば出来る。）
ーーー
であれば、Ｃ７で指定したデータに基づいて、ＶＬＯＯＫＵＰ関数で
=VLOOKUP(C7,$e$1:$f$4,2,FALSE)
これで文字列としてエクセルファイル名が決まる。
ＭＡＴＣＨ関数とＶＬＯＯＫＵＰ関数は働きは似ていて、同じような用途に使える。
しかしそのまま=をつけて、セルに書いても
（１）フルパス指定の点（ファイル指定でA.xlsの前に着く部分すなわちフォルダ名は同じだと思うから”ＸＸＸ”で指定し、それとファイル名を＆でつなぐ。
セル番地は同じと思うので””の中に書く。
（２）ただの文字列から、「セルを参照する」に働きを変えてくれる
関数ＩＮＤＩＲＥＣＴ関数を思い起こす必要が有る。
Ｇｏｏｇｌｅで「ndirect関数 使い方」「ndirect関数」でＷＥＢ照会すること。
ーー
エクセルで他ブックのセル参照は
http://www.excel-jiten.net/formula/ref_other_books.html
の最後の所をじっくりみて考えて。
ーーー
セル参照は他ブックの範囲（多数セル群）は出来なかった思うが
質問者の意図はどうなっているのか？出来ると思ってないですか。

回答No.2

noname#204879

=MATCH(C7,INDIRECT("["&B5&"]"&C5&"!C1:C100"),0)
としたらどうなりまりか？
ただし、関連ブック(ファイル)をすべて開いておく必要があります。

お礼

早速、ご回答をありがとうございました。

昼間からずっと悩んでいたのですが、教えていただいた式を
入れてましたところ、まるで何事も無かったかのように静かに
求めていた値が現れて嬉しかったです。

本当に助かりました。
どうもありがとうございました。

回答No.1

momoo

マクロを使うと上手くいきます。
私が試作したもので解説します。
スクリーンショットの画像も参考にして読んで下さい。

MATCH関数で別ファイルの値を取得するファイルをmihon.xlsとし、
データが入っているファイルを２つ、それぞれ
ファイル名：test.xls　シート名：テストシート
ファイル名：renshu.xls　シート名：練習シート
としました。

テストシートにはC1セルからC100セルまでデータがあり、
練習シートにもC1セルからC100セルまで別のデータがあります。

mihon.xlsのシートのD5あたりに、
B5のファイル名とC5のシート名を入力後に
MATCH関数の中のファイル名とシート名の指示を変更させるための
マクロに連結したボタンを作りました。
つまり、B5のファイル名とC5のシート名を入力して［式の変更！］ボタンをクリックすると、
C9セルにあるMATCH関数のなかにあるファイル名とシート名が書き換わります。

［式の変更］ボタンのクリック時のマクロに、この1行を書きます。
Worksheets("Sheet1").Range("C9").Formula = "=MATCH(C7,[" & Range("B5").Value & "]" & Range("C5").Value & "!C1:C100,0)"

これで55nyanさんが希望している作動が実現できます。

わからないことがありましたら、補足をお願いします。

お礼

遅い時間にもかかわらず、詳しいご回答をいただいて恐縮です。

マクロはまだやったことがなく、難しそうですがやってみたいと
思います。

取り急ぎ、お礼申し上げます。
いろいろ教えていただいて、どうもありがとうございました。