Excel作業でデータ検索する関数のVLOOKUP関数やXLOOKUP、HLOOKUPを使って、つまづいている人向けの記事です。
表に絶対あるはずの値、見比べてみても同じ値があるのに、VLOOKUP関数やXLOOKUP、HLOOKUPで計算すると「#N/A」エラーが出てしまうことがあります。途方に暮れますね。
それに対処するための方法を書いています。
ExcelのVLOOKUP関数やXLOOKUP、HLOOKUPで「#N/A」エラーが出るとき
ExcelのVLOOKUP関数やXLOOKUP、HLOOKUPで「#N/A」エラーが出るときがあります。
効率化のためにエクセルで作業しているのに、関数のせいでつまづいてしまうとイライラしますね！
わかります。
落ち着いて原因を調べてみましょう。
まずはフィルター機能を使って、存在していることを調べてみましょう。
ありましたか？
そもそも存在していなかった場合は「#N/A」エラーが出ること自体間違っていません。
#N/A（該当なし）と出ること自体は正解なんだけど”#N/A”じゃない表示にしたいとき、エラーをトラップする関数を使って解決しましょう。Office365ならIFERROR関数が使えます。ほかのバージョンならIS関数のISERRORを使って実現できます。
文字の問題
あるはずなのに存在していない結果が出た場合、まず疑うことが3点あります。
１つは全角半角問題。一方が全角文字、もう一方が半角文字で入力されている可能性です。
2点目は横棒問題。横棒には「ハイフン」「マイナス」「長音」「アンダーバー」などいろいろあります。入力者の趣味趣向で一般的でない文字が使われている可能性があります。さらに全角半角問題も絡んでくるのが厄介な点です。横棒を入力し直すとあっさり解決することがあります。
3点目はスペース問題。空白にも「半角スペース」、「全角スペース」、「タブ」、それに加えて特殊な文字が使われているために見えていない文字などがあります。また一方のセルにだけスペースが入っていることもあります。
これらの問題は両方のセルをエクセル以外の別のエディターやメモ帳、ワープロなどにコピペしてみると差異がわかりやすいかもしれません。
セルの形式が異なっている
ちゃんと存在しているのに「#N/A」エラーだった場合、セルの書式を比較します。同じ形式になっている確認をします。
セルの形式が「標準」「数値」「通貨」「文字列」と揃っていないせいで「#N/A」エラーが出ていることがよくあります。
異なっていたら、どれか適当な種類に統一します。それらの検索対象の列すべてに対して統一した書式にします。
ここまででたいていの場合は解決するでしょう。
コピペや挿入を繰り返した結果、書式が乱れていることが往々にしてあります。それくらいにセルの書式設定が違うことによってうまくいかない場合が多くあります。
VLOOKUP関数を正しく理解していない
そもそも論になりますが、VLOOKUP関数を正しく理解していない場合があります。
VLOOKUPでは探したい値は指定した範囲の1列目になければいけません。また、検索するテーブルは昇順にソートされていなければ正しく見つけられません。
最新版の関数 XLOOKUPなら1列目や昇順を意識しなくて探せるようになりましたし、見つからない場合に返す引数も付きました。Office 365なら、XLOOKUPを使うことを検討してみるのもいいでしょう。関数の仕様からXLOOKUPは利用者の要望や提案が反映された関数とわかりますね。
大きい数値、小さい数値の問題
比較している値の桁数が多い、小数点以下があるせいで、VLOOKUP関数やXLOOKUP、HLOOKUPが正しく機能していない可能性があります。
エクセルの仕様では、桁数が大きい場合や小数点以下の数値の場合に、画面に表示されている値と内部的に保持している数値に差異がある場合があります。
画面上で２つのセルの値はまったく同じに見えるのですが、内部的には２つのセルの値は指数で保持されていて誤差レベルの差が存在している場合があるのです。
これは桁数が大きい数値や小数点以下の数値で起こるときがあります。
複数人で同じシートを作業していたり、2台以上の異なるパソコンで作業したことがある場合に起きやすくなります。
内部的に格納されている値に差異があるせいで、比較したときに完全一致せず「#N/A」エラーが出てしまうのです。
対処方法として、数値をテキストに変換して、テキスト同士を比較するようにします。そうすることで内部的な差をなくします。
=TEXT(書式設定する値, “適用する表示形式コード”)
大きな数値の場合の例は、文字列になったとき数値が指数化されないように”00″としておきます。
たとえば
=TEXT(A2,”00″)
とします。
小数点以下の数値の場合、比較したい小数点以下の桁数を指定します。下記例の場合2ケタにしています。
=TEXT(A2,“0.00”)
文字列にしてしまえば内部的な誤差はなくなります。
一時的に作った文字列のセル同士で比較すればちゃんと同じになって「#N/A」エラー問題は解決です。
参考情報
