Excel関数「VLOOKUP」範囲の１列目で値を検索し、指定列と同じ行の値を返す

こんな悩みを解決します。

最後まで読んで頂き、実践して、ここへ来なくなった頃には、

あなたはスキルアップしている事でしょう。

さて、この記事の題目は

Excel関数「VLOOKUP」って何？

指定された範囲の１列目名で特定の値を検索し、

指定した列と同じ行にある値を返す場合は

「VLOOKUP関数」を使用します。

式の書き方は

「=VLOOKUP(検索値,範囲,列番号, [検索方法])」

となります。

 

又、似ている関数として

１行または１列のみのセル範囲、または配列に含まれる値を返す場合に

「LOOKUP関数」と

指定したテーブルまたは配列の先頭行で特定の値を検索し、

指定した列と同じ行にある値を返す場合に

「HLOOKUP関数」があります。

後程こちらも簡単に説明させて頂きますが

この３つの中では、最も使用頻度が多いと思われる

「VLOOKUP関数」の使い方を紹介させて頂きます！

1限目「VLOOKUP」使い方：表の作成

今回は、情報を処理するシートと、情報を管理するシートに分けた方式で作成します。

まずは、情報を処理するシート＝検索用のシート名を「検索」とし、

情報を管理するシート＝百人一首の一覧用のシート名を「DB（データベース）」とします。

※シート名は自由です。又、シートを分けなくても機能します。

 

「DB」のシートに、百人一首の一覧を作成していきます。

左から「番歌」「詠み人」「歌」「歌（読み）」

という項目を作成し、全ての情報を入力します。

一番上の行は項目の順番を入力しておきます。

これは、ちょっとした仕掛けなのですが、後程説明させて頂きます。

 

又、「番歌」の列には「セルの書式設定」の「ユーザー定義」の

種類の初期値「G/標準」の後ろに「” 番歌”」を追記します。

よって「G/標準” 番歌”」となります。

 

Excel機能について、もっと詳しく知りたい方は

↑コチラをご覧ください

 

次は「検索」シートの作業となります。

「DB」のシートと同様に、左から「番歌」「詠み人」「歌」「歌（読み）」とし

今回は、複数表示させる仕様とするので、一番左に「No」も追加します。

入力するセルと結果を表示するセルは区別しておくとよいでしょう。

図は、入力するセルを青、結果を表示するセルはゴールドという設定です。

 

「DB」のシートと同様に「番歌」の列には「セルの書式設定」の「ユーザー定義」の

種類の初期値「G/標準」の後ろに「” 番歌”」を追記します。

よって「G/標準” 番歌”」となります。

この設定をする事で、数字を入力するだけで

「＊番歌」と表示される様になります。

 

又、入力するのは数値の1～100なので

「データーの入力規則」で入力値を限定する事も可能です。

図の様に、入力値の種類を「整数」データを「次の値の間」とし

「最小値1」「最大値100」と設定します。

 

Excel機能について、もっと詳しく知りたい方は

↑コチラをご覧ください

 

指定した入力値ではない入力があった場合は

図の様にエラーメッセージが表示されます。

 

以上で「VLOOKUP関数」を使う準備が出来ました。

 

 

2限目「VLOOKUP」使い方：式の書き方

ようやく本題の「VLOOKUP関数」ですが、まずは「番歌」に該当した

「詠み人」を表示する為、セル「C3」に関数式を入力していきます。

「=VLOOKUP(」までを入力し、検索値を入力するセルを指定します。

検索値の終わりには「,」（カンマ）を入力するのがルールとなっているので

ここまでをセル名に当てはめて式を作成すると「=VLOOKUP($B3,」となります。

又、「B3」に「$」を付けて「$B3」としていますが

これについては後程、説明させて頂きます。

 

次に情報が入力されている「範囲」を指定します。

情報が入力されている「DB」のシートを選択し

「番歌」「詠み人」「歌」「歌（読み）」が入力されて全ての範囲を指定します。

範囲の終わりには「,」（カンマ）を入力するのがルールとなっているので

ここまでをセル名に当てはめて式を作成すると

「=VLOOKUP($B3,DB!$A$3:$D$102,」となります。

ちなみに別のシートを参照する場合は

シート名の後ろに「!」を付け、セルの範囲を指定するのがルールとなっています。

 

次の「列番号」は、先程の範囲を指定をした一番左の列を「1」とし

取り出したい情報がある「列番号」を入力します。

「詠み人」の列は「2」番目でしたので、式の続きに「2」を入力し

列番号の終わりも「,」（カンマ）を入力するのがルールとなっているので

ここまでをセル名に当てはめて式を作成すると

「=VLOOKUP($B3,DB!$A$3:$D$102,2,」となります。

ここで「DB」のシートで作業した際の、ちょっとした仕掛けを使います。

先程の「列番号」の指定では数値を入力しましたが、この方法以外に

数値が入力されたセル名を指定し、その数値を「列番号」として扱う事も可能です。

ですので、「2」と入力した部分を

「DB」シートの「B1」セルと置き換える事が出来るわけです。

ちょっとした仕掛けにはまだ続きがありますが、後で説明させて頂きます。

 

ここまでをセル名に当てはめて式を作成すると

「=VLOOKUP($B3,DB!$A$3:$D$102,DB!B$1,」となります。

又、「B1」に「$」を付けて「B$1」としていますが

これについても後程、説明させて頂きます。

 

最後の「検索方法」ですが

完全一致とする場合は「0」又は「FALSE」

近似一致とする場合は「1」又は「TRUE」となります。

今回の「番歌」は固有の番号なので、完全一致の「0」を使用します。

最後は「)」を入力するのがルールとなっているので、ここまで入力して完成です。

ここまでをセル名に当てはめて式を作成すると

「=VLOOKUP($B3,DB!$A$3:$D$102,DB!B$1,0)」となります。

式の入力が終わると「DB」シートに戻ると同時に、結果が表示されます。

「10番歌」の「蝉丸」が表示されました。

 

それでは、右隣りの「歌」を表示するセルの入力となります。

実は「詠み人」を表示する関数式の、列番号だけを変更すればよいので。

「DB」シートの「歌」の列番号「3」が入力されたセル名「DB!C$1」を使い

「=VLOOKUP($B3,DB!$A$3:$D$102,DB!B$1,0)」を

「=VLOOKUP($B3,DB!$A$3:$D$102,DB!C$1,0)」と

一部変更した式を入力すれば完成・・・ですが、入力の必要はありません。

セル「C3」をコピーして、セル「D3」に貼付けをすれば

「=VLOOKUP($B3,DB!$A$3:$D$102,DB!C$1,0)」となり

「DB!B$1」から「DB!C$1」へ変化しました。

これは、関数式が入力されたセルのコピーと貼り付けの特徴である

コピー元と貼り付け先のセル位置の差が、貼り付け先の関数式内のセル位置にも影響する

という仕様が反映された為であります。

そして、何度か出てきた「$」はこの影響を無効にする効果があり

「$」が付けていない列番号「B」に対して、セル位置の差が右に1つ分なので

「C」へと変化したという事になります。

又、上下のセル位置の変化は無いので「$」を付けていない行番号には影響はありません。

 

作業に戻りますが、要するに

セル「C3」をコピーし、結果表示するセルを全選択し、貼り付けをすれば完了という事です。

この様に「$」は非常に役に立ちますので覚えておいて下さい。

 

一応、セル「E7」の関数式を確認してみます。

セル「C3」の「=VLOOKUP($B3,DB!$A$3:$D$102,DB!B$1,0)」に対して

セル「E7」は「=VLOOKUP($B7,DB!$A$3:$D$102,DB!D$1,0)」と

「$」の付いていない部分に差の影響が適用されていました。

 

ここで注意ですが、「検索」シートの項目の配列順と

「DB」シートの項目の配列順が同じ作りになっている場合にのみ有効です。

 

以上で、完成となります。

 

3限目「VLOOKUP」使い方：式の書き方

「LOOKUP関数」関数についてですが

列番号を指定できない「VLOOKUP関数」というイメージです。

式の書き方は

「=LOOKUP(検査値,検査範囲,[対応範囲])」

となります。

 

百人一首の例で、簡単に式を作成してみます。

「=LOOKUP(」までを入力し、検査値を入力するセルとなるのは「C3」ですので

「=VLOOKUP($C3,」となります。

 

次の検査範囲は「DB」のシートから「番歌」の範囲を選択し

ここまでの式は「=LOOKUP($C3,DB!$A$3:$A$102,」となります。

 

最後に対応範囲ですが「DB」のシートから「歌」の範囲を選択し

「=LOOKUP($C3,DB!$A$3:$A$102,DB!$C$3:$C$102)」で完成となります。

 

1つの検査値に対して1つの結果であれば「LOOKUP関数」でも十分です。

 

 

4限目「HLOOKUP」使い方：式の書き方

「HLOOKUP関数」についてですが

式の書き方は

「=VLOOKUP(検索値,範囲,行番号, [検索方法])」

となります。

「VLOOKUP関数」との違いは、行が列かという事です。

 

また百人一首の例で、簡単に式を作成してみますが

先に「DB（データベース）」の行と列を逆転したシートを作成しておきます。

情報が横長で非常に見難くなりますので、5番歌までの例で作成します。

 

「=HOOKUP(」までを入力し、検査値を入力するセルとなるのは「C3」ですので

「=HLOOKUP($C3,」となります。

 

範囲は「DB」のシートから「番歌」の範囲を選択し

行番号を「歌」の行である「3」の完全一致とすると

「=HLOOKUP($C3,DB!C1:G4,3,0)」となります。

 

5限目「VLOOKUP」おまけ：ちょっとした仕掛け

ちょっとした仕掛けが残っていましたね、

「VLOOKUP関数」の「DB」でもし情報列を増やしたい場合

列の挿入をすればこんな風になり、セルC1の3がセルD1に移動してしまいます。

挿入に対して「VLOOKUP関数」の式は追従してくれますが

入力した3はそのままなので、何もない3列目の結果が表示され0という結果になります。

そこで、一番上に入力した番号は左のセルに+1をした数値になる様にしておき

挿入した際は

セルを右にドラッグすれば1づつ加算されていくので、セルD1の値が4になり

列番号をセルで指定していれば一瞬で修復されるという

ちょっとした仕掛けでした。

 

Excel関数「VLOOKUP」まとめ

「VLOOKUP関数」を使う事で

複数の情報を検索して結果を表示する事が可能となります。

情報量が増えてくれば使う機会も多くなりますので、覚えておきましょう。

 

これで悩みが解決し、一つ成長しました。

最後までご観覧頂きありがとうございました！