エクセル関数の便利帳 [エクセル関数の便利な使い方を事例を交えてご紹介します] エクセル関数の便利帳 [エクセル関数の便利な使い方を事例を交えてご紹介します]

最近見た
ページ

case
07

VLOOKUP関数で繰り返しの入力を簡単に!

検索/行列2022年7月26日
VLOOKUP関数の使い方を説明しているイメージ

エクセルのVLOOKUP(ブイルックアップ)関数を使って、一覧表から条件にあう値に対応するデータ入力する方法について解説します。
また新しい関数であるXLOOKUP関数の使い方についても簡単に説明します。

VLOOKUP関数で一覧から該当の値を自動入力

エクセルでは、VLOOKUP(ブイルックアップ)関数を使って、一覧表から条件にあう値に対応するデータを簡単に入力できます。

例えば、

  • ・繰り返し複数の商品名を入力したい
  • ・名前を繰り返し間違えずに入力しなければならない
  • ・商品名や価格を素早く入力したい

このようなときに、VLOOKUP関数では、一覧表から条件にあう値に対応するデータを自動的に入力することができます。

ここでは、VLOOKUP関数の基本的な書式と機能について紹介します。

VLOOKUP関数の書式

VLOOKUP関数は次のように入力します。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

VLOOKUP関数は、「検索値」に入力された値を「範囲」の中からみつけ、検索値に対応する列番号のデータを表示させることができます。

[関数の挿入]ボタンをクリックして[関数の分類]で[検索/行列]を選択すると、VLOOKUP関数が簡単に入力できます。
また、関数ライブラリからも選択できます。

(注)「検索の方法」は空欄だと近似値(同じ値がなければ検索値に一番近い値)が検索されるので、今回は空欄としておきます。

今回の例では、下表のB列の12行目以降に担当者Noを入力するセルがあります。ここに担当者Noを入力すると、VLOOKUP関数でC列に対応する担当者名が表示されるように、次のように関数を入力してみましょう。

セルC12をアクティブにし、[数式]タブの[関数の挿入]をクリックしている画面イメージ

1VLOOKUP関数を入力するセルC12をアクティブにし、[数式]タブの[関数の挿入]をクリックします。

[関数の分類]で「検索/行列」を選択し、[関数名]で「VLOOKUP」を選択して、[OK]ボタンをクリックしている画面イメージ

2[関数の分類]で「検索/行列」を選択し、[関数名]で「VLOOKUP」を選択して、[OK]ボタンをクリックします。

[検索値]に「B12」、[範囲]に「A3:B8」、[列番号]に「2」を入力し、[OK]ボタンをクリックしている画面イメージ

3[検索値]に「B12」、[範囲]に「A3:B8」、[列番号]に「2」を入力し、[OK]ボタンをクリックします。

担当No 3の担当者名がC12に表示された画面イメージ

4担当No 3の担当者名がC12に表示されました。

C13にVLOOKUP関数を入力し、担当者Noに対応した担当者名を表示している画面イメージ

5同様にC13にVLOOKUP関数を入力し、担当者Noに対応した担当者名を表示させます。

ステップアップ:VLOOKUPの式はコピーできないの?

VLOOKUP関数をコピーすると、エラーが表示されることがあります。これは「範囲(A3:B8等)をコピーすると、1行ずつセル範囲がずれて「A4:B9」「A5:B10」のようにセル番地が変わり、正しい範囲が参照できなくなってしまうからです。そこで、コピーをしても範囲がずれないように「絶対値指定」を行います。行列番号の前に「$」を入力して、「$A4:$B$9」のようにすれば、コピーをしても必ず同じ範囲を参照できます。

(注)「絶対値指定」とは、参照するセルの「セル番地を変更しない」ための機能です。絶対値に指定されたセルはコピーしても必ず同じセル番地が参照されます。

セルC12をアクティブにし、[数式]タブの[関数の挿入]ボタンをクリックしている画面イメージ

1セルC12をアクティブにして、[数式]タブの[関数の挿入]ボタンをクリックします。

[関数の分類]で「検索/行列」を選択し、[関数名]で「VLOOKUP」を選択して、[OK]ボタンをクリックしている画面イメージ

2[関数の分類]で「検索/行列」を選択し、[関数名]で「VLOOKUP」を選択して、[OK]ボタンをクリックします。

[検索値]に「B12」、[範囲]に「$A$3:$B$8」、[列番号]に「2」を入力し、[OK]ボタンをクリックしている画面イメージ

3[検索値]に「B12」、[範囲]に「$A$3:$B$8」、[列番号]に「2」を入力し、[OK]ボタンをクリックします。

式を下のセルにコピーしている画面イメージ

4式を下のセルにコピーします。

VLOOKUP関数は、「表引き関数」ともいわれます。あらかじめ繰り返し入力する項目を表としてまとめておきます。そしてVLOOKUP関数を利用して、表の値を参照して簡単に入力することができるようになります。式をコピーするとき、「範囲」が含まれている関数は絶対値指定を行うとスムーズに操作ができるようになります。

ここまでは2列の一覧表でしたが、3列以上ある一覧表から2列目3列目を参照して表示したい場合もセルのコピーでは正しく表示されません。

「C12」セルをそのまま「D12」セルにコピーしても担当地区は表示されず、エラーとなった画面イメージ

「C12」セルをそのまま「D12」セルにコピーしても担当地区は表示されず、エラーとなる

VLOOKUPではコピーしても列番号はそのままなので、別の列を指定したい場合はあらためて入力しなおさなければいけません。

ここではA列に対応するBとC列を表示させたいとした場合、検索範囲を絶対参照にした上で、検索値の列番号を絶対参照とし、B列を表示さたいところの[列番号]に「2」を入力し、C列を表示させたいところの[列番号]に「3」と入力することでコピーしてもずれなくなります。

(補足)下図では検索値を「$B12」として、列のみ絶対値指定しています。このように列のみ絶対指定すると、セルをコピーした時に参照する行は変化しますが、参照する列は変化しないように指定することができます。

[検索値]に「B12」、[範囲]に「$A$3:$B$8」、[列番号]に「2」を入力している画面イメージ

1[検索値]に「$B12」、[範囲]に「$A$3:$B$8」、[列番号]に「2」を入力し、[OK]ボタンをクリックします。

「C12」セルをコピーして「D12」セルに貼り付け、[列番号]に「3」を入力している画面イメージ

2「C12」セルをコピーして「D12」セルに貼り付け、[列番号]に「3」を入力します

担当No 3の担当地区がD12に表示された画面イメージ

3担当No 3の担当地区がD12に表示されました。

ステップアップ2:XLOOKUP関数を使うとさらに便利に!(Microsoft 365)

Microsoft 365以降のバージョンをお使いであれば、XLOOKUP関数を使うことも出来ます。

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

VLOOKUP関数でコピーする度に列番号を変更するのは意外と手間です。

そこでXLOOKUP関数の出番です。

セルC12をアクティブにし、[数式]タブの[関数の挿入]をクリックしている画面イメージ

1XLOOKUP関数を入力するセルC12をアクティブにし、[数式]タブの[関数の挿入]をクリックします。

[関数の分類]で「検索/行列」を選択し、[関数名]で「XLOOKUP」を選択して、[OK]ボタンをクリックしている画面イメージ

2[関数の分類]で「検索/行列」を選択し、[関数名]で「XLOOKUP」を選択して、[OK]ボタンをクリックします。

[検索値]に「B12」、[検索範囲]に「$A$3:$A$8」、[戻り範囲]に「$B$3:$C$8」を入力し、[OK]ボタンをクリックしている画面イメージ

3[検索値]に「B12」、[検索範囲]に「$A$3:$A$8」、[戻り範囲]に「$B$3:$C$8」を入力し、[OK]ボタンをクリックします。

担当Noに対応する担当者と担当地区が表示されたイメージ

4担当Noに対応する担当者が「C12」セルに、担当地区が「D12」セルに表示されました。

VLOOKUP関数は参照範囲の左端の列が検索値の検索対象になりますが、XLOOKUP関数は検索対象の列より左の列を取り出すことができます。

以下はC12の検索値より左側にある値を取り出す例です。

セルB12をアクティブにして、[数式]タブの[関数の挿入]ボタンをクリックします。[検索値]に「C12」、[検索範囲]に「A3:A8」、[戻り範囲]に「B3:C8」を入力し、[OK]ボタンをクリックしている画面イメージ

1セルB12をアクティブにして、[数式]タブの[関数の挿入]ボタンをクリックします。[検索値]に「C12」、[検索範囲]に「A3:A8」、[戻り範囲]に「B3:C8」を入力し、[OK]ボタンをクリックします。

C12の検索値より左側にある値(担当No)が表示されたイメージ

2C12の検索値より左側にある値(担当No)が表示されました。

XLOOKUP関数を使えばVLOOKUP関数で不便だったことがあっという間に解決します。

今回はXLOOKUP関数の使い方の一例のみのご紹介となりましたが、便利な関数ですので是非ご利用ください。

※頂いた内容についての返信は行っておりません。個人情報の記載はしないようお願いいたします。

おすすめ
ページ

ページTOP