Excel(エクセル)のVLOOKUP関数の使い方！複数条件や別シート参照のやり方など解説

更新日時 : 2024-11-12 14:28

エクセルの関数の中でも、業務などで良く使われるのがVLOOKUP関数です。

IDから商品名を抽出したり、個人名から住所を所得したりとさまざまな使い方ができ、大量のデータを扱う際はミスの防止もできるので、VLOOKUPは業務の効率化に欠かせません。

しかし、VLOOKUP関数は引数が多く扱いがやや複雑で、エラーが出てしまうケースも多いです。

業務で必要だけど使い方が良く分からないし、上手く扱えず困っている、こういった方も多いでしょう。

そこで、この記事では、VLOOKUP関数について、実例を見ながら分かりやすく使い方を解説していきます。

エラーが出る原因とその対処法や、VLOOKUP関数で別シートを参照する方法、結果に0を表示しない方法や、IF関数と組み合わせて使う方法など応用についても解説するので、VLOOKUP関数でお困りの方は、ぜひ参考にしてみてください。

VLOOKUP関数とは？意味は？何ができる？

VLOOKUP関数は、垂直を意味する「Vertical」 の頭文字「 V」 と、探すを意味する「LOOKUP」 を組み合わせた関数で、日本語読みは「ブイルックアップ」です。

関数としては、表を縦方向に検索し、指定した条件に関するデータを取り出す機能を持ちます。

ExcelのVLOOKUP関数でできること

VLOOKUP関数は様々な使い方ができますが、代表的なものなだと、顧客名から住所や連絡先を取得したり、在庫管理データから商品の価格や在庫数などの情報を取得したりといったことが可能です。

大量のデータから特定のデータを探すというのは大変ですが、VLOOKUP関数を使えば簡単に、指定した条件から正しいデータを取り出すことができます。

Excel・ VLOOKUP関数の引数（ひきすう）は4つ

VLOOKUP関数の構文は下記のようになっています。

＝VLOOKUP（検索値, 範囲, 列番号, 検索方法）

実際のExcelの画面で見ると、上画像のようになります。

VLOOKUP関数が入力されているのは、C4セル。

検索値はB4セルを参照し、範囲は②の赤枠内を選択。

列番号は、左から数えて3番目なので、「3」と入力。

検索方法に、完全一致検索するために「FALSE」を入力しています。

B4セルに入力した「姓」から、その人物の点数を見るけるためのVLOOKUP関数です。

B4セルに「田中」と入力すれば、対応する「55点」が抽出され、B4セルに「伊藤」と入力すれば、対応する「80点」が抽出される仕組みです。

次から、それぞれの引数について、詳しく解説していきます。

• 検索値
• 範囲
• 列番号
• 検索方法

検索値

検索値は、引数の一番目に、指定した範囲から見つけ出したい、数値・文字列を入力します。

例えば、表から田中という人物の点数を見つけ出したいなら、田中と入力されたセルを参照します。

関数内に直接文字列を入力することも可能で、伊藤という人物を検索するなら「＝VLOOKUP（“伊藤”, C8:E12,3,FALSE)というように、ダブルクォーテーションで文字列を囲んで、検索値を入力します。

検索値は範囲の一番左に指定されている必要があります。

範囲の2番目になっているようなケースだと、VLOOKUP関数はエラーを返すので、注意してください。

例えば、2列目の姓から検索したいのに、表に入っているからと番号も範囲に含めてしまう。

こういったケースだと、VLOOKUP関数がエラーになります。

検索したい列が範囲の左にないのでエラー

また、検索値は全角文字と半角文字は区別されますが、英字の大文字と小文字は区別されず同じ値として認識される点にも、注意が必要です。

範囲

範囲で注意するべき点は、先述したように検索値が左端になるようにする点と、返す値の列が含まれるように範囲を指定することの2点です。

範囲の指定はシンプルで、「=VLOOKUP(B4,　　」のように検査値まで入力したら半角カンマで区切り、直接表から範囲をドラッグすることで、選択できます。

列番号

列番号は、取り出したい値のある列が、範囲の左端の列から数えて何列目にあるかを記入します。

上画像のケースだと、「姓」が「１列目」、「名」が「２列目」、「点数」が「３列目」なので、列番号は「３」になります。

もし、同じ表で「姓」から「名」を検索したいというケースなら、列番号は「2」になります。

検索方法

検索方法は、検索値を完全一致で取得するか、近似値で取得するかを指定するため、「TRUE」（トゥルー）または「FALSE」（フォールス）の文字を入力します。

完全一致を指定するには「FALSE」を。

近似一致で取得するには、「TRUE」を入力します。

引数の4つ目、検索方法に関しては省略も可能なのですが、その場合、「TRUE」が選択されているのと同じ状態になり、近似一致での検索になってしまいます。

実際にVLOOKUP関数を使用する際は、完全一致で検索するケースがほとんどなので、基本的に検索方法には、「FALSE」を入力することになるでしょう。

「FALSE」は「偽」という意味の単語なので、完全一致を選択するのに入力するのは少し違和感があるかもしれません。

VLOOKUP関数の場合、検索方法は近似一致を行うか？という問いに対し、「TRUE」で「はい」、「FALSE」で「いいえ」と返答するという仕様です。

検索方法の選択は「TRUE」と「FALSE」で勘違いしそうですが、完全一致で検索する場合は「FALSE」と入力しましょう。

VLOOKUP関数の使い方

VLOOKUP関数の使い方を、順を追って解説していきます。

姓から検索し、テストの点数が何点かを調べられるVLOOKUP関数を、一から作成してみます。

結果を判定したいセルに、まずは「＝VLOOKUP（　　」を入力。

次に、一番目の引数を入力するため、B4セルを選択し、参照します。

半角カンマ「,」で区切り、範囲を選択します。

今回は姓から点数を検索するので、姓が左端になるように、点数までの範囲を選択します。

VLOOKUP関数では検査値を求める列を左端にする必要がある点に注意しましょう。

3番目の引数は、参照する列の数字を入力します。今回は左から3列目の点数を求めたいので、半角カンマで区切り、「３」を入力。

引数の4番目は、完全一致で検索して欲しいので、半角カンマで区切って「FALSE」と入力。

最後に、「）」で引数を閉じます。ちなみに、「FALSE」は数字の0で代用可能です。

あとはエンターで確定すれば、VLOOKUP関数の完成です。

今回はB4セルにリストを作成しているので、リストから姓を変更すれば、それに対応した点数を検索できます。

Excel・VLOOKUP関数でエラーが出る原因と対策

VLOOKUP関数は便利ですが、使い慣れないうちはエラーに悩まされがちな関数でもあります。

エラーが出る原因と、その解決方法を解説していきます。

• Excel・VLOOKUP関数で「#N/Aエラー」が出た時
• Excel・VLOOKUP関数のエラー：完全一致しない

Excel・VLOOKUP関数で「#N/Aエラー」が出た時

「#N/Aエラー」は「No Assign」（ノー　アサイン）の略で、検索値が見つからないという意味のエラーです。

上記が、「#N/Aエラー」の原因になる、ありがちなミスです。

検索したい列が範囲に含まれていない

検索したい列が、引数2番目の範囲に含まれていないと、「#N/Aエラー」になります。

VLOOKUP関数はCOUNT関数などとは違い、値を返す列だけを範囲にするわけではありません。

忘れずに、検索値を求める列を左端にいれ、値を返す列まで含めて、範囲を設定してください。

検索値が範囲の一番左に無い

VLOOKUP関数の範囲は検索値を求める列を、一番左に設定しておく必要があります。

繰り返し述べていますが、この範囲の左に検索値を求める列が無い、というのはありがちなミスです。

「「#N/Aエラー」が出る場合は、検索したい列が一番左にあるか、確認してください。

表の左が会員番号で、その次が氏名、こういったケースで検索を名前にしていると、会員番号まで表に含んでしまい、エラーの原因になるという事例があります。

全角・半角が間違っている

検索値が「Ｙａｍａｄａ」（全角）、表の求める検索列に「yamada」（半角）と入力していると、VLOOKUP関数は「#N/Aエラー」を返します。

VLOOKUP関数では半角・全角を異なるものとして識別します。

半角・全角が異なると同じ文字列であっても「#N/Aエラー」になるので、半角・全角に違いがないか、確認してください。

検索値や検索される値に半角の「~」を使っている

VLOOKUP関数は半角の「~」を正しく識別できないという仕様があり、半角の「~」を含んだ検索を行うと「#N/Aエラー」を返します。

上画像は、関数は正しいのですが、「~」を地名の間に使用しているため、エラーになっています。

こういった場合は、「~」（半角）を「～」（全角）に変更すれば、VLOOKUP関数が正しく動作するようになります。

Excel・VLOOKUP関数のエラー：完全一致しない

値はエラーにはならないけれど、回答が間違っている。

数値を求める時などに、正しくない答えが表示されてしまう。

こういったケースでは、検索方法が「FALSE」になっていないのが原因です。

VLOOKUP関数の引数の4番目、検索方法は省略が可能です。

しかし、省略した場合は「TRUE」の近似値での検索を選択した状態になります。

そのため、数字を求めると近似値を拾い上げてしまい、答えが完全一致しない、という問題が起きてしまいます。

上画像では、「FALSE」を省略した結果、求める値がおかしなことになっています。

VLOOKUP関数で完全一致が必要な場合は、4番目の引数に「FALSE」を入力しましょう。

VLOOKUP関数の応用

VLOOKUP関数を使っていくときに便利な、応用を解説していきます。

• Excel・VLOOKUP関数の使い方・応用：範囲を別シートから参照する方法
• Excel・VLOOKUP関数の使い方・応用：結果に0を表示しない方法
• Excel・VLOOKUP関数の使い方・応用：IF関数と組み合わせて使う
• Excel・VLOOKUP関数の使い方・応用：複数条件
• Excel・VLOOKUP関数の使い方・応用：複数結果
• Excel・VLOOKUP関数の使い方・応用：ワイルドカードを使って部分一致で検索する方法

Excel・VLOOKUP関数の使い方・応用：範囲を別シートから参照する方法

VLOOKUP関数を入力したいセルと、表を記載しているシートが異なる。こういったケースはよくあるはずです。

範囲を別シートから選択する場合、やり方はシンプルで、範囲の部分に別シートの必要なセルを選択するだけです。

結果を入力したいセルに「＝VLOOKUP（検索値,　　」まで入力したら、範囲選択したいシートに切り替えます。

ドラッグで表の必要な範囲を選択。

「=VLOOKUP(B4,表!C8:E12,3,FALSE)」のように、範囲のセルの前に「シート名！」と入力されていれば、別シートの参照に成功です。

※例の場合はシート名が「表」なの「表！」に。シート名が「Sheet1」なら、「Sheet1！」になります。

後は、数式バーで、列番号、検索方法を入力し、関数を確定します。

別シートで範囲選択後、元のシートのセルで関数の続きを入力しようとすると、シートを切り替えた時点で範囲がそのシートのセルに切り替わってしまう仕様があります。

これを避けるために、別シートの範囲選択後は、数式バーで、関数の続きを入力しましょう。

関数を確定すれば、別シートの範囲を参照し、VLOOKUP関数を使うことができます。

引数の入力は関数の引数ウィンドウからも行えます。

セルに「＝VLOOLUP（　」まで入力したら数式バー横の「ｆｘ」をクリックすれば、関数の引数ウィンドウが開きます。

範囲横のアイコンをクリックすると選択ができるので、こちらから別シートの範囲を選択することも可能です。

Excel・VLOOKUP関数の使い方・応用：結果に0を表示しない方法

VLOOKUP関数では参照先のセルが空白の場合、「0」を返します。

この0を表示したくない場合は、VLOOKUP関数後ろに、「&””」を加えるだけで、値を空白にして返すことができます。

例

=VLOOKUP(H8,$C$5:$E$9,3,FALSE)&””

「“”」は空白を意味するので、これを、文字列を結合する演算子「＆」でVLOOKUP関数とつなげるだけで、0を空白にできます。

Excel・VLOOKUP関数の使い方・応用：IF関数と組み合わせて使う

結果に条件を追加してVLOOKUP関数を使いたい。

そんな時は、VLOOKUP関数とIF関数を組み合わせることで、課題を実現できます。

例えば、「姓」から「点数」を取り出し、その結果を元に「合格」か「不合格」を判定したい。

こういったケースで、VLOOKUP関数とIF関数の組み合わせが使えます。

IF関数は、条件が真または偽の場合に、それぞれ別の結果を返すことができる関数です。

＝IF(条件式,真の場合,偽の場合)

条件式は下記の論理式が使えるので、さまざまな応用が利きます。

〇 > △	〇は△よりも大きい
〇 >= △	〇は△以上
〇= △	〇は△と等しい
〇<> △	〇と△は等しくない
〇 < △	〇は△よりも小さい
〇<= △	〇は△以下

例として、VLOOKUP関数で姓から点数を取り出し、それをIF関数で判定する組み合わせを作ってみます。

セルにVLOOKUP関数の結果を求め、それをIF関数で判定します。

使用する関数は、それぞれ下記のとおりです。

VLOOKUP関数
=VLOOKUP(B4,C8:E12,3,FALSE)
姓を検索し、対応する点を取り出す

IF関数
= IF（VLOOKUP関数＞=70,”合格”,”不合格”)
参照したデータ（この場合はVLOOKUP関数で求めた値）が70点以上なら合格、70点以下なら不合格

IF関数の参照セルにVLOOKUP関数を組み込みたいので、関数は「=IF(VLOOKUP(H20,B19:E24,4,FALSE)>=70,”合格”,”不合格”)」とします。

関数が2つあるので見かけは少し複雑に見えますが、参照する値に、VLOOKUP関数を入れているだけのシンプルな関数です。

VLOOKUP関数で参照しているセル、B4に山田と入れると、点数が70点なので、合格と表示。

B4セルに田中と入れると、点数が55点なので、不合格と表示されます。

これで、VLOOKUP関数とIF関数の組み合わせが完成です。

Excel・VLOOKUP関数の使い方・応用：複数条件

VLOOKUP関数は基本的に一つの検索条件に対しての値を返します。

そのため、検索条件を複数にしたい時は、工夫が必要です。

例えば、「学年」と、「姓」の二つを条件にして値を探すときなどに、工夫が必要になります。

工夫といっても、解決法はシンプルです。

二つの条件を結合したセルを新しく作成し、それをVLOOKUP関数の検索値として参照すれば、複数の条件を検索する事ができます。

例として、「学年」と「姓」、二つの条件から、「点数」を取り出すVLOOKUP関数作成してみます。

まずは、表の左に、学年＆姓の項目を準備し、一番上のセルに、「=C7&D7」と入力します。

今回の条件は学年プラス姓なので、それをつなげた参照用のセルを、検索する際に一番左になるように、準備していきます。

イチイチ手入力は面倒なので、ここも関数を使って条件を結合します。

セルを参照し、「＆」でつなげれば、簡単にデータを準備可能です。

「=C7&D7」を入力したら、オートフィルでそれを表の一番下まで伸ばします。

あとは、「学年＆姓」のセルを検索するVLOOKUP関数を入力すれば、「学年」と「姓」二つの条件を満たすデータを取り出すことができます。

例の場合だとD3セルに「=VLOOKUP(C3,B7:E15,4,FALSE)」と入力。

これで、「学年」と「姓」二つの条件を満たす「点数」を求めることができます。

Excel・VLOOKUP関数の使い方・応用：複数結果

検索に対して複数の結果を得るには、以下の手順を行っていきます。

例として、「学年」から検索し、「姓」と「点数」の二つの値を取り出す方法を解説していきます。

まずは、COUNTIF関数を使って、表の左に連番を振っていきます。

表を広げて連番の項目を作り、一番上のセルに、下のCOUNTIF関数を入力。

=COUNTIF($C$4:C4,C4)

COUNTIF関数の入力が終わったら、表の下までオートフィルで広げます。

一番目に出てくる「3年」の連番が「1」に、2番目に出てくる「3年」の連番が「2」に、3番目に出てくる「3年」の連番が「3」になっているのが確認できます。

同じ「3年」の人物を異なるデータとして識別するため、こうやって連番をつけています。

次は、複数条件で検索する方法で解説したのと同じように、「連番」と「学年」を「＆」でつなげたセルを表に左端に作成していきます。

検索対象としてこのセルが必要になるので、表の左に作るようにしましょう。

今回は連番＆学年の順でつなげると「13年」や「23年」のように見た目が別の意味に見えてしまいます。

そこで、順番を変えて、学年＆連番として検索値を作成していきます。

学年＆連番の項目を作り、一番上のセルに、「=C4&B4」と入力。

オートフィルで表の下まで広げ、表を完成させます。

ここで、VLOOKUP関数を出力する欄の左側にも連番の項目を作り、入力していきます。

ここに入れる数値は単純に上から１，２，3というように数字を増やしていけば大丈夫です。

ここから、VLOOKUP関数を使用していきます。

まずは、「姓」の欄の一番上に、「=VLOOKUP($G$4&$I4,$A$4:$E$12,4,FALSE)」と入力します。

検索値は、3年1を検出しなければいけないので、学年＆連番となるように、「G4＆I4」とします。

また、今回は複数結果を表示するためにVLOOKUP関数をコピーして使うので、参照するセルがズレないように、絶対参照を使います。

学年のG4は固定で参照するので、F4キーを一回押し、行・列共に固定します。

連番のI4は列のみ固定し、行を下にずらして参照していくので、F4キーを3回押して、Iの前にだけ「＄」を入れ、列を固定します。

「=VLOOKUP($G$4&$I4　」

範囲は「学年＆連番」の項目から、点数までを選択。この表は固定で参照するので、F4キーを一回押して、絶対参照にしておきます。

「姓」の値を取り出すVLOOKUP関数は、点数は含めなくても構わないのですが、次に作るVLOOKUP関数に合わせて、ここでも範囲に選択しておきます。

「=VLOOKUP($G$4&$I4,$A$4:$E$12　」

列は、左から4番目の「姓」を値として取り出すので、「4」と入力。

「=VLOOKUP($G$4&$I4,$A$4:$E$12,4　」

検索方法は完全一致なので、「FALSE」を入力します。

「=VLOOKUP($G$4&$I4,$A$4:$E$12,4,FALSE)」

VLOOKUP関数の入力が終わったら、正しいのを確認して、表の下までオートフィルで伸ばします。

これで、3年の3名分の「姓」をとりだせました。

次は、点数の欄に、VLOOKUP関数を入力していきます。

ここで使う関数は簡単で、隣のセルに入力したVLOOKUP関数から列の部分を「5」に変更するだけです。

=VLOOKUP($G$4&$I4,$A$4:$E$12,5,FALSE)

後は、表の下までオートフィルでコピー。

これで、学年から「姓」と「点数」の複数の結果を得るVLOOKUP関数が完成です。

学年を切り替えれば、対応の結果を得られます。

1年

2年

連番の最大値が必要な場合は、MAX関数を使って取得しておきましょう。

Excel・VLOOKUP関数の使い方・応用：ワイルドカードを使って部分一致で検索する方法

VLOOKUP関数を使う場合、検索値であいまい検索をしたいケースもでてきます。

たとえば、姓名の入力されているセルを、名だけで検索したいケースなどがありますよね。

こういった前方一致などの部分一致で検索するときに使えるのが、ワイルドカードです。

Excelに使われるワイルドカードは、主に下記の2つです。

「？」は一文字を表すワイルドカードで藤田、小田など、〇田というワードを検索したい時に、「？田」のように使用すると、藤田、小田というワードを検索できます。

ただし、「？」は一文字だけを代替するので、検索対象に小山田という人がいた場合は、「？田」では検索できません。

○○田を検索するには「？？田」とする必要があります。

文字数の制限なく使えるのが、「*」です。「*田」と使えば、藤田、小田に加えて、小山田も同時に検索することができます。

実際にワイルドカードを使用した例を確認していきます。

検索値に「姓」だけ、表には「姓名」が記載されている場合、通常だと検索が一致せず、VLOOKUP関数はエラーになります。

こういったケースで、ワイルドカードが活用できます。

今回は姓の後ろの文字列をワイルドカードで代替したので、引数の検索値を「姓＆“*”」とします。

関数の全体は、下記のとおりです。

=VLOOKUP(G4&”*”,C4:E8,3,FALSE)

参照されるセルに「姓」が入力されており、その後ろの部分、「名」を「*」（アスタリスク）で代替しています。

ワイルドカードを使用する際は、「＆」で繋げること、ワイルドカード自体をダブルクォーテーションで囲むことの2点に注意しましょう。

ワイルドカードを置く一致によって、前方一致や後方一致などさまざまな応用が可能です。

XLOOKUP関数が使えるとより便利に

XLOOKUP関数はOffice365向けに新実装された関数で、VLOOKUP関数の後継に当たる関数です。

XLOOKUP関数はより便利になっており、検索値が検索対象の 左端 になくても使えます。

値を取り出す列を数字で指定する必要もありません。

また、「スピル」に対応しているので、オートフィルによる参照のズレも気にしなくて済みます。

XLOOKUP関数の構文

=XLOOKUP（検索値, 検索範囲, 戻り配列, 見つからない場合, 一致モード, 検索モード）

引数が6つもあるのでVLOOKUP関数より難しく感じますが、実際に必須なのは最初の3つだけであり、入力も楽です。

ただし、XLOOKUP関数はOffice365向けに新たに実装された関数なので、使用できるのはマイクロソフト365、office2021を利用している方になります。

Excel 2016 および Excel 2019 では、XLOOKUP関数を使用できません。

自身の使っているExcelのセルに「＝X」と入力し、候補にXLOOKUP関数が表示されない場合は、XLOOKUP関数の利用ができなくなっています。

XLOOKUP関数の使い方

実際に使用した例を解説します。

「姓」から、「点数」を戻り値として取得します。

値を取得したいセルに、「＝XLOOKUP（　」と入力し、検索したいセルを選択します。

今回は姓を入力しているB4セルを参照。

半角カンマで区切り、検索値を探す範囲を選択。

例のケースだと、「姓」が入力されているC８からC12を範囲指定。

VLOOKUP関数だと範囲に検索する列から戻り値を取得する列までを範囲指定していましたが、ここで範囲選択するのは、検索される範囲のみでOKです。

3番目に、戻り値を取得したい範囲を選択します。

今回は、点数の入力されているE8からE12を範囲選択。

最後に、引数をカッコで閉じて、関数を確定。

これで、答えを求めることができました。

検索値、戻り値ともに範囲を直接指定すればいいので、VLOOKUP関数よりも非常に分かりやすいです。

XLOOKUP関数を利用できる環境にある方は、こちらを利用するのがおすすめです。

まとめ

VLOOKUP関数は業務の効率化にもつながる便利な関数です。

記事を参考に、VLOOKUP関数を使いこなしていただけると幸いです。

ノジマではパソコンに関するご相談などを受け付けています。

Officeソフトをはじめ、パソコンの操作で気になることがあれば、お気軽に店頭のスタッフにお尋ねください。

※ご相談内容によっては有料の場合があります。