VLOOKUP関数で値があるのに＃N/Aや＃REF!になる Excel(エクセル)

VLOOKUP関数はデータを扱ううえで非常に便利な関数ですが、一度＃N/Aや＃REF!のエラー沼にはまるとなかなか抜け出せなくなってしまう経験がある人も多いのではないでしょうか。今回はVLOOKUP関数で＃N/Aや＃REF!になってしまう原因とその対策をいくつかご紹介します。

「＃N/A」検索値が完全一致していない

一番多いのがこのパターンでしょう。VLOOKUP関数は検索値が対象と完全一致していないとデータを抽出することができません。少し長い文字列だったり名称だったり文字の後ろにスペースがあったりすると目視で一致しているかどうか確認するのは難しい場合があります。

例えば下図のようなパターンです。右側の表が顧客名簿で、こちらのデータを左の表の顧客名を検索値としてデータを拾っています。

数を見てわかるようにほとんどが「#N/A」になっていますが、ぱっと見では何が違うのかが分かりません。確かに存在するのにエラーになっているように見えてしまっています。

こちらのデータの間違いの答えは

• 1月1日：売上帳の「八王子」の後ろに半角スペースが入っている
• 1月2日：支店名の前のスペースが全角スペースではなく半角スペース3つになっている
• 1月3日：売上帳のアポストロフィー(’)が半角になっている
• 1月5日：齋藤の「齋」の字が「斎」になっている

です。
いずれも見た目でだけでは判断できないものがほとんどです。そのため関数も間違ってないし確かにデータがあるのにエラーになっているように見えてしまっています。

確認方法

これは実際に検索してみれば分かります。

下図のように確認したい検索値のセルを選択し、「[Ctrl]+C」でコピーします。

顧客名簿の顧客名の列を選択し、「[Ctrl]+F」+「[Ctrl]+V」を押す。

これで検索ボックスに検索文字が入った状態になります。
この状態で「次を検索」ボタンを押します。

ここで下図のようなメッセージが出れば完全一致したセルが無いということですので、見た目では分からないわずかな違いがあるということです。

この場合は顧客名簿の方から顧客名をコピーして、売上帳の該当の名称のセルに張り付けします。

すると下図のように「#N/A」のエラーを消すことができました。

対策

• マスタに無い名称は入力できないように入力規則を利用する。入力規則を利用する場合はプルダウン(ドロップダウン)を作る Excel(エクセル)も参考に設定してみてください。
• 表記ゆれの起きにくい連番などのコードを検索値にする

「＃N/A」関数コピーで範囲がズレている

多くの場合は下記のように相対参照になっている関数をコピーすることで発生します。

=VLOOKUP(B3,F3:H9,2,0)

確認方法

下図の関数を見ると「$」マークがついていない相対参照になっています。
この状態で関数を下までコピーすると

下図のように顧客名簿を参照している部分が表からズレてしまっているのが分かると思います。

ズレてもたまたま参照範囲に収まっている番号については結果が表示されていますが、参照範囲からはみ出てしまった「1002」番は「#N/A」になってしまっています。

対策

今度は下記のように参照範囲に「$」マークを付けて絶対参照にしてみます。

=VLOOKUP(B3,$F$3:$H$9,2,0)

この状態で関数を下までコピーしてみると

このようにエラーを修正することができました。

「＃N/A」行数の範囲を超えている

まずはこちらのエラー状況を見てみましょう。

確認方法

見た目だけでは分かりませんので、「C3」セルをダブルクリックして計算式を表示してみると、
下図のように範囲選択が顧客名簿の途中までしか選択されておらず、「1005」の顧客番号が範囲外になっていることが分かります。

対策

この場合は範囲の選択範囲を表の最後まで広げてあげればOKです。

これでエラーが修正されました。

あとは修正した関数を下の行までコピーしておきましょう。

「＃N/A」検索値が範囲の左端になっていない

確認方法

今度は顧客コードをキーに顧客名を引っ張ってくる関数を記述してみました。

ところがこれはすべてが「#N/A」エラーとなってしまいました。

これはVLOOKUP関数の特性で、データベース上の検索値は必ず範囲の一番左側の列にある必要があります。上図の例では顧客コードは真ん中にあり、顧客名が左側の列に存在しています。

このために全てのセルがエラーになってしまっています。

対策

顧客名簿の方を並べ替えて顧客コードが一番左側に来るように修正しました。

するとすべて「#N/A」エラーを消すことができました。

「＃REF!」列番号が範囲を超えている

確認方法

下図を見てください。すべての行で「＃REF!」のエラーが表示されています。
こちらの計算式を見てみましょう。

関数を見てみると2列目までしか範囲指定されていませんが、列番号は「3列目」を指定しています。
2列目までしかないのに「3列目」を指定したために「＃REF!」エラーが発生したことが分かります。

対策

このパターンの対策は範囲を「3列目」まで広げてあげればOKです。あとは修正した関数を下までコピーしてください。

これで「＃REF!」のエラーを修正することができました。

まとめ

エラーが出た場合はいろいろなパターンの原因があります。ここまでご紹介した方法を一つずつ試してみて該当する原因が無いか調べてみてください。

この記事が皆さんのお役に立てればうれしいです。