Excelの魅力をお伝えする「パソコン教室ISA」ライターチームです。
VLOOKUP関数は、エクセルで使える関数の一種です。一定範囲のデータのなかから、特定のデータを取り出すのに役立ちます。しかし、適切な設定や指定ができていないと、エラーを出して有効活用できません。この記事では、VLOOKUP関数におけるエラーと対処法を解説します。VLOOKUP関数を使用する際の注意点も解説するため、ぜひ参考にしてください。
VLOOKUP関数とは？
VLOOKUP関数とは、エクセルで活用できる関数の1つです。垂直という意味を持つVerticalの頭文字「V」と、探すという意味を持つ「LOOKUP」を組み合わせた造語です。VLOOKUPの機能としては、表を縦方向に検索して、指定した条件に関するデータを取り出してくれます。そのため、大量のデータのなかから、特定のデータを探したい場合に活用されます。
VLOOKUP関数の構文
VLOOKUP関数の構文は以下のとおりです。
関数に記載する項目である引数は、4つで構成されています。それぞれの引数の詳細を下記で解説します。
1.検索値
VLOOKUP関数における最初の引数は検索値です。検索範囲に検索値と合致する値があった場合に、VLOOKUP関数が適切な値を返してくれます。
2.検索する範囲
2番目の引数では、検索値で指定されたものを検索する範囲を決めることが可能です。検索する範囲で指定された一番左の列を基準にして、検索値が一致するかどうかを検索してくれます。
3.表示する値の列番号
3番目の引数は、表示する値の列番号を指定するため、検索結果の表示先を左右する部分です。2番目の引数で指定した範囲における、一番左の列を1として基準にし、列番号を指定します。
4.検索方法
4番目の引数では、検索方法を決めます。基本的には、検索値と完全に一致した場合だけ表示するか、それ以外の方法(近似値検索)を指定するかの二択です。最初の図で、D3にパイナップルと誤って入力した場合検索する範囲に、パイナップルはないので、エラーを返したいです。そのような場合は、検索方法をFALSEとします。TRUEとすると、何かしらの数値を返してしまい、間違えに気づきずらいです。
なお、検索方法は省略可能です。しかし、省略してしまうと、完全一致ではないTRUEを指定した扱いになります。そのため、省略せずにFALSEを記入することがほとんどです。
＼ Excel講座の詳しい情報を受け取る ／
VLOOKUP関数におけるエラーと対処法
VLOOKUP関数におけるエラーは多岐に渡ります。以下は、具体的なエラーとそれぞれのエラーへの対処法です。
#N/Aが表示されてしまう
VLOOKUP関数における#N/Aの表示は、合致するデータがないため、検索値が存在しないことを意味します。なお、#N/Aのアルファベットは、No Applicableの略で、「該当なし」という意味です。#N/Aが表示されてしまう原因と対処法は以下のとおりです。
#N/Aが表示されてしまう原因
#N/Aが表示されてしまう原因は多岐に渡ります。検索値に指定されているものが誤った値だったり、検索範囲がずれていて、検索値が範囲外になっていたりする場合が該当します。特に、相対参照のままVLOOKUP関数をコピーすると、検検索範囲がずれる事態に陥りやすいため注意が必要です。
#N/Aが表示された際の対処法
#N/Aが表示された際は、検索値に誤った値が指定されていないか確認しましょう。まず、Ctrl＋Fで検索と置換のダイアログボックスを開きます。検索窓から、指定した値がワークシートに含まれているかを調べましょう。検索値に間違いがなかった場合は、検索範囲のずれが起こっていないかを確認します。
F2を押すとセルが編集状態になり、数式をチェックすることが可能になります。引数として参照するセル範囲を確認して、ずれが起こっていないか確かめましょう。
#REF!が表示されてしまう
VLOOKUP関数で#REF!が表示されたら、検索範囲で指定した範囲の列数を超える値が列番号に指定されている状態になっています。なお、#REF!は、referenceの略であり、日本語では参照エラーと訳される言葉です。
#REF!が表示されてしまう原因
#REF!が表示される原因は、指定した列番号が表示可能な列を超えてしまうことです。例として、=VLOOKUP(E3,$A$3:$C$7,4,FALSE)の状態における解説をします。
上記の数式では、検索範囲がAからC列となっており、表示可能な列は3列になります。しかし、列番号に4を指定すると、検索範囲に４列目はないため、#REF!の状態になるという原理です。
#REF!が表示された際の対処法
検索範囲や列番号を修正することで、#REF!表示を解消できます。F2を押してセルを編集状態にすれば、数式をチェックして検索範囲や列番号を確認可能です。
正しくない値が表示されてしまう
VLOOKUP関数では、関数の構成や指定した値が正しくても、誤った値が表示される場合があります。ここからは、正しくない値が表示される際に考えられる原因と、対処法について解説します。
正しくない値が表示されてしまう原因
VLOOKUP関数は、指定した範囲内を上から順番に検索したうえで、最初にヒットした値を表示する方法です。そのため、範囲内に重複したデータがあると、意図していない値が表示されかねません。また、4番目の引数である検索方法がTRUEとなっていたりする場合も、正しくない値が表示される原因になります。
正しくない値が表示された際の対処法
データの重複が原因の場合は、データの重複を解消したり、先に最新のデータを表示するように並び替えをしたりしましょう。また、検索方法や表示列番号などの引数が間違っていれば、正しい値に修正することで対処可能です。
VLOOKUP関数を使用する際の注意点
VLOOKUP関数を使用する際は、注意すべき点があります。以下は、代表的な注意点とそれぞれの詳細です。
検索値は一番左の列に配置する
VLOOKUP関数は、検索する値が、検索範囲の中の一番左の列に入力されていなければなりません。そのため、IDや名称といった検索する値より左の列に、表示したい値を存在させることは不可能です。その場合はエラーになってしまい、VLOOKUP関数が機能しません。検索値は必ず一番左の列に配置して、表示したい値は右側に配置しましょう。
検索値でデータを並び替える
VLOOKUP関数は、上から順番に検索をかけて指定された値を探し出したいときに便利です。そのため、IDを活用して並び替えをしておくと、正しい値が表示されやすくなります。データの重複が発生しやすそうな場合は、入力した日付で並び替えできるように設定しておくと効果的です。
検索範囲は絶対参照か列ごとを参照にする
VLOOKUP関数をコピーして活用する際のトラブルを避けるために、検索範囲は絶対参照か列ごとを参照にしておきましょう。関数の検索範囲の設定例は、以下のとおりです。
| 種類 | 書き方 |
| 通常の範囲指定 | =VLOOKUP(E3,A3:C7,2,FALSE) |
| 絶対参照の範囲指定 | =VLOOKUP(E3,$A$3:$C$7,2,FALSE) |
| 列の範囲指定 | =VLOOKUP(B3,A:C,2,FALSE) |
列による範囲指定は多用に注意が必要
列による範囲指定を多数のセルに施す際は注意しましょう。なぜなら、検索範囲が増えて表示時間が遅くなる可能性があるからです。表示時間が遅くなると、パフォーマンスの低下が懸念されます。
IFERROR関数で表示を変える
検索して適切な値が見つからない場合、デフォルトでは#N/Aと表示されます。#N/Aと表示されること自体は、問題がない場合も少なくありません。しかし、ほかの情報との統一感がなく見栄えが悪くなってしまいます。IFERROR関数を用いれば、検索して適切な値が見つからなかった場合に、表示される文言を変更可能です。
たとえば、=IFERROR(VLOOKUP(E3,$A$3:$C$7,2,FALSE),”存在しない商品名”)とすれば、#N/Aではなく、存在しない商品名という文言が表示されます。
VLOOKUP関数は、大量のデータを扱う際に効果的な関数です。しかし、適切な使い方をしないと、表示時間が遅くなってパフォーマンスが下がる懸念があります。そのため、VLOOKUP関数のみならず、各種関数やエクセルの使い方についての知識・ノウハウがあると効果的です。
ISAパソコン教室は、スキルアップや資格取得など、さまざまな面でパソコンに関する勉強をサポートしています。40年近く教室を運営し、直近の10年はMicrosoftジャパンが年に1社しか選ばないLearning Awardを3度受賞しているなど実績もあります。VLOOKUP関数や、エクセルの使い方について学びたい人は、ぜひISAパソコン教室をご利用ください。
MOS試験の概要や取得するメリット、受験科目の選び方や勉強方法を解説
VBAとはどういうものか？VBAの基本情報からできること、できないことを解説
