出席番号を入れたら、氏名と点数が出てくる表を作りたい。教員の仕事では、番号から、情報を引き出す場面があります。
ExcelのXLOOKUP(エックスルックアップ)関数は、検索値に合う行から、別の列の値を取り出します。この記事では、教員の仕事でよく出る例を使って、書き方を説明します。XLOOKUPが使えるのは、Excel 2021以降とMicrosoft 365です。
教員の仕事でXLOOKUP関数を使う場面|出席番号から氏名を出す
表の上のアルファベット(A、B、C…)が列、左の数字(1、2、3…)が行です。「E2」は、E列の2行目のセルのことです。
| A | B | C | D | E | F | |
|---|---|---|---|---|---|---|
| 1 | 出席番号 | 氏名 | 算数 | 入力する番号 | 氏名 | |
| 2 | 1 | 青木 | 72 | 2 | 伊藤 | |
| 3 | 2 | 伊藤 | 88 | |||
| 4 | 3 | 上田 | 60 |
F2に次の式を入れます。E2に番号を入れると、その番号の氏名が出ます。
=XLOOKUP(E2,A2:A4,B2:B4,"該当なし")
該当する番号がないときは、「該当なし」と出ます。
ここからは、XLOOKUP関数の書き方を順に見ていきます。
XLOOKUP関数の基本の書き方
XLOOKUP関数は、「検索範囲」から検索値を探し、「戻り範囲」の同じ位置にある値を返します。
- 検索値:探したい値(コードや名前など)
- 検索範囲:検索値を探す列(1列だけを指定します)
- 戻り範囲:結果として取り出したい列
- 見つからない場合:検索値がないときに表示する値(省略できます)

- 結果を表示したいセル(E2)を選び「=XLOOKUP(」と入力する
- 検索値(D2)を選び、カンマを入れる
- 検索範囲(A2:A5)をドラッグして選び、カンマを入れる
- 戻り範囲(B2:B5)をドラッグして選び、カンマを入れる
- 見つからないときの表示「”未登録”」を入力し、「)」で閉じて Enter を押す
見つからない場合の表示を指定する
VLOOKUPでは、探している値がないと「#N/A」が表示され、消すにはIFERRORを組み合わせる必要がありました。XLOOKUPは4つ目の引数で指定するだけです。
4つ目の引数を省略すると、VLOOKUPと同じように「#N/A」が表示されます。空白にしたいときは、「””」と入力します。
=XLOOKUP(D2,A2:A5,B2:B5,"")
検索値より左の列を取り出す
VLOOKUPは、検索値の列より右にある列しか取り出せません。XLOOKUPなら、検索範囲と戻り範囲を別々に指定するので、左側の列も取り出せます。

VLOOKUPとの違い
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 列の指定 | 列番号(数える必要あり) | 戻り範囲を直接選ぶ |
| 左の列の取得 | できない | できる |
| 検索方法の初期値 | 近似一致(FALSEの指定が必要) | 完全一致 |
| 見つからない場合 | #N/Aになる(IFERRORが必要) | 4つ目の引数で指定できる |
| 対応バージョン | すべて | Microsoft 365・Excel 2021以降 |
XLOOKUPが使えないとき
Excel 2019以前では、XLOOKUPは使えません。式を入力すると「#NAME?」と表示されます。ファイルを共有する相手のバージョンが古い可能性があるときは、VLOOKUPやINDEX+MATCHを使うほうが安全です。
もう一歩進める|XLOOKUPの便利な引数
XLOOKUP関数には、基本の3つの引数のほかに、省略できる引数がいくつかあります。知っておくと、VLOOKUPでは難しかった検索ができるようになります。
一致モードで検索の方法を変える
4つ目の引数「一致モード」を指定すると、完全一致以外の探し方ができます。
- 0:完全一致(省略したときの初期設定)
- -1:完全一致。なければ次に小さい値
- 1:完全一致。なければ次に大きい値
- 2:ワイルドカード(*や?)を使った検索
成績の段階や料金表のように、「ちょうどの値がなければ、近い値で判定したい」ときに便利です。
検索の向きを変える
5つ目の引数「検索モード」では、検索の順番を変えられます。「-1」を指定すると、下から上に向かって探すため、同じ値が複数あるときに「最後に出てくるもの」を取り出せます。
複数の列を一度に返す
戻り値の範囲を2列以上にすると、結果が隣のセルにもあふれて表示されます(スピル)。たとえば、商品コードから商品名と単価の2つを、1つの式で同時に取り出せます。
スピルを使うには、結果があふれる先のセルが空いている必要があります。セルが埋まっていると#SPILL!エラーになるので、先に空けておきましょう。
横に並んだ表や複数条件での使い方
XLOOKUPは、縦に並んだ表だけでなく、横に並んだ表でも使えます。VLOOKUPの横版にあたるHLOOKUPを別に覚えなくても、同じ書き方で検索できます。
=XLOOKUP(A2,B1:G1,B2:G2)
1行目の見出しから探して、2行目の値を返す式です。
また、「商品名と色の2つが一致する行」のような複数条件の検索は、検索値と検索範囲を「&」でつなぐ方法で実現できます。たとえば検索値をA2&B2とし、検索範囲を商品列&色列とします。
大量のデータを検索するときの工夫
データが数万行あるときは、検索の範囲を必要な行だけに絞ると、計算が速くなります。列全体(A:A)を指定する方法は手軽ですが、行数が多いと再計算に時間がかかることがあります。
- 範囲は、データがある行までを指定する(A2:A5000など)
- 表を「テーブル」にして、列の名前で範囲を指定すると、データが増えても範囲が自動で広がる
- 検索する列と、取り出す列は、同じ行数にそろえる
XLOOKUP関数を使うときの注意
- 古いExcelでは使えません。学校のパソコンのバージョンを、先に確認します。
- 古いExcelでは、VLOOKUPや、INDEXとMATCHを使います。


コメント