VLOOKUP関数で別シートを参照してデータを抽出する方法

ここでは、「VLOOKUP関数で別シートを参照してデータを抽出する方法」を解説します。

別シート参照時のポイントや数式の作り方とともに、条件に応じて参照するシートを変える方法も説明します。

なお、VLOOKUP関数で別ファイルを参照する方法は、以下のページに引っ越しました。

　⇒ VLOOKUP関数で別ファイルを参照しデータを引っ張る方法

まずはやりたいことを、例を含めて確認します。

１－１．VLOOKUP関数で別シートからデータを抽出したい

まず、やりたいことは「VLOOKUP関数で別シートを参照しデータを抽出する」です。

「VLOOKUP関数の数式を入れたシート」とは別のシートから、検索値に一致したデータの値を抽出します。

実現できれば「抽出元のデータ（表）を別のシートで管理できるようになる」ため、見た目もすっきり分かりやすくなります。

１－２．例を確認

一つ例を見てみましょう。

まず、「見積書シート」に上のような見積書の様式があるとします。

そして「単価表シート」に、上のような商品ごとの単価が入力された表があるとします。

VLOOKUP関数の別シート参照をすると、「見積書シート」に「単価表シート」から「えんぴつ」の単価を引っ張ってくることができます。

また、商品名を「えんぴつ」にすることでえんぴつの単価が、「消しゴム」にすることで消しゴムの単価が表示されるようになるのです。

次に、VLOOKUP関数を「別シートに表示させるコツ」です。

２－１．別シート参照にする引数は「範囲」だけ

VLOOKUP関数で別のシートから値を抽出したい場合、特定の引数を「別シート参照」にする必要があります。

その引数は、「範囲」です。

VLOOKUP関数は、「範囲」から値を抽出します。

そのため、「範囲」に「データがあるシート」を指定すれば、数式を入れたシートにデータの値を表示させることができるようになるのです。

２－２．数式の例

では数式が実際にどのようになるか、例を見てみます。

先ほどの例を使って説明します。

先ほどの例の場合、単価のセルに入れる数式は次のようになります。

上のように、「範囲」には「シート名!」を付けます。なお、シート名によっては「'シート名'!」となることもあります。

２－３．シート名を自動で入力できる方法がある

「'」が必要な場合があるなど面倒なシート名指定ですが、自動で入力できる方法があります。

この方法であれば、シート名の書き方を覚えなくても別シート参照ができます。

詳しいやり方は、この後解説します。

VLOOKUP関数を別シート参照にする方法を、数式を作りながら確認します。

以下手順です。

（１）セルに「=VLOOKUP(」と入力し、fxボタンを押す

（２）検索値と列番号、検索方法は普段通り入力する

（３）「範囲」の欄をクリックした後、参照先のシートをクリックする

　※ ここがポイントです

（４）対象の範囲をマウスの左ドラッグで選択する

（５）OKボタンを押せば完了

数式作成時のポイントは、次の通りです。

これで自動的にシート名が入ります。

ちなみに、数式は次のようになります。

=VLOOKUP(B15,単価表!B3:C7,2,FALSE)

例のような場合には、「検索値（例だと『商品名』）をリストにすると便利」です。

手入力しなくても選ぶだけで値が入り、VLOOKUP関数も「検索値がない」とエラーにならないからです。

リストにする方法はいくつかありますが、一番簡単な方法を紹介します。

以下手順です。

（１）対象の範囲をマウスの左ドラッグで選択する

（２）「データの入力規則」をクリックする

（３）「入力値の種類」で「リスト」を選ぶ

（４）参照先のシートをクリック後、リストで出したい範囲を指定する

※ 例だと、単価表シートの商品名の入った範囲です

（５）OKボタンを押せば完了

これで、セルをクリックすれば上のようにリストが出るようになります。

入力誤りがなくなり、便利ですよ。

VLOOKUP関数の別シート参照が#N/Aエラーになってうまくいかない場合には、次のことを確認してください。

５－１．検索値に「~」が入っていないか確認する

検索値に小文字の「~」が入っていると、VLOOKUP関数は#N/Aエラーになります。

そもそも#N/Aエラーとは、「検索値がない」場合に表示されるエラーです。

どうやらVLOOKUP関数では、小文字の「~」をうまく処理できないようです。

対処法は、「『~』を大文字（～）にする」ことです。

ちなみに「~」を「～」に置き換える場合には、置換機能ではなくSUBSTITUTE関数を使って行いましょう。

なぜなら、置換機能を使うと下のようにエラーになるからです。

手順はまず作業列を準備し、そこでSUBSTITUTE関数を使って「~」を「～」に置き換えた値に変換します。その後、その値をコピーして置き換えたいセルに「値の貼り付け」を行えばOKです。

なお、SUBSTITUTE関数の使い方を知りたい場合には、次のページを参照してください。

⇒ SUBSTITUTE関数の使い方のページへ

５－２．範囲から外れている・ズレている

こちらは一般的な話ですが、「検索値のあるセルが範囲から外れている・ズレている」ことも考えられます。

別シート参照の場合、「範囲」は「数式を入れるシート」と別になるので気づきにくいですが、指定した「範囲」の左端が「検索対象の列」になっているかも確認しましょう。

最後に、「参照するシートを状況（セル値）によって変える」方法です。

６－１．IF関数と組み合わせる

VLOOKUP関数をIF関数と組み合わせると、セル値に応じてデータを抽出するシートを変えることができます。

数式の例は、次の通りです。

IF関数の引数「真の場合」と「偽の場合」に、それぞれVLOOKUP関数を入れます。

そして、「範囲」で違うシートを指定することにより、条件に合う/合わないによってVLOOKUP関数の参照先を変えることができます。

６－２．例を確認

一つ例を見てみましょう。

まずは上のように、割引率を変えた単価表を２つ用意します。

そして、単価のセルに次の数式を入れました。

=IF(R15=10%,VLOOKUP(B15,単価表1!B4:C8,2,FALSE),VLOOKUP(B15,単価表2!B4:C8,2,FALSE))

これでセルR15が「10%」なら「単価表1」の単価が、それ以外なら「単価表2」の単価が抽出されます。

このように、IF関数と組み合わせることによって、VLOOKUP関数で抽出するシートを変えることができます。

６－３．数式作成時のポイント

ポイントは、次の通りです。

IF関数の使い方を知っていて、VLOOKUP関数で別シート参照にする方法も知っていれば、特に難しいことはありません。

以上、参考になれば幸いです。

VLOOKUP関数のその他の記事はこちらです。

• VLOOKUP関数その他の記事はこちら

• ◎基本的な使い方
• ◎「以上/未満」の条件指定の仕方
• ◎〇番台の条件にする方法
• ◎部分一致の条件指定の仕方
• ◎別シートから値を抜き出す方法
• ◎別ブックから値を抜き出す方法
• ◎数式コピーで横方向の値を簡単に取得する方法
• ◎２番目、３番目以降の値を抽出する方法
• ◎＃N/Aエラーになる３つの原因と対処方法
• ◎＃REF!エラーになる原因と対処方法
• ◎＃VALUE!エラーになる原因と対処方法
• ◎空白を０ではなく空白で表示する２つの方法
• ◎「IF(ISERROR(VLOOKUP」の意味と使い方
• ◎IFERROR関数とVLOOKUPの組み合わせ方
• ◎空白でもエラーでも空白で表示する方法
• ◎複数条件にする方法
• ◎セルや範囲を固定しコピーのズレを防ぐ方法
• ◎文字列を条件にする方法
• ◎列番号の意味と指定の仕方

関数の解説記事一覧へはこちら