【完全攻略】エクセルのプルダウンにVLOOKUPを連動させて自動入力する方法
初心者エクセルのプルダウン連動で自動入力はできますか？
連動プルダウンは選択できて楽なのですが、その隣りのセルが毎回手入力で困っています。
Dr.オフィスせっかく連動プルダウンで効率を上げても、そこから先のデータを自分で探して入力するのは手間だよね。
そんなときはVLOOKUP関数の出番だよ！
本記事を読むと、連動プルダウンだけでは対応しきれない場面でも、VLOOKUP関数を組み合わせて必要な情報を自動入力する方法を学べます。
エクセルのプルダウン連動で自動入力する簡単ステップ
- VLOOKUP関数の設定画面で検索値を入力
- 検索範囲を選択する
- 取得したい値のある列番号を指定
- 検索方法に「FALSE」または「0」を入力する
VLOOKUP関数は、設定画面で4つの項目を入力するだけで使えるため、初心者の方でも簡単に設定できます。
慣れてきたら数式を直接書き込むことも可能です。
プルダウンリストの作成方法や便利な応用テクニックについては、こちらで詳しく解説していますので是非ご覧ください。
※本記事は『OS：Windows11』画像は『Excelのバージョン：Microsoft365』を使用しています。
＼ Officeドクター読者限定・無料Q&A ／
この記事の内容でわからないことや、今すぐ解決したいOfficeのお悩みはありませんか？
Officeドクターの中の人が、公式LINEで直接ご質問にお答えします！
下のボタンをタップして、表示された入力欄からそのまま送信してくださいね。
目次
エクセルのプルダウン連動で自動入力を実現！VLOOKUP関数の設定方法
エクセルのプルダウン連動で自動入力を行いたいとき、大変便利なのがVLOOKUP関数です。
今回は、例えばプルダウンで品名を選ぶと、その値段を別表から探し出して隣りのセルに自動入力してくれるVLOOKUP関数について解説します。
まずはVLOOKUPの「左から右」のルールを知っておこう
VLOOKUP関数は、以下の仕組みでデータ範囲の中から目的の値を探し出す関数です。
- 左端の列で値を見つけ
- その位置から右方向へ取得したい値を探しにいき
- 見つけた値を取り出して表示する
Dr.オフィス「VLOOKUPは左から右へ探しにいく関数」だと覚えておこう。
このルールを知っておくと次の解説がわかりやすいよ。
ここからは、下のサンプル画像を見ながらVLOOKUP関数の数式と入力方法を見ていきましょう。
『商品リスト』では、「型番」までは連動入力できますが、「価格」にはプルダウンが設定されていないため、「A-001」の価格は別シートの『マスタ表』から探して入力しなければなりません。
このようなときにVLOOKUP関数を使うと、「価格」への自動入力が可能になります。
VLOOKUP関数は、この4つの項目を入力して設定します
=VLOOKUP(①検索値,②検索範囲,③列番号,④検索方法)
では、「A-001」の価格を自動入力するには、『商品リスト』と『マスタ表』のどの情報をこの数式の①～④に入力すればよいかを見てみましょう。
- 「検索値」は、探したい値「A-001」が入力されているセルD2を指定します
- 「検索範囲」は、「A-001」がある「型番」の列を左端に置き、取り出したい「価格」のあるD列までを含むB2:D8を指定します
- 「列番号」は、「価格」が検索範囲の左端から数えて3列目にあるので3を指定します
- 「検索方法」は、通常は完全一致の「FALSE」または「0」を指定します
Dr.オフィスここまでで、VLOOKUPの仕組みと数式の入力方法がわかったね。
次はいよいよVLOOKUPを設定するよ。
VLOOKUP関数を設定しよう！エクセルプルダウンで自動入力
それでは、実際にVLOOKUP関数を設定してみましょう。
冒頭で解説した『商品リスト』と『マスタ表』をそのまま使い、「A-001」の価格を自動入力できるようにします。
STEP
セルを選択して設定画面を開く
『商品リスト』を表示しましょう。
「型番」の「A-001」が選ばれている状態で、隣りの「価格」セルを選択してVLOOKUP関数の設定画面を開きます。
- セルE2を選択する
- 「関数の挿入」ボタンをクリックする
STEP
「関数の挿入」画面で「VLOOKUP」を選択する
- 「関数の挿入」が開いたら、「関数名」で「VLOOKUP」を選択する
- 「OK」ボタンを押す
STEP
「検索値」を指定する
- 「関数の引数」が開くと「検索値」が入力できる状態になっているので、セルD2を選択する
- 「検索値」に自動的にD2と入力される
STEP
「範囲」を指定する
- 「範囲」をクリックし、シートを『マスタ表』に切り替えてセルB2からD8を選択する
- 「範囲」に『マスタ表!B2:D8』と自動的に入力される
STEP
「範囲」の『B2:D8』を選択する
「範囲」の『B2:D8』の部分を選択しましょう。
STEP
「列番号」と「検索方法」を入力する
- 「列番号」に「3」と入力
- 「検索方法」に「FALSE」と入力する
- 「OK」ボタンを押す
STEP
価格が自動で入力された
「A-001」の価格が自動入力されました。
VLOOKUP関数の数式は、以下のようにセルを選択して数式バーに直接入力することも可能です。
慣れてくると、ちょっとした修正もしやすくなるので試してみてください。
さらに詳しいVLOOKUPの使い方については、VLOOKUP関数で請求書を作成しようで解説していますので参考にしてください。
VLOOKUPの「#N/A」はエクセルのIFERRORを使って対策しよう
VLOOKUP関数で条件が合う値が見つからない場合に表示されるのが「#N/A」です。
ここでは、IFERROR関数を組み合わせてこのエラーを見やすく整える方法を紹介します。
空欄でも安心！エクセルのIFERRORで「#N/A」を非表示にする方法
VLOOKUP関数が設定されているセルでは、参照元が空欄でも検索が行われるため、このようにD2が空欄の状態だと「#N/A」が表示されてしまいます。
こうした不要な「#N/A」はIFERRORを使って非表示にすることが可能です。
設定方法を見てみましょう。
STEP
セルを選択してIFERRORの数式を追加する
VLOOKUP関数が設定されているセルを選択し、IFERRORの数式を追加します。
- セルE2を選択
- 数式バーで、『=IFERROR(VLOOKUP(D2,$H$2:$I$8,2,FALSE),"")』のようにVLOOKUPの数式の前後をIFERRORで囲んでEnterを押す
STEP
「#N/A」が非表示になった
エラーが表示されなくなりました。
Dr.オフィス数式の最後の「""」は空文字と呼ばれ、何も表示しないという意味だよ。
この「""」に好きな文字を入れることもできるんだ。
好きな文字に変換！IFERRORで「#N/A」を任意の表示に変える方法
前の見出しではIFERRORの「""」を使ってエラーを非表示にしました。
次は、この「""」に好きな文字を入れて「#N/A」の代わりにメッセージを表示しましょう。
STEP
「""」に「未入力」と入れる
前の見出しで使った「#N/A」を非表示にする数式を引き続き使います。
- セルE2を選択
- 数式最後の「""」の中に「未入力」と入れてEnterを押す
STEP
「未入力」と表示できた
「未入力」というメッセージを表示させることができました。
用途によって「未設定」、「入力不要」、「該当なし」などを表示させると便利です。
IFERRORのより詳しい使い方については、IF関数で#N/Aや0を表示させない方法をご覧ください。
エクセルでVLOOKUPが反映されない！困ったときの対処法
VLOOKUP関数を使うときに起こりがちな不具合2例について、その対処法を紹介します。
- 数式をコピーしたら「#N/A」が出たときの対処法
- 検索値が見つからず「#N/A」が出たときの対処法
数式をコピーしたら「#N/A」が出たときの対処法
こちらの例は、VLOOKUP関数が設定されているセルE2の数式をE3にコピーしたところ、「#N/A」が表示されたケースです。
コピーをすると参照先が変わってしまうことがあるため、まずは数式を確認します。
順を追ってやってみましょう。
STEP
セルを選択して数式を確認する
セルE3を選択して数式を見てみると、検索範囲が絶対参照になっていないため、『H2:I8』から『H3:I9』にずれていることがわかります。
Dr.オフィスI-081は『H2:I2』にあるのでエラーになって当然だよ。
この場合は数式を直さないといけないね。
STEP
コピー元の数式を修正する
セルE3の数式を直すには、エラーの原因となっているコピー元の数式を絶対参照に修正します。
- セルE2を選択する
- 数式の『H2:I8』の部分を選択してF4を押す
- 絶対参照の『$H$2:$I$8』に変わったらEnterを押す
STEP
正しく自動入力できた
絶対参照に修正できたセルE2の数式を再度E3にコピーすると、今度は「I-081」の価格が正しく入力されました。
これで、数式のずれによるエラーは解消です。
検索値が見つからず「#N/A」が出たときの対処法
次はこちらのケースのように、数式は正しいのに「#N/A」が表示されてしまう不具合について解説します。
このエラーは、検索値と検索範囲の左端列の値が完全一致していないときに発生します。
そこでまず検索値のセルD4を確認したところ、問題はありませんでした。
次に左端列のセルH8を選択して数式バーを確認すると、このように先頭に半角スペースが入っていました。
わずかなスペースでもVLOOKUPは別の値と判断してしまうため、完全一致にならなくなります。
エラーの原因となっていた半角スペースを削除すれば、検索が正しく行われ、価格も問題なく入力されます。
Dr.オフィス完全一致にならない原因にはスペース以外にも次のような例があるから、「#N/A」が出るときはしっかり確認してみよう。
| 確認ポイント | 具体例 | 説明 |
|---|---|---|
| 漢字の違い | 「高田」と「髙田」 | 漢字が違っている |
| 半角と全角の違い | 「A-001」と「Ａ-001」 | 半角と全角が混ざっている |
| 記号の違い | 「-」と「–」 | 見た目は似ているが、ハイフンとダッシュは別の記号 |
プルダウンにVLOOKUPを連動させて自動入力する方法に関するQ＆A
連動プルダウンとVLOOKUPでエクセル入力をもっと安心に！
今回は、連動プルダウンにVLOOKUP関数を組み合わせて必要な情報を自動入力する方法と、IFERROR関数でエラーを整えて見やすく仕上げる方法を解説しました。
VLOOKUP関数は、左から右へ検索するという特性を押さえておけば、既存のリストの並びを変えずに使える場面が一気に広がり、実務でも扱いやすくなります。
最後に、エクセルのプルダウン連動で自動入力する方法をおさらいしましょう。
おさらい
- VLOOKUP関数の設定画面で検索値を入力
- 検索範囲を選択する
- 取得したい値のある列番号を指定
- 検索方法に「FALSE」または「0」を入力する
選択肢を絞り込める連動プルダウンと、大量のデータから自動入力できるVLOOKUP関数を合わせて活用すれば、それぞれの得意分野を活かしたデータ管理が可能になります。
是非実務に取り入れ、その便利さを実感してみてください！
プルダウンリストの作成方法や便利な応用テクニックについては、こちらで詳しく解説していますので是非ご覧ください。