研修の講師を務めると、必ずと言っていいほど「VLOOKUP関数がうまく動きません」という相談を受ける。その場で見せてもらう数式の多くは、書き方は合っているのにエラーが出ている。実は、エラーの正体は関数の書き方ではなく、元データの作りにあることがほとんどだ。
よくある質問
実務でVLOOKUP関数を使う際、誰もが一度は直面するトラブルをFAQ形式でまとめた。
Q. 「社員番号」で検索しているのに、データがあるはずの場所で「#N/A」エラーが出ます。
A. 現場で最も多く目にするのが、このパターンだ。原因は大きく分けて2つある。一つは「データの型」が一致していないこと。もう一つは「目に見えない余計な文字」が入っていることだ。
筆者の経験では、基幹システムからダウンロードした社員リストと、自分で作成した交通費精算書を照合する際によく発生する。システムから出した数値が「文字列」として認識されている一方で、自分が入力した数字が「数値」になっている場合、Excelはこれらを別物と判断する。
また、セルの値の後ろに「半角スペース」が紛れ込んでいるケースも非常に多い。見た目には「1001」でも、実際には「1001 」（後ろにスペース）となっていると、Excel VLOOKUP エラー 原因の筆頭候補になる。
対策としては、以下の関数を使ってデータを整えるのが定石だ。
– CLEAN関数 / TRIM関数：余計なスペースや改行を取り除く。
– VALUE関数：文字列を数値に変換する。
Q. 列を挿入したら、今まで正しく表示されていた数式の値がズレてしまいました。
A. これはVLOOKUP関数の「列番号」を数字で直接指定している（ハードコーディング）ことが原因だ。
例えば、顧客マスターの「B列（2列目）」にある住所を引っ張っていたとする。その後、マスターのA列とB列の間に「ふりがな」の列を挿入すると、住所は「C列（3列目）」に移動する。しかし、数式の第3引数が「2」のままだと、新しく入れた「ふりがな」を表示し続けてしまうのだ。
筆者が経理部で月次決算の資料を作っていた頃、前任者から引き継いだ複雑な表でこれが頻発し、修正に追われた苦い記憶がある。
これを防ぐには、列番号を「MATCH関数」で動的に取得するか、そもそも列の増減に強い XLOOKUP関数 への乗り換えを検討すべきだろう。XLOOKUPはOffice 2021以降やMicrosoft 365で利用可能だ。
Microsoft公式: VLOOKUP 関数によると、第3引数には検索範囲の左端を1とした列番号を指定する必要がある。
Q. 数式を下にコピーしたら、下の方のセルだけ「#N/A」が出てしまいます。
A. 研修で初心者が必ずと言っていいほどハマるのが、「絶対参照（$マーク）」の付け忘れだ。
商品マスタから単価を引いてくる売上管理表をイメージしてほしい。1行目の数式を入力した際、範囲を「A2:B100」と指定したとする。これをそのまま下にオートフィル（コピー）すると、Excelの親切心（相対参照）によって、2行目は「A3:B101」、3行目は「A4:B102」と、検索範囲が1行ずつ下にズレていく。
結果として、範囲外になったデータを探そうとして「#N/A」が出るわけだ。これを防ぐには、範囲を指定した直後に F4キー を一回押し、「$A$2:$B$100」と固定しなければならない。
Q. 「#REF!」というエラーが出て消えません。何が間違っているのでしょうか？
A. 「#REF!」は「参照範囲が無効」という意味だ。VLOOKUPでこれが出る場合、十中八九「指定した列番号が、選択した範囲の列数を超えている」ことが原因だ。
よくあるミスを挙げよう。
– 範囲を「A2:B10」と2列分しか選んでいないのに、第3引数（列番号）に「3」を指定している。
– 参照先のシートやセルを削除してしまった。
請求書作成の実務では、商品コードから商品名（2列目）、単価（3列目）、単位（4列目）を引いてくることが多い。範囲を狭く見積もってしまうと、このエラーが牙を剥く。
Q. 正しいはずの値を引っ張ってくる時と、デタラメな値を出す時があります。
A. 第4引数の「検索の型」を省略、あるいは「TRUE（近似一致）」にしていないだろうか。
実務において、商品名や顧客名の検索でVLOOKUPを使うなら、第4引数は必ず「FALSE（完全一致）」にすべきだ。ここを省略するとデフォルトでTRUE扱いになり、データが昇順に並んでいない場合に「それっぽい場所」にある別の値を拾ってきてしまう。
筆者が営業管理を担当していた際、見積書の金額が微妙にズレている原因を調べたら、この「検索の型」の指定漏れだったということが何度もあった。数式の最後は必ず `, FALSE)` で締める癖をつけよう。
Microsoft公式: VLOOKUP のエラーを回避する方法では、この近似一致によるトラブルが詳しく解説されている。
プロのコツ
15年の実務経験から導き出した、VLOOKUPを安全に運用するためのTipsを紹介する。
1. 「テーブル機能」で範囲指定の呪縛から逃れる
範囲を「$A$2:$B$100」のように固定しても、マスタの行数が増えるたびに数式を直すのは手間だ。そこで、マスタ範囲をあらかじめ「テーブル」に変換（Ctrl + T）しておくことを勧める。
テーブルに名前（例：商品マスタ）を付ければ、数式は `=VLOOKUP(A2, 商品マスタ, 2, FALSE)` となる。これならマスタに1,000行追加しようが、範囲は自動で拡張される。Excel VLOOKUP エラー 原因の多くを占める「範囲の指定ミス」を根本から断つ強力な手法だ。
2. IFERROR関数で「わざと空白」にする
「#N/A」が並んだ表は、提出書類としては見栄えが悪い。まだ入力していない行のエラーを隠すには、IFERROR関数を被せよう。
`=IFERROR(VLOOKUP(…), “”)`
こうしておけば、検索値が未入力のときはエラーの代わりに空白が表示される。ただし、本物のミス（データ漏れ）まで隠してしまう諸刃の剣でもある。筆者の場合、最終チェック時だけはIFERRORを外して、予期せぬエラーが隠れていないか確認するようにしている。
3. 文字列数字を「1」で数値化する
前述の「数値と文字列の不一致」を数式内で手っ取り早く解決する小技がある。
検索値が文字列で、マスタが数値の場合、`VLOOKUP(A21, 範囲, …)` と検索値に1を掛けてみよう。Excelが計算過程で強制的に数値へ変換してくれる。逆にマスタが文字列の場合は、`VLOOKUP(A2&””, 範囲, …)` と空文字を結合すれば文字列化できる。現場で型変換の関数を忘れた時に役立つ裏技だ。
まとめ
Excel VLOOKUP エラー 原因を解消するためのポイントは以下の通り。
– #N/A は、データの型（数値/文字列）の違いや余計なスペースを疑う
– $マーク（絶対参照） を忘れずに範囲を固定する
– 第4引数は「FALSE」 を指定し、完全一致で検索する
– 列番号の不一致 は、列の挿入や削除が原因であることが多い
– #REF! が出たら、指定した列番号が範囲内に収まっているか確認する
VLOOKUPは非常に便利な関数だが、その分エラーに悩まされる場面も多い。エラーが出たときは、数式を疑う前にまず「元データ」の状態を冷静に眺めてみるのが解決への近道だ。困ったときはこの記事に戻って確認してみてほしい。
コメント