VLOOKUP（ブイルックアップ）は実用的でとても面白い関数ですが、ちょっとしたことで「#N/A」（ノーアサインエラー）が出やすいものでもあります。今回は、最近VLOOKUPを使い始めました･･･という方向けに、かつての私も悩まされたエラーになる事例を3つばかり取り上げてみたいと思います。
※事前にVLOOKUP関数の基本的な使い方を見たいわーという方は【Excel】検索して表示する！VLOOKUP関数を使ってみようの回をご覧ください。
※解説画像はMicrosoft365のExcelを使用しています
「検索値」と「範囲の先頭列」の形式が違う
表示形式トラップ
こちらの例は、セルC2に社員番号を入力すると、「社員マスタ」から該当者の氏名と所属を取得してC3、C4に表示させる式を入れています。しかし、①②どちらも#N/Aのエラーになっています。数式は合っているはずなのに何故でしょう？
実はこれ、C2に検索値として入力した「0001」と社員マスタの社員番号列に入力された「0001～0005」の表示形式が異なっているのがエラーの原因です。
頭が「0」で始まる数字を「標準」や「数値」の書式で入力すると0が外され整数部分しか表示されません。そのため、数式を作成する際に範囲となる表のデータをぱっと見て『社員番号が頭ゼロではじまるという事は文字列にしておかなくちゃダメだよね』と思い込み、C2の表示形式を「文字列」に変更しました。
しかし、C2とE3それぞれにカーソルを合わせて数式バーを見てみると、C2は文字列で入れた「0001」がそのまま表示されていますが、E3の方は「1」となっています。これは何を表すかというと、E3の実態はただの数値の「1」なのですが、表示形式で頭に0をつけているという事が考えられます。
E3を選択して「セルの書式設定」ダイアログボックスを開き、表示形式を確認すると「ユーザー定義」で「0000」という書式が適用されています。4桁に満たない整数を入力すると頭に0を補完する形式ですね。
このような場合は、セルC2は「標準」に戻し、「1」や「2」の整数を入力する事で結果は正しく表示されます。ただ、『リストは「000×」だけど入力は頭のゼロ入れなくていいから』としてしまうと、入力する側にとってはとてもわかりづらい仕様になってしまうので、入力するセルC2にも同じユーザー定義の形式を適用しておくのがベターです。
この例に限らず、基本「検索値を入力するセル」と「範囲で検索値が含まれる列」の表示形式は合わせると覚えておくと、エラーになるのを防ぐことができます。
文字列トラップ
もうひとつの形式違いの事例です。
先ほどと同じ表で、社員番号は整数に変えたパターンです。入力するセルC2は「標準」のままですが、社員マスタの社員番号列が「文字列」になっています。これも結果はエラーになります。
先の例にならい、セルC2の表示形式も文字列にすれば結果は正しく表示されます。ただ、根本的な事を言うと、このケースは「社員マスタ」の作りの方に問題あり、と言わざるを得ません。
Excelは「数値と特定できる値」と「数値なのか何なのか曖昧な値」は別物として扱いますので、整数のデータをあえて文字列形式にしてしまうと予期せぬ数式エラーを招く元となります。
文字列にしなくてはならない（それこそ先の例のように頭にゼロを入れたいからというような）理由がない限り、セルに文字列形式を適用するのは極力避けるようにしましょう。
※文字列の数字を他の形式に変えたい時はこちらの回で書いていますのでご参考にどうぞ。
https://www.anadigilife.com/excel-mojiretu-formatchange/
範囲の選択誤り
次は引数でいうところの「範囲」の設定誤り例です。B3に商品コードを入力し、「商品マスタ」にある商品名をC3に表示させるという式ですが、結果はエラー値「#N/A」になっています。
これは単純にVLOOKUPの「検索値が含まれる列を範囲の先頭列に指定する」というお約束が守られていない事によるエラーです。範囲に「E3:H7」と入れていますが、そうすると先頭列は「No.」の列になりますので、どの商品コードを打っても「値なし」でエラーになってしまいます。
「範囲」は表示したい値が含まれる表全体を指定しなければならない、という事ではありません。「検索値が含まれる列」から「表示したい値が入力されている列」までを指定するのが正しい考え方です。
この例では「商品コード」を入力して「商品名」を表示したいだけなので、範囲は「商品マスタ」のF3からG7までの2列を指定すればOKです。また、列の数についても表の左端から数えるのではなく、「範囲で指定した先頭列から数えて何列目」となりますので、「2」が正解です。
商品マスタのスタイルを変えておさらいしてみましょう。下の表では検索値が含まれる列はF列、表示したい値（商品名）が入力された列がI列になりますので、範囲はF3からI7までを指定します。先頭の「商品コード」の列から数えて4列目が商品名になるので、列は「4」と入力するのが正解です。
なんとなくイメージはつかめたでしょうか。範囲について、ついでにもうひとつ気に留めておきたいのが、商品リストや社員番号など、あとから行が追加される事が想定される表を範囲として使っているケースです。参照する表に項目が追加された場合、数式の範囲も拡張しておかないと、追加されたコードを入力しても範囲内に該当する値がないため「#N/A」になってしまうので注意しましょう。
数式をコピーする時に範囲を「絶対参照」にしていない
VLOOKUPの数式を他のセルにコピーする時につい忘れがちなのが「範囲を絶対参照で固定しておく」ことです。固定を忘れると、数式をコピーした時に最初は調子が良くてもある段階から結果が正しく表示されなくなります。
1本目の数式の入ったセルを確認すると、範囲は「F3:G7」と相対参照になっている状態です。
数式を入れたセルは、ダブルクリック もしくは 選択してＦ２キーを押すと、上の画像のように参照先のセルやセル範囲がカラーでマークされるので目視で確認しやすくなりますよ。カラーの枠をドラッグで動かして数式を修正する事もできます。
このまま数式を下方向にコピーすると範囲も1行ずつ下にずれていきますので、3つめに入力した商品コードが範囲から外れてエラーになった、という事です。
下図のように、最初に入れた数式で範囲の列記号と行番号の前に「$」をつけて絶対参照にすると･･･
数式をコピーしても範囲は動かないので、正しい結果が表示されます。
･･･慌てて数式を作っているとつい忘れちゃうんですよね絶対参照って。で、式をずべ───っとコピーした時に「ぅおあっ」となるのです。私は今でもたまにやります（笑）
