エクセルで、プルダウン（ドロップダウンリスト）で選んだ値に合わせて、隣のセルに商品名や単価を自動で入れるには、プルダウンと VLOOKUP関数（または XLOOKUP関数）を組み合わせます。商品コード「A02」を選ぶと、みかん・80 が自動で表示されます。
この記事では、商品の一覧（マスタ）の作り方、プルダウンの設定、VLOOKUP関数・XLOOKUP関数での自動入力、まだ選んでいないときに空にする方法、一覧に無い値のときの表示までを、実際に Excel で確かめた表で説明します。プルダウンそのものの作り方・追加・解除は プルダウンの設定方法 で詳しく説明しています。下の練習ツールで、商品コードを書き換えて自動入力の変わり方を試せます。
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
自動入力のしくみ
| 場所 | 入れるもの | この表の例 |
|---|---|---|
| 一覧（マスタ） | 商品コード・商品名・単価の表 | E2:G6 |
| プルダウン | 一覧の商品コードから選べるようにする | A3:A6 |
| 自動入力の式 | 選んだコードで一覧を検索する式 | B列（VLOOKUP）・C列（XLOOKUP） |
実際の Excel で、B3 を選んだときの画面です。数式バーに VLOOKUP の式が入っていて、A3 の「A02」から商品名の みかん が自動で入りました。商品コードが空の5行目は何も表示されず、一覧に無い「B99」の6行目は「未登録」になっています。
プルダウンを作る
手順1・2の画面です。「データ」タブの「データツール」グループにある小さなアイコンが「データの入力規則」です。マウスを合わせると名前が出ます。キーボードなら Alt → A → V → V の順に押しても開きます。
手順3の画面です。「入力値の種類」を「リスト」にすると「元の値」の欄が出るので、商品の一覧の商品コード（E3:E6）を =$E$3:$E$6 と入れて「OK」を押します。
A3:A6 のセルを選ぶと右に ▼ が表示され、一覧の商品コード（A01〜A04）から選べるようになります。一覧に無い値を手で入力しようとすると、エラーのメッセージが出ます（データの入力規則の使い方）。
選んだら商品名を自動で入れる（VLOOKUP）
VLOOKUP関数で、A3 の商品コードを一覧の左の列から探し、2列目の商品名を返します。A3 が「A02」なら みかん、A4 が「A03」なら ぶどう です。一覧の範囲は $ を付けて固定すると、下にコピーしてもずれません（VLOOKUP関数の使い方）。
▶ この式（B3）を、上の練習ツールで試す ／ この式（B4）を、上の練習ツールで試す
単価を自動で入れる（XLOOKUP）
XLOOKUP関数は、探す列（E列）と返す列（G列）を別々に指定するので、列番号を数える必要がありません。A02 の単価は 80、A03 は 450 です。Microsoft 365・Excel 2021 以降で使えます。
▶ この式（C3）を、上の練習ツールで試す ／ この式（C4）を、上の練習ツールで試す
まだ選んでいないときは空にする
式の最初の IF(A3="","",…) は、商品コードが空のときに何も表示しないための部分です。A5 のようにまだ選んでいない行は、商品名も単価も空になります。これが無いと、空のセルを探して #N/A エラーや「未登録」が表示されてしまいます（IF関数の使い方）。
▶ この式（B5）を、上の練習ツールで試す ／ この式（C5）を、上の練習ツールで試す
一覧に無い値のときの表示
「B99」のように一覧に無いコードが入っていると、検索が失敗して #N/A エラーになります。IFERROR関数で 未登録 と表示するようにしておくと、どこが間違っているか分かりやすくなります（IFERROR関数の使い方）。プルダウンを付ける前に入っていた値や、貼り付けた値は、入力規則があっても残ることがあります。
▶ この式（B6）を、上の練習ツールで試す ／ この式（C6）を、上の練習ツールで試す
プルダウンで自動入力できないときの確認表
| 症状 | 考えられる原因 | 対処 |
|---|---|---|
| 「#N/A」になる | 一覧に無い値、または前後の空白・全角半角の違い | IFERROR関数で表示を決め、TRIM関数などで値をそろえる。 |
| 下の行で結果がずれる | 一覧の範囲に $ が付いていない | $E$3:$G$6 のように絶対参照にする。 |
| 商品を増やしても選べない | プルダウンの元の値・式の範囲が古い | 一覧をテーブルにするか、範囲を広げる。 |
| 数値のコードが見つからない | 一覧は数値、選んだ値は文字（またはその逆） | 一覧とプルダウンの値の種類をそろえる。 |
よくある質問
まだ選んでいない行に #N/A や「未登録」が出ます。
空のセルを検索しているためです。式の最初を =IF(A3=””,””,…) にすると、商品コードが空の行は何も表示されません。
一覧に無いコードが入っていたら？
検索が失敗して #N/A になります。IFERROR関数で「未登録」と表示するようにしておくと、どこが違っているか分かりやすくなります。
商品を一覧に追加したのに、プルダウンで選べません。
プルダウンの「元の値」と式の範囲が、追加前の範囲のままだからです。一覧をテーブルにするか、範囲を広げます（データの入力規則の使い方）。
あわせて読みたい：関連記事
練習用ファイル：練習用Excelファイル（.xlsx・無料）
コメント