【Excel】別のセルの値に基づいてセルを自動入力する方法|VLOOKUP・INDEX-MATCH・HLOOKUP活用ガイド

入力した値に応じて、他のセルが自動的に埋まってくれたら便利だと思いませんか?本記事では、別のセルに入力された値をもとに、Excelのセルを自動的に入力(オートポピュレート)する方法をご紹介します。ここではExcel 2019を使用していますが、お使いのバージョンでも同様の手順で実行できます。

まず最初に、今回の解説でベースとなるサンプルデータセットを確認しておきましょう。

この表には、従業員の氏名、ID、住所、所属部署、入社日などの情報がまとめられています。このデータを使って、セルを自動的に入力する方法を見ていきます。

なお、これはダミーデータによる基本的なデータセットです。実際の業務では、より大規模で複雑なデータを扱うことになるでしょう。

練習用ワークブック

以下のリンクから、練習用ワークブックをダウンロードできます。

別のセルに基づいてセルを自動入力する

ここでは、「従業員名を入力すると、その従業員の情報が自動的に表示される」という例を設定します。

元の表とは別に、情報表示用のフィールドを設けました。ここで「Name」欄にRobertと入力してみます。

すると、Robertの詳細情報が表示されるはずです。その具体的な方法を順番に見ていきましょう。

1. VLOOKUP関数を使う方法

少し立ち止まって考えてみてください。「自動入力」のことは一旦忘れて、条件に一致するデータを取り出す関数といえば何を思い浮かべますか?真っ先に挙がるのがVLOOKUPでしょう。

VLOOKUPは、縦方向に整理されたデータを検索するための関数です。詳細については、VLOOKUP関数の解説記事をご参照ください。

それでは、VLOOKUP関数を使って、目的のデータを取得する数式を作成していきます。

まずは従業員IDを取得するための数式です。

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,2,0),"")

VLOOKUP関数の中では、氏名(I4)をlookup_value(検索値)として指定し、表全体の範囲をlookup_array(検索範囲)としています。

「従業員ID」は表の2列目にあるため、column_num(列番号)には「2」を指定します。

さらに、IFERROR関数でVLOOKUPの数式全体を包んでいます。これにより、数式から発生するエラーを非表示にできます(IFERROR関数の詳細は解説記事をご覧ください)。

部署名を取得するには、数式を少し変更します。

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,3,0),"")

ここでは、元の表における項目の位置に合わせて列番号を変更しています。「部署」は3列目なので「3」を指定しました。

入社日と住所については、それぞれ次の数式になります。

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,4,0),"")

そして

=IFERROR(VLOOKUP($I$4,$B$4:$F$9,5,0),"")

これで従業員の詳細情報をすべて取得できました。名前を変更すれば、他のセルも自動的に更新されます。

VLOOKUPとドロップダウンリストの組み合わせ

先ほどは名前を手動で入力しましたが、手作業では時間がかかるうえ、入力ミスの原因にもなります。

そこで、従業員名を選択できるドロップダウンリストを作成しましょう。ドロップダウンリストの作成方法は、こちらの記事で詳しく解説しています。

「データの入力規則」ダイアログボックスで「リスト」を選択し、名前が入力されているセル範囲への参照を指定します。

B4:B9が名前が入力されている範囲です。

これでドロップダウンリストが使えるようになりました。

リストからなら、より素早く確実に名前を選択できます。

VLOOKUPを設定しているため、他のセルにも自動的に値が反映されます。

2. INDEX-MATCH関数を使う方法

VLOOKUPで行った処理は、別の方法でも実現可能です。INDEX-MATCHの組み合わせを使えば、同じようにセルを自動入力できます。

MATCH関数は、行・列・表の中で検索値がどの位置にあるかを特定します。INDEX関数は、指定した範囲内の特定の位置にある値を返します。詳しくは、INDEXおよびMATCHそれぞれの解説記事をご覧ください。

数式は次のようになります。

=IFERROR(INDEX($C$4:$C$9,MATCH($I$4,$B$4:$B$9,0)),"")

この数式では従業員IDを取得します。INDEX関数にIDの範囲(C4:C9)を指定し、MATCH関数が条件値に一致する行番号を表(B4:B9)から返す仕組みです。

部署を取得するには、INDEX関数の範囲を変更します。

=IFERROR(INDEX($D$4:$D$9,MATCH($I$4,$B$4:$B$9,0)),"")

部署はD4~D9の範囲にあります。

入社日の数式は次のとおりです。

=IFERROR(INDEX($E$4:$E$9,MATCH($I$4,$B$4:$B$9,0)),"")

住所の場合はこうなります。

=IFERROR(INDEX($F$4:$F$9,MATCH($I$4,$B$4:$B$9,0)),"")

動作を確認するために、一度選択を消去して、別の名前を選択してみましょう。

他のセルが自動的に入力されることがわかります。

3. HLOOKUP関数を使う方法

データが横方向に並んでいる場合は、HLOOKUP関数を使用します。関数の詳細については、HLOOKUPの解説記事をご覧ください。

「Name」フィールドはドロップダウンリストから設定し、残りのフィールドは自動的に入力されます。

IDを取得するには、次の数式を使用します。

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,2,0),"")

操作の流れはVLOOKUPの数式とほぼ同じです。HLOOKUP関数の中で、氏名をlookup_value(検索値)として、表全体をlookup_array(検索範囲)として指定しています。IDは2行目にあるためrow_num(行番号)は「2」、完全一致させるために最後に「0」を指定します。

部署の数式は次のとおりです。

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,3,0),"")

「部署」は3行目なので、行番号は「3」です。

入社日の数式はこうなります。

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,4,0),"")

「入社日」は4行目なので行番号は「4」。住所の場合は、行番号を「5」に変更してください。

=IFERROR(HLOOKUP($C$11,$C$3:$H$7,5,0),"")

セルの内容を消去して、ドロップダウンリストから名前を選択してみましょう。

名前を選択すると、他のセルが自動的に入力されます。

4. 行方向に対応させたINDEX-MATCH

INDEX-MATCHの組み合わせは、横方向(行単位)のデータにも使用できます。数式は次のとおりです。

=IFERROR(INDEX($C$4:$H$4,MATCH($C$11,$C$3:$H$3,0)),"")

これはIDを取得するための数式で、INDEX関数には「従業員ID」の行であるC4:H4を指定しています。

部署を求めるには、行の範囲を変更します。

=IFERROR(INDEX($C$5:$H$5,MATCH($C$11,$C$3:$H$3,0)),"")

同様に、入社日と住所についても行の範囲を変更します。

=IFERROR(INDEX($C$6:$H$6,MATCH($C$11,$C$3:$H$3,0)),"")

ここでC6:H6は「入社日」の行です。

そしてC7:H7は「住所」の行なので、住所を取得する数式は次のようになります。

=IFERROR(INDEX($C$7:$H$7,MATCH($C$11,$C$3:$H$3,0)),"")

まとめ

今回は、別のセルの値をもとにセルを自動入力するための複数の方法をご紹介しました。皆様のお役に立てば幸いです。わかりにくい点があれば、お気軽にコメントしてください。また、ここでご紹介できなかった他の方法があれば、ぜひお知らせください。

関連記事

• Excelでオートフィル数式を使う方法(6つのやり方)
• Excelで別のセルに基づいてセルをオートフィルする(5つの方法)
• Excelでの自動連番の付け方(9つのアプローチ)
• Excelで連続した数字をオートフィルする方法(12のやり方)
• 対処法:Excelのオートフィルが動作しないときの修正方法(7つの原因)
• Excelで複数シートにまたがって連続した日付を入力する方法