XLOOKUP関数で表から必要な値を取り出す基本手順
Excelで商品ID、社員番号、顧客IDなどをもとに、別表から単価や部署名を取り出したい場面はよくあります。以前はVLOOKUPを使うことが多かったですが、現在のExcelではXLOOKUP関数を使うと、検索する列と取り出す列を分けて指定しやすくなります。
この記事では、初心者向けにXLOOKUPの基本形と、見つからない時の表示を整える方法を紹介します。
XLOOKUP関数の基本形
XLOOKUPは、指定した値を検索範囲から探し、同じ行にある別の列の値を返す関数です。基本形は次の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない時の表示)たとえば、商品IDをもとに単価を取り出すなら、検索値は商品ID、検索範囲は商品IDの列、戻り範囲は単価の列になります。
手順1：検索値を決める
最初に決めるのは「何をキーに探すか」です。商品ID、社員番号、メールアドレスのように、表の中で重複しにくい値を検索値にすると安定します。
商品名のように表記ゆれが起きやすい値よりも、IDや番号のように決まった形式の値を使う方が、検索ミスを減らしやすくなります。
手順2：検索範囲と戻り範囲を指定する
次に、検索する列と、取り出したい列を指定します。商品IDをA列から探し、単価をD列から取り出すなら、次のような式になります。
=XLOOKUP(F2, A2:A6, D2:D6)`F2` に探したい商品IDを入れると、A2:A6から同じ商品IDを探し、同じ行のD2:D6にある単価を返します。
手順3：見つからない時の表示を整える
検索値が表にない場合、何も設定していないとエラー表示になります。XLOOKUPでは第4引数を使って、見つからない時の表示を指定できます。
=XLOOKUP(F2, A2:A6, D2:D6, "未登録")このようにしておくと、商品IDがマスタにない場合に「未登録」と表示できます。エラーのままにするより、確認すべきデータが分かりやすくなります。
VLOOKUPより分かりやすいポイント
XLOOKUPは、検索範囲と戻り範囲を別々に指定できます。そのため、「検索する列はA列、返す列はD列」と見たまま指定しやすいのが利点です。
| 確認項目 | XLOOKUPで考えること |
|---|---|
| 何で探すか | 商品ID、社員番号、顧客IDなど |
| どこから探すか | 検索値が入っている列 |
| 何を返すか | 単価、部署、担当者名など |
| 見つからない時 | 「未登録」「要確認」などを表示 |
初心者がつまずきやすい注意点
- 検索範囲と戻り範囲の行数をそろえる
- 商品IDの前後に余分な空白が入っていないか確認する
- 数値としてのIDと文字列としてのIDが混ざっていないか確認する
- 同じIDが複数ある場合、最初に見つかった値が返る点に注意する
- 古いExcel環境ではXLOOKUPが使えない場合があるため、利用環境を確認する
まとめ
XLOOKUP関数は、IDや番号をもとに別表から必要な値を取り出すときに便利です。まず検索値を決め、検索範囲と戻り範囲を指定し、見つからない時の表示まで整えると、実務で使いやすい表になります。
商品マスタ、社員一覧、顧客一覧など、決まったIDで管理している表がある場合は、XLOOKUPから試してみるとよいでしょう。
