Introduction
Ver. all 2013 2016 2019 2021 365
VLOOKUP関数の使い方を説明します。
他の表から対応データを探し出し、一致した行中のデータを求めることができます。
VLOOKUP関数は、仕事でエクセルを使用すると必ず目にする関数ではないでしょうか。
しかし、合計のSUM関数や平均のAVERAGE関数などと比べると覚えるのが大変な関数です。初心者にとっては一つのハードルともいえるかもしれません。
ゆっくり理解して、そのハードルを越えてしまえば、エクセルが得意になると思いますよ。
そのVLOOKUP関数ですが、他の表から元の表と条件が一致した場合にデータを引っ張ってくる関数です。下の図が使用例。
請求書や見積書で、「商品ナンバー」が一致した「商品名」を引っ張ってきたり、別で作成していた「住所録」の表から、電話番号など一部のデータを引っ張ってきたり・・・。
いろいろな状況で使用されています。
難しそうだけど頑張るにゃ！
VLOOKUP関数の使い方
では、VLOOKUP関数を使ってみましょう。
VLOOKUP関数・・・他の表から対応データを探し出し、一致した行中のデータを求める関数
=VLOOKUP（検索値,範囲,列番号,検索の型［省略可］）
- 検索値・・・検索する値（一致させたい場所のセル参照や値、文字列）を指定
- 範囲・・・検索する（一致させる）相手のデータ全体を指定
- 列番号・・・「範囲」で指定したデータの、左から何列目と「検索値」を一致させるかを指定
- 検索の型・・・完全一致させたい場合は「FALSE」、近似値で一致させたい場合は「TRUE」を入力。省略した場合は「TRUE」になる。（FALSEは「0」、TRUEは「1」でも可）
言葉で説明すると、どうしても分かり難くなりますね。実際の表でみてみましょう。
下の表では、提出物の項目に「A～D」の評価が入力されています。そして、どうやら別で「コメント一覧表」というのが用意されているみたいですね。
その「A~D」の評価に応じて、「コメント一覧表」の「提出物」の内容を隣に表示させたいというわけです。
この場合、先ほどの関数の引数説明と対応させてみると、下のようになります。
- ［検索値］・・・検索する（一致させる）値・・・「D3」
- ［範囲］・・・検索する（一致させる）相手のデータ全体・・・「G9：I12」
- ［列番号］・・・［範囲］の左端から何列目のデータを表示したいか・・・「3」
- ［検索の型］・・・「A~D」と完全に一致させたい・・・「FALSE」
いかがでしょうか。何となく見えてきましたか。
そして参照する表（［範囲］で指定する表）に関しては、幾つかポイントがあります。
- ［検索値］のデータと一致させるのは、必ず［範囲］となる表の左端列
- ［範囲］の場所は、参照する表のデータ部分だけで良い
上のように、タイトルや項目は必要ありません。使用するデータ範囲だけを選択します。
- ［範囲］は絶対参照にする
関数を後でコピーする事を考えて、［範囲］は「$」マークを付けてセル番地を固定しましょう。
※絶対参照の意味がちょっと・・・という方はExcel（エクセル）で計算する方法 ③｜絶対参照の使い方をご覧ください。
実際にVLOOKUP関数を使ってみる
では、実際に使ってみましょう。
関数を入れる「E3]をクリックしてから、［関数の挿入］ボタンをクリックします。
［関数の挿入］画面が表示されるので、［すべて表示］を選んで全ての関数を表示しましょう。
一覧から「VLOOKUP」を選んで［OK］。
［関数の引数］画面が表示されます。4か所も入力が必要なので大変ですね。
［検索値］には検索したい値の「D3」セルを指定します。
［範囲］は、タイトルや項目を抜かした、「G9:I12」を選択し【F4】キーを押して、絶対参照にします。
左から3列目の「提出物」のコメントを取り出したいので、「3」と入力します。
最後の［検索方法］は、完全に「A~D」の文字を完全に一致させたいので「false」と入力します。
※「false」の代わりに「0」でもOK
［OK］をクリックすると結果が表示されます。
問題なさそうですね。後はオートフィル機能を使用してコピーすれば完了。
ちゃんと［範囲］の場所を絶対参照にしていたので、きれいにコピーされました。
ちなみに、［列番号］を「2」にすると、左から2番目の列の内容が取り出されますよ。
VLOOKUP関数での「TRUE」と「FALSE」の違い
4番目の引数の［検索の型］ですが、「TRUE」と「FALSE」が選べるのでした。
「TRUE」が近似値で一致させて、「FALSE」が完全一致。
この「TRUE」に関して、説明を追加します。
先ほどの表だと、「A~D」という文字列を一致させるので完全一致しか確認できません。
ということで、表の「A～D」を「1～4」へ変更します。
そして、「コメント」に先ほどと同じVLOOKUP関数を検索の型「FALSE」のまま入れると、
特に問題なく表示されます。完全一致なので、数字が一致したものをそのまま表示しただけですね。
では、ここから実験です。
「D5」の数字を「1」から「1.5」、「D10」の数字を「2」から「5」へ変更してみました。両方とも参照する表には存在しない数字です。
青線の場所を見ると、両方とも「#N/A」エラーが表示されていますね。「FALSE」で完全一致したものしか表示しないため、参照するセルに存在しない数字の場合はエラーが表示されるのです。
では、その状態のまま、VLOOKUP関数の検索の型を「TRUE」に変更してみます。
結果は下のように、参照する表の近い値が表示されます。
「1.5」の場所は、「1」の結果。「5」の場所には「4」の結果が表示されていますね。
ここで疑問がわきます。参照する表に存在する値より、小さい数値や大きい数値の場合は、参照する表の最小値か最大値が表示されるのは分かりますが、間の数値はどうなるのでしょう。
近似値というのは、どの辺りを拾ってくるのでしょうか。前後の値の近い方になるのでしょうか？それとも前の値が優先だったりして・・・。
ということで、調査してみましょう。下の表をご覧ください。「1」～「2.4」まで「0.2」区切りで入力したものです。
よーく見てみると、「1.8」が「2」ではなく「1」として扱われています。どうやら次に検索される値を超えない値までのようですね。
TRUEの近似値・・・検索値以下の最大値が一致した値として使用される
ということが分かります。
［注意事項］
下の表をご覧ください。エラーが表示されています。
こちら、関数は先ほどと同じままで、参照する表の「ランク」を昇順「1～4」からから降順「4～1」へ変更しただけのものです。どうやら参照する表の数値は昇順（小さい順）である必要があるようですね。
- ［検索の型］で「TRUE」を使用する場合、参照する表の検索場所は昇順になっている必要がある
「降順」になっているとうまく機能が働いてくれません。VLOOKUP関数を使用していて、どうしてもエラーが出る場合は、これが原因のこともありますよ。
このようなエラーを防ぐためにも、［検索の型］は「FALSE」をお勧めします。
まとめ
今回は説明のため小さい表を横に並べて説明しました。
本来VLOOKUP関数は、大量のデータを扱う場合に使用することが多いため、複数のワークシートをまたいで使用することも少なくありません。引数の入力にミスが無いようにしたいですね。
また、［検索の型］は常に「FALSE」にして完全一致させることをおすすめします。面倒だからと省略すると「TRUE」になってしまうので注意しましょう。「FALSE」打ち込むのが面倒な人は「0」と入力しましょう。「FALSE」と同じ意味になります。
ポイントをまとめると以下のようになります。
- ［検索値］のデータと一致させるのは、必ず［範囲］となる表の左端列
- ［範囲］の場所は、参照する表のデータ部分だけで良い
- ［範囲］は絶対参照にすることが多い
- ［検索の型］は「FALSE」がおすすめ
バージョン2021以降では、今回説明した「VLOOKUP」をパワーアップした「XLOOKUP」が使えるようになりました。
最近登場した関数だけあり「XLOOKUP」の方が使いやすいのですが、互換性を考えると「VLOOKUP」もまだまだ現役です。両方理解しておきたいところです。
［範囲］は絶対参照にするのを忘れちゃダメにゃ