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

VLOOKUP関数で別シートを使用する際に、難しいのが「別シートの参照方法」です。

本記事では、別シートの参照方法を画像を用いながら丁寧に紹介。また、別シートを参照して#N/Aのエラーが出る場合の対処法も紹介しているので、ぜひ参考にしてくださいね。

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

VLOOKUPで参照したい表と参照結果が同じシートにあると、「見にくくなってしまう」「管理しにくい」などの問題があります。VLOOKUP関数を利用するときには、Excelの別シートを参照し、見た目にもよく使いやすいシートを作成しましょう。

VLOOKUP関数でExcelの別シートを参照するためには、別シートを作成し、「VLOOKUP関数」を書くといういくつかのステップを順番にこなす必要があります。

本記事では、VLOOKUP関数でExcelの別シートを参照してデータを抽出する方法を一つひとつのステップに分けて丁寧にご紹介します。

STEP1．別シートを作成する

まずは、別シートを作成することから始めます。画面左下にある「+」マークをクリックしましょう。

「+」をクリックすると、「シート2」と表示されます。

STEP2．シートの名前を変える

必要ならば、シートの名前を変えると管理がしやすくなります。「シート2」と書かれた文字の上をクリックしましょう。

「シート2」をクリックした後、入力できるようになるので、希望のシートの名前を入力します。

STEP3．表を移動する

すでに表を作成している場合でも、もう一度表を作り直す必要はありません。作成した表を選択し、左クリックを行います。「コピー」ではなく「切り取り」を選択しましょう。

切り取りしたい範囲を選択後、MACの場合「Command＋X」Windowsの場合「Ctrl+X」でも同じ操作が行なえます。

「別シート」に移動して、左クリック後「貼り付け」を選択します。

同様の操作を、MACの場合「Command＋V」Windowsの場合「Ctrl+V」で行えます。

「切り取り」と「コピー」の違いは、「貼り付け」を行った後に、コピーした内容がなくなるかなくならないかです。

STEP4．VLOOKUP関数を確認する

表を移動する前に、予めVLOOKUP関数を入力していた場合、VLOOKUP関数は自動的に別シートを参照した内容に変更されます。

STEP5．VLOOKUP関数を入力する

事前にVLOOKUP関数を入力していない人もいるのではないでしょうか。

以下では、表を別シートに作ってから、VLOOKUP関数を入力する方法を以下でご紹介します。

VLOOKUP関数は「＝VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])」と使用します。

まだ使い方がわからないという人は「VLOOKUP関数の使い方とは？準備から基本の式の作り方を解説」を参考にしてくださいね。

今回は、管理番号から商品名を参照するVLOOKUP関数を入力します。

まずは、「シート1」のVLOOKUP関数を参照したい場所に、「=VLOOKUP(検索値,)」を入力します。

次は「別シート」に移り、検索範囲を選択します。自動的に「'別シート'!A1:C6」と入力され、簡単に別シートから参照できます。

「＝VLOOKUP(検索キー, 範囲, 指数, [並べ替え済み])」の指数や並び替え済みを入力します。

指数や並び替え済みの意味がわからない人は「VLOOKUP関数の使い方とは？準備から基本の式の作り方を解説」で復習することをおすすめします。

STEP6．VLOOKUP関数をコピーする

VLOOKUP関数をドラッグしてコピーする前に、範囲がずれてしまわないように「$」をつけましょう。

上記の画像のように、「$」つけていないと範囲がずれてしまいエラーが出てしまいます。

「=VLOOKUP(C2,'別シート'!A$1:C$6,2,false)」のように、ドラッグをする前は忘れずに「$」を使用しましょう。

VLOOKUPで別シートを参照して#N/Aのエラーが出る場合の対処法

「#N/Aのエラー」が出てしまってうまく参照ができないという人もいるのではないでしょうか。

以下では、VLOOKUPで別シートを参照して#N/Aのエラーが出る場合の原因と対処法を簡単にご紹介します。

より詳しい解決策を知りたい人は「VLOOKUPで#N/Aのエラーが出たときの7つの原因と対処方法、表示させない方法を紹介」を参考にしてくださいね。

原因1．全角と半角が一致していない

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の1つ目は、全角と半角が一致していないことです。

EXCELやGoogleスプレッドシートは「全角」と「半角」を別のものだと認識しています。

そのため、全角と半角が一致していないことが原因でエラーが出ている場合は、「全角」と「半角」を一致させるといいでしょう。

EXCELやGoogleスプレッドシートを使用するときは、このようなエラーを起こさないためにも、すべて半角で入力することをおすすめします。

原因2． 検索範囲の左端に検索値が見つからない

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の2つ目は、検索範囲の左端に検索値が見つからないことです。

検索値の設定ミスでも、#N/Aのエラーが出てしまいます。このようなエラーを防ぐためには、検索範囲の一番左側に検索値が入っていることを確認しましょう。

検索範囲の左側を参照する方法を知りたい人は「VLOOKUP関数の左側を参照する3つの方法を画像付きで徹底解説！」を参考にしてくださいね。

原因3．検索値が昇順になっていない

VLOOKUP関数で別シートを参照してエラーが出る原因の3つ目は、検索値が昇順になっていないことです。

「TRUE」で検索している場合は、必ず検索値が昇順になっていることが必要です。検索値が昇順になっていないと、参照したいもの以外を間違って参照する可能性があります。

特に、表に付け足しをする際は昇順にすることを忘れないでおきましょう。

原因4．完全一致を指定しているが、一致しているものが見つからない

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の4つ目は、完全一致を指定しているが、一致しているものが見つからないことです。

完全一致を指定する「FALSE」を入力している場合、全く同じものしか見つけることができません。

例えば「42.3」という値と「42」は違うものだとみなしてしまいます。

「FALSE」の場合に、#N/Aのエラーを出さないためには、正確に値を入力することが必要です。

もしくは、完全一致にしたくない場合は「TRUE」を使用するといいでしょう。

原因5．検索値が絶対参照になっていない

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の5つ目は、検索値が絶対参照になっていないことです。

すでにご紹介しましたが、検索値を「$」で固定していない場合、検索値がずれてしまいます。

「$」を入力することで、すぐ後ろの文字を固定する役割があります。「A$1」のものを下にドラッグしても「A$1」で変化しません。横にドラッグすると、横のセルが「B$1」に変化します。

検索地を固定したい場合は、絶対参照にすることを忘れないようにしましょう。

原因6．検索値が小数で計算した値になっている

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の6つ目は、検索値が小数で計算した値になっていることです。

EXCELの場合は「浮動小数点演算」というコンピューターのエラーで#N/Aのエラーが出てしまう可能性があります。

解決策として、「検索値が小数で計算した値にしないこと」「ROUND関数」を使用することなどがあります。

原因7．検索値＜検索範囲の最小値となっている

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の7つ目は、検索値＜検索範囲の最小値となっていることです。

「TRUE」を使用していても、検索値が検索範囲の最小値よりも小さい場合はエラーが出てしまいます。

検索値を変更したり、検索範囲を増やしたりして対処しましょう。

原因8． 「〜」が「~」になっている

VLOOKUP関数で別シートを参照して#N/Aのエラーが出る原因の8つ目は、 「〜」が「~」になっていることです。

EXCELの場合「~」を使用した場合、#N/Aのエラーが出てしまいます。対処法としては、半角の波ダッシュ「~」を、全角の波ダッシュ「〜」に変えることです。

Googleスプレッドシートの場合は、「~」を使用してもエラーが出ないので安心して使用できますよ。

具体的な解決策を知りたい人は「VLOOKUPで#N/Aのエラーが出たときの7つの原因と対処方法、表示させない方法を紹介」を参考にしてくださいね。

Excelの別シートを参照する方法をマスターしよう

本記事では、Excelの別シートを参照する方法を画像を使って丁寧にご紹介しました。

複雑そうに見えますが、別シートを作成したり、表の移動をしたりと一つひとつの動作は難しくありません。丁寧にステップごとに入力し、VLOOKUP関数を使えるようになりましょう。

本記事を参考に、別シートを参照する方法を学んで実践してみてはいかがでしょうか。

【関連記事】

VLOOKUP関数で複数の条件を設定・抽出する方法を解説

VLOOKUP関数で複数の条件を設定し、抽出する作業を行いたいと思っている人もいるのではないでしょうか。 本記事では、VLOOKUP関数で複数条件を設定する方法やINDEX＋MA...

VLOOKUP関数の使い方とは？準備から基本の式の作り方を解説

VLOOKUP関数は他の基本的な関数と比べると複雑でわかりにくい関数です。「なんとなく難しいイメージがある」「使いこなせなさそうで避けている」という方も多いのではないでしょうか。 本記事で...

VLOOKUP関数とは？役立つシーンと、基本の型を紹介

Excelやスプレッドシートを活用するときに覚えておきたい関数のひとつである「VLOOKUP関数」。なんとなく難しそうなイメージがある人も多いのではないでしょうか。 本記事では、VLOOK...