エクセルのVLOOKUP関数で他のブックから正しく参照されないセルがある

質問

台帳xxxx.xlsファイルで住所録.xlsファイルを参照しているのですが最近正常動作しません。

住所録.xls というブックには、メンバーの住所録が入っています。
A列氏名、B列フリガナ、C列電話番号
という項目になっています。

台帳xxxx.xls側には
A列連番、B列氏名、C列フリガナ、D列電話番号・・・になっています。

例えば台帳0712.xls というブックには日々のメンバーの動きを記録しており、
Bのセルに氏名を入れればその右側にフリガナ、電話番号が、住所録.xlsから参照されて自動入力されるようになっています。
台帳ファイルは原本を複製して 台帳0713.xls、台帳0714.xls・・・と日々増えていきます。

現在、住所録.xlsは600行を超えてきたのですが、最近、台帳xxxx.xls に氏名を入れるとフリガナ、電話番号の欄がうまく参照されず"0"(ゼロ)と表示されてしまいます。
正しく表示される名前とされない名前が出てきました。
600番台以降の人に多い気がするのですが定かじゃありません。
なぜならその氏名を上位のセルにコピーしても参照結果が"0"だからです。

具体的には、
例えばC列の数式は
=IF(B2="","",VLOOKUP(B2,[住所録.xls]sheet!$A:$C,2,FALSE))
で、フリガナが自動入力されるようになっています。
※氏名が未入力のときは"#N/A"のエラー表示がされないようにしています。

どうして"0"が入ってしまう場合があるのでしょう。
行数に限界があるのか疑問です。
またはもっといい方法があれば教えてください。

回答No.2

noname#52504 · ベストアンサー

「自信なし」ですが…。

#N/Aや""ならともかく、0が返るということは、
「検索はヒットしてるのに、なぜかブランクセルを参照して返してくる」
ということですよね。

ご質問の数式を検討した限りでは、そのように動作する理由というのはちょっと思い当たらないのですが、
もしかすると、以下のチェックで原因が見つかるかもしれません。

Ａ
適当な列で、
　=IF(B2="","",VLOOKUP(B2,[住所録.xls]sheet!$A:$C,1,FALSE))
として、正常に動作しない行(氏名)について、適切な氏名が返るかどうかチェックする。

Ｂ
適当な列で、
　=IF(B2="","",MATCH(B2,[住所録.xls]sheet!$A:$A,FALSE))
として、正常に動作しない行(氏名)について、ヒットしている台帳側の行数を特定し、
当該行のデータに問題がないか確認する。

Ｃ
適切なブックを参照しているか確認する。
（どうも変だと思ったら、同名の古いファイルが別にあって、それを参照していたという経験があります）
・まず、「台帳xxxx.xls」　だけを開く。
　数式の参照部分が、
　　'C:\Documents and Settings\***\[住所録.xls]sheet!$A:$C
　のようにフルパスで表示されていることを確認する。
・次に、「住所録.xls」　を開く。
　数式の参照部分が、　　
　　[住所録.xls]sheet!$A:$C
　という表示に変わることを確認する。

Ｄ
参照範囲の行数を制限してみる。
実は、ご質問の数式を見て唯一気にかかったのがこの点です。
VLOOKUPの検索範囲の指定で、$A:$C　のように列全体を指定するのはあまり見かけない設定です。
手元の環境では列全体を指定した場合でも正常に動作しましたが、いつでもうまくいくのかどうかはわかりません。
　=IF(B2="","",VLOOKUP(B2,[住所録.xls]sheet!$A$1:$C$2000,2,FALSE))
のように、あらかじめ最終行を（十分に大きな行番号で）指定しておくのが一般的な書き方かと思います。

お礼

大変参考になりました。
ありがとうございます。

回答No.1

imogasi

第１チェックステップは、VLOOKUP関数の第２引数は
検索に使う表ですが、その指定において
（１）番地に＄が付いていますか（付いてないと複写で連れて来る）
（２）（検索に使う表について）その当初式を入れるときに、指定した範囲が、データの増加によって指定範囲よりオーバーしてませんか。
この２点まずパスしないと、前へ進めない大切な、良くあるミス事項です。

補足

回答ありがとうございます。

＞=IF(B2="","",VLOOKUP(B2,[住所録.xls]sheet!$A:$C,2,FALSE))

と入れています。
「$A:$C」という指定であればご指摘の2点はクリアしてると思うのですが。何か違っていたら教えてください。
よろしくお願いします。