VLOOKUP関数特有のエラーを確認しよう

指定した範囲の表を縦方向に検索して、対応する値を取り出すVLOOKUP関数は、仕事でExcelを使うビジネスパーソンには馴染み深い関数です。しかし、引数が4つあるこの関数は複雑で苦手な人も多く、エラーが起こったり、思った通りの結果にならなかったりする場面も少なくありません。

VLOOKUP関数が入力されたワークシートで、#N/Aエラーが表示されてしまいました。

VLOOKUP関数のトラブルは、#N/Aエラーをはじめ、特徴的なものがいくつかあります。ここでは、遭遇しやすいエラーの原因と、その回避方法を説明します。

検索値が見つからない#N/Aエラー

#N/Aエラーは、合致するデータがないときに表示されます。「N/A」は「No Assign」（ノー・アサイン）の頭文字で、有効なデータがないという意味です。試しに［検索値］として参照するセルA3を空白にすると、#N/Aエラーが表示されます。

［検索値］のセルA3を空白にすると、#N/Aエラーが表示されました。

この場合、空白に一致するデータが存在しないため、#N/Aエラーが表示されています。

冒頭の例でも#N/Aエラーが表示されていますが、こちらにはデータが入力されています。ここでのエラーの原因は何か、順に探っていきましょう。

手順に入る前に、VLOOKUP関数の構文を確認しておきます。引数として指定するのは［検索値］［範囲］［列番号］［検索の型］の4つです。

---

=VLOOKUP（検索値, 範囲, 列番号, 検索の型）

---

1. ［検索値］ ... どのデータで
2. ［範囲］ ... どこを検索して
3. ［列番号］ ... どの列にある値を取り出すか
4. ［検索の型］ ... 完全一致は「FALSE」、近似一致は「TRUE」

1数式が入力されたセルを調べる

セルB3を選択して［F2］キーを押すと、セルが編集状態になりました。［検索値］も［範囲］も問題なさそうです。「2」は［範囲］の2列目、完全一致で検索したいので［検索の型］も「FALSE」で問題ないようです。

編集状態にしたセルを見てみると、4つの引数に問題はなさそうです。ということは、指定した値が問題の原因と考えられます。確認が済んだら［Esc］キーを押して、セルの編集状態を解除します。

2指定した値がワークシートに含まれているかを調べる

セルA3の値をコピーしておきます。［Ctrl］＋［F］キーを押して［検索と置換］ダイアログボックスを表示します。［オプション］ボタンをクリックして、表示されたオプションの［大文字と小文字を区別する］［セルの内容が完全に同一であるものを検索する］［半角と全角を区別する］の3つにチェックを付けて検索します。

ここで、オプションを指定するのを忘れないよう注意しましょう。検索の結果、合致する値は存在しませんでした。

セルA3の値をよく見ると、全角文字で入力されています。［範囲］のデータは半角文字のため、単純な入力ミスが原因で合致するデータがない状況でした。

ここで焦ってVLOOKUP関数が入力されているセルを修正したりすると、もっと深刻なエラーにつながります。#N/Aエラーが表示されたときは、落ち着いて［検索値］の入力ミスではないかを確認しましょう。

なお、4つめの引数［検索の型］を「TRUE」と指定した場合、［検索値］を超えない最大値を一致する値と見なす近似一致となります。値の大小で判定されるため、#N/Aエラーが表示されやすく、注意が必要です。

［検索の型］が「TRUE」のときの誤動作

前述の通り、4つめの引数［検索の型］が「TRUE」の場合は、近似一致でデータが合致すると見なされます。このとき、見た目ではエラーとは分からずに意図しない結果となることがあるので要注意です。具体例を見てみましょう。

［検索の型］として、セルA3に「1303」と指定しました。商品ID「1303」に対応する商品名は「商品303」のはずですが、「商品301」のデータが表示されています。

商品ID「1303」に対応する商品名は「商品303」ですが、なぜか「商品301」と表示されています。先ほどと同じように、セルを編集状態にしてみても、数式に問題はなさそうです。

セルを編集状態にして、VLOOKUP関数の数式を確認しても問題なさそうです。

何が問題なのでしょうか。ここで、［範囲］に注目してください。単価で並べ替えられており、商品IDの順番はバラバラです。

「TRUE」の場合は近似一致のため、先頭から「1303」を検索して「1303」を超えない最大値が近似値として合致する値と判断されます。この表では近似値の「1301」に対応する「商品301」が取得されてしまったのです。

この問題を解決するには［範囲］の左端の列、ここでは［商品ID］を昇順（小さい順）に並べ替えておきます。

［商品ID］を昇順（小さい順）に並べ替えた状態です。「1303」に対応する「商品303」が正しく取得できました。

数式のコピーでセル参照がずれた

VLOOKUP関数の引数として指定したセル参照がずれることによるエラーもよくあります。例えば以下のような状況です。

セルH3に入力したVLOOKUP関数の数式をコピーしたところ、セルH4～H6にエラーが表示されてしまいました。

セルH3に入力したVLOOKUP関数の数式を、セルH4～H6にコピーしたところ、エラーが表示されていました。#N/Aエラーが表示されたのはなぜでしょうか。［F2］キーを押して、セルH4を編集状態にして数式を確認してみます。

数式を確認すると、引数として参照するセル範囲がずれています。

数式をコピーした際の自動補正により［検索値］と［範囲］のセル参照がずれ、#N/Aエラーが表示されてしまいました。

「H2」や「A3:E13」のようなセル範囲の指定方法は相対参照と呼ばれます。ここでは、数式のコピーによって相対的にセル参照がずれる機能が裏目に出てしまったわけです。

これを回避するには、コピー元のVLOOKUP関数で指定しているセル参照を絶対参照に修正します。

1第1引数を絶対参照に切り替える

セルH3を選択して［F2］キーを押してセルを編集状態にします。「H2」を選択して［F4］キーを押します。

2絶対参照に修正できた

「$H$2」となり、絶対参照に切り替わりました。「$」にはセル参照を固定する意味があり、「$H$2」は「H」（列番号）も「2」（行番号）も固定することになります。
なお、［F4］キーを押すごとに「H$2」→「$H2」→「H2」→「$H$2」と切り替わります。

3第2引数を絶対参照に切り替える

同様に「A3:E13」を「$A$3:$E$13」に切り替えます。

4修正した数式をコピーする

セル参照が固定されたまま、修正した数式をコピーできました。取得したい列は、それぞれの項目で異なるので、第3引数の［列番号］を修正しましょう。

第3引数の［列番号］を修正した結果、意図通りのデータを取得できました。

VLOOKUP関数のエラーは、［検索値］が［範囲］に存在しないことがほとんどです。#N/Aエラーが表示されても焦らず、状況確認をしてください。また、セル参照のずれもエラーの原因になりがちです。はじめから絶対参照で指定することを習慣にしましょう。

関連記事