Excelで、ある範囲に入力された番号を検索し、その番号と同じ行にある名前を別のセルへ自動表示したい場合は、検索関数を使うと簡単に実現できます。例えばC4:C20に1、2、3などの番号があり、E4:E20に氏名が入力されている表なら、番号を条件として対応するE列の名前を取得できます。
この処理はVBAを使わなくても、XLOOKUP関数やINDEX関数とMATCH関数などで作成できます。ただし、AN11、AN15、AN19のように複数のセルへ1番、2番、3番に対応する名前を表示したい場合は、検索する番号をどのように指定するかがポイントです。
C列の番号からE列の名前を取得する基本的な考え方
例えば、C4:C20が順位や番号、E4:E20が名前になっているとします。C18に「1」、C10に「2」、C6に「3」が入力されている場合、求めたい結果は「1がある行のE列」「2がある行のE列」「3がある行のE列」の値です。
| C列 | E列 | 取得したい場所 |
|---|---|---|
| 1 | C18と同じ行の名前 | AN11 |
| 2 | C10と同じ行の名前 | AN15 |
| 3 | C6と同じ行の名前 | AN19 |
つまり、AN11では「C4:C20から1を探して、その行のE列を返す」という数式を作ればよいことになります。同じ考え方で2と3についても検索できます。
XLOOKUP関数が使えるExcelなら簡単に検索できる
Microsoft 365や比較的新しいExcelでXLOOKUP関数を利用できる場合は、AN11に次の数式を入力できます。
=XLOOKUP(1,$C$4:$C$20,$E$4:$E$20,\"\")
この式ではC4:C20から数値の「1」を探し、見つかった位置と同じ行にあるE4:E20の値を返します。例えばC18が1でE18が「山田」という名前なら、AN11には「山田」と表示されます。
最後の\"\"は1が見つからなかった場合に空白を表示するための指定です。これにより、まだ番号が入力されていない状態で不要なエラーを表示させずに済みます。
AN15には2、AN19には3の名前を表示できる
AN15に番号2の人を表示する場合は、検索値を2へ変更します。
=XLOOKUP(2,$C$4:$C$20,$E$4:$E$20,\"\")
AN19で番号3の人を表示するなら次のようになります。
=XLOOKUP(3,$C$4:$C$20,$E$4:$E$20,\"\")
この方法なら、C列の番号を後から変更しても結果が自動更新されます。例えば「1」がC18からC7へ移り、E7に別の名前が入っていれば、AN11には新しくC7と同じ行にあるE7の名前が表示されます。
単純なコピペだけでは検索する番号は変わらない
注意したいのは、AN11の式をそのままAN15やAN19へコピーしただけでは、検索値として直接入力した「1」は自動的に2、3へ変化しないことです。数式内に直接書かれた数値は、セルをコピーしても基本的にそのまま残ります。
そのため、AN11、AN15、AN19へ個別に式を設定するのであれば、それぞれ検索値を1、2、3に変更する方法が分かりやすいでしょう。
一方、今後4、5、6……と多数の番号を扱うのであれば、検索する番号を別のセルに入力して、そのセルをXLOOKUPの検索値として参照する設計の方が管理しやすくなります。
古いExcelならINDEX関数とMATCH関数でも対応できる
XLOOKUP関数が使用できないExcelでは、INDEX関数とMATCH関数を組み合わせる方法があります。番号1に対応する名前を取得する基本形は次のとおりです。
=INDEX($E$4:$E$20,MATCH(1,$C$4:$C$20,0))
MATCH関数がC4:C20の中から「1」と完全一致する位置を調べ、INDEX関数がその位置に対応するE4:E20の名前を返します。MATCHの第3引数を0にすることで完全一致検索になります。
番号がまだ存在しないときにエラーを表示させたくない場合は、IFERROR関数と組み合わせて次のようにできます。
=IFERROR(INDEX($E$4:$E$20,MATCH(1,$C$4:$C$20,0)),\"\")
検索番号をセルに入力する方式にすると管理しやすい
番号が増える可能性がある表では、数式の中へ「1」「2」「3」を直接記述するより、検索番号専用のセルを用意する方法も便利です。例えばAM11に1、AM15に2、AM19に3を入力しておきます。
AN11には次のように入力できます。
=XLOOKUP(AM11,$C$4:$C$20,$E$4:$E$20,\"\")
この方式なら数式そのものを変更せず、AM列に入力する番号を変えるだけで検索対象を変更できます。帳票のレイアウトによってはAM列を非表示にして、AN列だけを印刷・表示するといった運用も可能です。
範囲には絶対参照を使うとコピー時のずれを防げる
数式中の$C$4:$C$20や$E$4:$E$20についている「$」は絶対参照です。これを設定しておけば、数式を別の場所へコピーしても検索範囲が勝手にC8:C24などへずれてしまうことを防げます。
特に同じ検索表を使って複数の結果を表示する場合は、元データの範囲を絶対参照にしておくとミスが少なくなります。
なお、C列の値が数値の1ではなく文字列として保存された「1」になっているなど、データ形式が混在していると期待どおり検索できない場合があります。結果がおかしいときは、C4:C20のデータ形式も確認するとよいでしょう。
値が重複する場合は別の方法が必要
XLOOKUPは通常、条件に一致した最初の値を返します。そのため、今回のように1、2、3という番号がそれぞれ一度しか登場しない表とは相性がよい方法です。
一方、「1」が複数行にあり、その全員の名前を一覧表示したい場合には、XLOOKUPだけでは目的が変わってきます。Microsoft 365などFILTER関数を利用できる環境なら、条件に一致する複数の名前を抽出する方法を検討できます。
したがって、番号が重複しないことが保証されている表ではXLOOKUPまたはINDEX＋MATCH、重複するデータをすべて抽出したい表ではFILTERなど、目的に応じて関数を使い分けるのがポイントです。
まとめ｜C列の番号と同じ行の名前は検索関数で自動表示できる
C4:C20に番号、E4:E20に名前が入っている表なら、XLOOKUP関数を使うことで「1が入力された行の名前」をAN11などへ自動表示できます。番号1なら=XLOOKUP(1,$C$4:$C$20,$E$4:$E$20,\"\")が基本形です。
番号2、3についても検索値を変更すれば同じ仕組みで取得できます。古いExcelでXLOOKUPが使えない場合には、INDEX関数とMATCH関数の組み合わせでも同様の処理が可能です。
複数箇所へ展開するときは「検索範囲を絶対参照にする」「検索番号を直接指定するか別セルから参照する」という2点を意識すると、後から修正しやすいExcel表になります。
コメント