ExcelのXLOOKUP関数の使い方｜別表から商品名や単価を自動入力する方法
はじめに
商品コードから商品名を出したい、社員番号から部署名を出したい。
このような「別表から値を持ってくる」作業は、Excelでは非常によくあります。
そのときに便利なのが XLOOKUP関数 です。
この記事では、初めて使う人でもそのまま試せるように、一番よく使う形だけに絞って解説します。
この記事でわかること
- XLOOKUPの基本形
- 商品コードから商品名や単価を返す方法
- VLOOKUPより使いやすいポイント
対象となる人
- 別表から値を自動入力したい方
- VLOOKUPは見たことがあるが苦手意識がある方
- Excelの検索関数を実務で使いたい方
結論
XLOOKUPは、検索する列 と 返したい列 を分けて指定できるので、初心者でも意味を理解しやすい関数です。
まずは 検索値, 検索範囲, 戻り範囲, 見つからないときの表示 の4つだけ覚えれば十分です。
XLOOKUP関数の基本形
基本形は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, "見つからない時の表示")たとえば、A2に商品コードがあり、別シートのマスタから商品名を返したいなら、検索値はA2、検索範囲は商品コード列、戻り範囲は商品名列になります。
手順1 商品マスタをテーブルにする
マスタ表は、普通のセル範囲よりテーブル化しておく方が保守しやすいです。
理由は次の3つです。
- 列名で式が読める
- 行追加に強い
- 参照範囲のズレが起きにくい
たとえば、商品マスタに以下の列があるとします。
- 商品コード
- 商品名
- 単価
手順2 商品コードから商品名を返す
商品コードがA2にある前提なら、式は次のようになります。
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[商品名], "未登録")この式の意味はシンプルです。
A2を探す商品マスタ[商品コード]で探す- 一致した行の
商品マスタ[商品名]を返す - 見つからなければ
未登録と表示する
手順3 単価も同じ考え方で返す
単価を返したい場合は、戻り範囲だけ変えます。
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[単価], "")商品名と単価で検索値は同じなので、考え方は変わりません。
返したい列を差し替えるだけです。
手順4 社員番号や取引先コードにもそのまま使う
XLOOKUPは商品マスタ以外でも同じように使えます。
- 社員番号 → 部署名
- 取引先コード → 取引先名
- 店舗コード → 店舗名
実務では「コードを入力したら必要情報を引く」場面が多いので、覚えておくと再利用しやすい関数です。
XLOOKUPがうまくいかないときの確認ポイント
うまく返らないときは、次の順で確認してください。
- 検索値に余分な空白がないか
- 検索範囲と戻り範囲の行数が一致しているか
- コードが文字列と数値で混在していないか
- 参照している列名を間違えていないか
見つからないときの表示を入れておくと、空白より原因が把握しやすくなります。
コピペ用関数
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[商品名], "未登録")
=XLOOKUP(A2, 商品マスタ[商品コード], 商品マスタ[単価], "")
=XLOOKUP(A2, 社員マスタ[社員番号], 社員マスタ[部署名], "未登録")
つまずきポイント
- 検索範囲と戻り範囲を逆にしてしまう
- 商品コードの型が揃っていない
- 古いファイルの構造をそのまま使って列を間違える
注意点
- まずは4引数までで使うと混乱しにくいです。
- マスタ表はテーブル化しておくと管理しやすくなります。
- 既存ファイルにVLOOKUPが入っていても、今から新規で作るならXLOOKUPの方が理解しやすい場面が多いです。
まとめ
XLOOKUPは、別表参照を読みやすく書ける関数です。
まずは商品コード→商品名、社員番号→部署名のような基本パターンで慣れてください。
一度使えるようになると、日々の入力作業がかなり速くなります。
