「昨日まで動いていたVLOOKUPが、シートを分けた途端に動かなくなった」
経理部で1000行以上の支払明細を作っていた頃、別シートのマスターから振込先情報を引こうとして何度もエラーに泣かされた経験がある。
VLOOKUP関数は便利な半面、シートを跨いだ瞬間に数式の難易度が跳ね上がる。
よくある質問
研修で必ず出る質問や、現場のメンバーから相談を受ける内容をベースに解決策をまとめた。
Q. 同じシート内ならできるのに、別シートを指定すると「#N/A」やエラーが出るのはなぜ？
A. シート名の指定形式、あるいは「参照範囲」のズレが原因だ。
別シートを参照する場合、数式は `Sheet1!A1:B10` のように「シート名 + !（感嘆符）」で始まる必要がある。
筆者の経験では、シート名に「2026年度 売上」のようにスペースが含まれているケースで失敗している人が多い。
スペースや特定の記号が含まれるシート名の場合、`’2026年度 売上’!A1:B10` とシングルクォーテーションで囲うルールがある。
マウス操作でシートを選択すれば自動で付与されるが、手入力で数式を直すとこれを見落としがちだ。
また、範囲選択の後に元のシートに戻ってから確定してしまうと、参照先が元のシートに書き換わってしまうミスもよく見かける。
Q. 数式を下にコピーすると、2行目以降が正しく参照できない
A. 「絶対参照」の設定を忘れていないだろうか。
これが研修で最も多く受ける質問だ。
例えば、「売上入力」シートに「商品マスター」シートから単価を引いてくる場合。
`=VLOOKUP(A2, 商品マスター!A2:B100, 2, FALSE)`
このまま下にコピーすると、範囲が `A3:B101` `A4:B102` と1行ずつズレていく。
実務では、参照範囲をドラッグして選択した直後に「F4」キーを1回押し、`$A$2:$B$100` とドルマークをつけるのが鉄則。
これを忘れると、下の行にいくほど「本来あるはずのデータが範囲外になる」ため、VLOOKUP 別シート 参照できない という状況に陥る。
Q. 社員番号で検索しているのに、別シートにあるはずのデータが見つからない
A. 「数値」と「文字列」の型が不一致を起こしている可能性が高い。
システムからダウンロードした社員リストや顧客マスターによくある罠だ。
「検索する側の社員番号」が数値（右寄せ）で、「マスター側の社員番号」が文字列（左寄せ、または緑の三角マーク付き）だと、見た目が同じ「101」でもExcelは別物と判断する。
この場合、数式側で対応するなら `VALUE` 関数で文字列を数値に変えるか、逆に `&””` をつけて数値を文字列に変換して型を合わせる必要がある。
現場でよく見た事例では、片方にだけ「前ゼロ（001など）」がついているケース。
この不一致もエラーの常連だ。
Q. 別ファイルのシートを参照したら、ファイルを開いていない時にエラーになった
A. 外部参照（リンク）の更新が止まっているか、ファイルパスが変わったことが原因だろう。
VLOOKUPは別ファイル（ブック）も参照できる。
しかし、参照先のファイルを移動したり名前を変えたりすると、パスが切れて参照できなくなる。
数式が `=’C:\Users\Documents\[予算.xlsx]Sheet1′!$A$1:$B$10` のように長くなっていたら要注意だ。
Microsoft公式: 別の実習シートのセルを参照する にもある通り、外部参照は管理コストが高い。
可能な限り、同じファイル内の別シートにマスターをコピーして運用することを勧める。
Q. 範囲の中に検索値があるのに、どうしても一番上のデータしか取ってこれない
A. VLOOKUPの仕様上、重複データがある場合は「最初に見つかったもの」しか返せない。
例えば「勤務表」シートから特定の社員の休日出勤日を探す際、その社員が月に2回休日出勤していても、VLOOKUPは1回目しか表示しない。
これは関数の限界だ。
実務で「2番目以降」や「条件に合うものすべて」を抽出したい場合は、FILTER関数（Microsoft 365 / Excel 2021以降）を使うのが正解だ。
プロのコツ
15年の実務経験から、エラーを未然に防ぎ、メンテナンス性を高めるためのTipsを2つ紹介する。
1. 参照範囲を「テーブル」にする
VLOOKUPで最も怖いのは、マスターに新しく商品や社員が追加され、参照範囲（A2:B100など）から漏れてしまうことだ。
これを防ぐには、マスター範囲を「Ctrl + T」でテーブル化してしまうのが一番いい。
テーブルにすると、範囲に名前（例：商品一覧）をつけることができる。
数式は `=VLOOKUP(A2, 商品一覧, 2, FALSE)` となり、シート名を気にする必要も、F4キーでドルマークをつける必要もなくなる。
データが増えてもテーブル範囲は自動拡張されるため、VLOOKUP 別シート 参照できない というトラブルの8割はこれで解決できる。
2. 列番号を「COLUMN関数」で動的に指定する
「列番号を3に指定していたのに、マスターシートの途中に1列挿入したら、表示されるデータがズレてしまった」
これも実務でよく聞かれる疑問だ。
筆者の場合、列番号に直接「3」と書かず、`COLUMN(マスター!C1)` のように指定することがある。
こうしておけば、マスター側で列を挿入しても、関数の引数が自動で追従してくれる。
まとめ
別シートの参照でつまずいたときは、まず以下の3点を確認してほしい。
– シート名が `’シート名’!` の形式で正しく囲われているか
– 参照範囲が `$A$2:$B$100` のように絶対参照（固定）になっているか
– 検索値とマスター側のデータ型（数値・文字列）が一致しているか
VLOOKUPは引数が多い分、一つでも設定が狂うと沈黙する。
特に別シート参照は視覚的に範囲を確認しづらいため、今回紹介した「テーブル化」などの工夫を取り入れるのが、ミスを減らす近道だ。
困ったときはこの記事に戻って、数式の「!」の位置や「$」の有無を一つずつ確認してみてほしい。
コメント