Excel使いこなしの基礎と応用

VLOOKUP関数でエラー#N/Aが出た時はここを確認!

使用関数:VLOOKUP

数式は間違っていない、検索範囲にはちゃんと検索値が然るべき場所に入ってる・・・それなのに、VLOOKUP関数は冷たくエラー#N/Aを返してくる! 一体、なぜ?!という時に確認すべき事項と対処法をまとめました。

目次


検索範囲をセル範囲で指定していたら・・・

VLOOKUP関数は4個の引数で構成されています。
VLOOKUP関数の構成

スポンサーリンク

この内、2番目の引数「検索範囲」をセル範囲で指定する時にありがちなケアレスミスがあります。

範囲の参照形式が絶対参照になっているか?

検索範囲を「A5:C9」のように手書きする場合も、ダイアログで範囲をドラッグして指定する場合も、「$A$5:$C$9」と絶対参照にしておくことが必要です。
データ範囲を相対参照で検索範囲に指定したVLOOKUP関数を挿入したとします。
相対参照で検索範囲を指定したVLOOKUP関数を挿入
この数式をB6セル以降にフィルコピーすると、検索範囲が1行ずつ下にずれて、実際の検索範囲に含まれない「C10~C13」のセルまでも検索対象になってしまい、
相対参照だと指定範囲がずれる
検索値が見当たらず、#N/Aが返される事態が発生するわけです。

検索範囲にする表データはテーブル化しておこう

このようなミスを避けるためには、検索範囲に使う表をテーブルにしておくのがお勧めです。表をテーブル化して得られるメリットは様々ですが、VLOOKUPに関して言えば、テーブルには固有の名前が付くこと、範囲の拡張、データの追加が容易で再設定の手間が不要なこと、この二つが大きな利点です。
テーブル化すると「テーブル1」のような名前が自動で付きますから、VLOOKUPの第2引数にその名称を入れるだけでOK。表の見出し行を除いた全範囲を絶対参照で指定したことになります。テーブル名は自由に変えられます。
検索範囲にテーブル名を指定
テーブル内の特定の列範囲だけを指定することも可能。「テーブル1[[商品コード]:[商品名]」のような表記になります。[]の中は列の項目名です。
テーブルの列範囲だけを指定

検索方法をTRUEにしていたら・・・

4番目の引数「検索方法」が「FALSE(完全一致)」であるなら特に問題はありませんが、「TRUE(近似値も含む)」にすると、しばしば問題が発生します。
検索方法にTRUEを指定

検索値が文字列なら、FALSEが無難

そもそも近似値検索は、下図のように最小値と最大値の間の数値も検索対象に含めたいといったケースで限定的に使われます。
数値同士の間の値も検索対象にする
ですが、例えば、名字だけ検索値に入力して、氏名の列から部分一致でデータを引っ張ってくるというような使い方はできません。この場合、検索方法をTRUEにしている限り、完全一致の氏名を入力しても正常な値が返されない可能性が高くなります。
文字列を検索値にするなら、「FALSE」にしておくことです。
「TRUE」を「FALSE」に変更するだけでエラーが解消するかもしれません。

検索範囲の左端の列が昇順になっているか?

また、TRUEで検索する場合には、一つ重要な制約があります。
VLOOKUPでは検索値に対応する検索範囲内の列は必ず左端に置かなければなりませんが、TRUEの検索方法を採る時は、尚且つ、この列が「昇順=小さい順」に並んでいる必要があるのです。
検索は似の左端列を昇順に並べ替える
並べ替えを実行するには「ホーム」タブの「編集」グループにある「並べ替えとフィルター▼」から「昇順」を選ぶか、
ホーム→並べ替えとフィルター→昇順をクリック
データ」タブの「並べ替えとフィルター」グループにある「昇順」コマンドボタンをクリック。
データ→並べ替えとフィルター→昇順ボタンをクリック
これで昇順に並びます。

検索値と範囲の左端列が「隠れ不一致」になっている

外部のデータファイルを取り込んだ時などに、検索の不一致として#N/Aが返されることがあります。データを見比べても完全一致しているのに、なぜ?!という時は、空白(スペース)が入り込んでいる「隠れ不一致」を疑ってみましょう。

空白(スペース)が隠れていたらワイルドカードで解決!

下図では「コード」を検索値にして、右横の表「テーブル1」から単純に「氏名」を転記する数式を「=VLOOKUP(A3,テーブル1,2,0)」とB3セルに入れています。
B3セルの数式は件数分だけフィルコピーし、A列に表と同じコードをそのまま入力してみました。当然、テーブル1の氏名がそっくり返るはず・・・が、全て#N/Aが返ってきてますね。
空白を含むコード範囲を空白無しで検索するとエラーになる
2つの「コード」の値は目視では同じに見えますが、実はテーブル1のコードには末尾に全角スペースが入っているのです。検索方法は「FALSE」ですから、完全一致とは見做されずエラーとなったわけですね。
さて、この課題の対処法ですが、もちろん、検索値にコードを入力する時に全角スペースを入れれば解決です。ただ、作業効率から言って、半角数字の後に全角スペース一つ入れるのは手間だし、ミスが頻発しそう。
ここで誰もが考えることは「TRUEにすればいいじゃん!」だと思うんですが、前述したように文字列の曖昧検索はできません。そこで、ワイルドカードの力を借ります。

スポンサーリンク

VLOOKUPの第一引数「検索値」を「A3&"*"」とします。
&と*を使って検索値を指定
これは、「A3に入った文字列に任意の文字列"*"が繋がる」という記述で、A3の入力値と、その末尾に続く何らかの文字列を検索して要求通りのデータを返して下さいとExcelに宣言しているわけですね。
VLOOKUPの引数にA3&"*"と入れる
数式は「=VLOOKUP(A3&"*",テーブル1,2,0)」。件数分、フィルコピーすると正常に氏名のデータが転記されました。
正常にデータが取得できた

表示形式の不一致は区切り位置で解決!

完全一致で検索する場合、データの表示形式も一致している必要があります。
検索値と範囲の左端列の表示形式が不一致
上図のように検索値のセルの表示形式が文字列になっていると、一致する値が無いとしてエラーが並んじゃいます。
困ったことに、列を選択してセルの表示形式を変更しても、入力値自体を入れ直さないと文字列のまま。ちまちま修正していくのは結構骨なので、パス。・・・ということで、ここは「区切り位置」コマンドを使います。
文字列を数値に変更するには、「データ」タブの「データツール」グループにある「区切り位置」ボタンをクリック。
データの区切り位置ボタン
区切り位置ウィザードが開いたら、いきなり「完了」を押します。
区切り位置ウィザードの1ページ目
めでたく文字列表示が数値に切り替わりました。エラーも解消。
表示形式が文字列から数値に変わった
逆に、数値から文字列に変えたい時は、区切り位置ウィザードが出たら、「次へ」を2回押してウィザードの3ページ目を開き、「列のデータ形式」で「文字列」のラジオボタンをオンにします。そして、「完了」。
区切り位置ウィザードの3ページ目で表示形式を文字列に設定
文字列から数値に変える操作で直ちに「完了」を押したのは、「列のデータ形式」がデフォルトで「G/標準」になっているからです。


VLOOKUP関数のエラー#N/Aへの対処法【まとめ】


高機能の表計算ソフトExcelを活用すれば、仕事や私生活で、今あなたが必要とするいろいろなモノが作れます。その作成手順とデザイン、アイデアのヒントまでを可能な限り詳細にわかりやすくナビします。基本操作や関数、グラフに関する解説記事とともにご利用下さい。