XLOOKUPの使い方｜初心者にもわかりやすく基本から実例まで解説
Excelでデータを探したり、別の表から情報を取り出したりするときに便利なのがXLOOKUP関数です。
たとえば、次のような作業を自動化できます。
- 商品コードを入力すると商品名や単価を表示する
- 社員番号から氏名や部署を表示する
- 顧客IDから住所や電話番号を取り出す
- 点数に応じて評価を表示する
- 縦方向だけでなく横方向の表からも検索する
以前は、こうした検索にはVLOOKUP関数がよく使われていました。
しかし、現在のExcelでは、より使いやすく高機能なXLOOKUP関数を使える環境が増えています。
この記事では、XLOOKUPの基本的な使い方、VLOOKUPとの違い、実務で使える具体例、よくあるエラーの対処法まで、初心者にもわかりやすく解説します。
XLOOKUPとは
XLOOKUPとは、Excelで指定した値を検索し、対応するデータを取り出す関数です。
たとえば、次のような商品一覧があるとします。
| 商品コード | 商品名 | 単価 |
|---|---|---|
| A001 | ノート | 120 |
| A002 | ペン | 80 |
| A003 | ファイル | 250 |
この表から、商品コード「A002」に対応する商品名「ペン」や単価「80」を取り出すことができます。
XLOOKUPは、従来のVLOOKUPよりも柔軟に使える検索関数です。
XLOOKUPでできること
XLOOKUPを使うと、次のようなことができます。
| やりたいこと | 例 |
|---|---|
| コードから名称を表示する | 商品コードから商品名を表示 |
| IDから情報を取得する | 社員番号から氏名を表示 |
| 別表から単価を取り出す | 商品マスタから単価を取得 |
| 該当なしの場合の表示を指定する | 見つからない場合に「該当なし」と表示 |
| 左側の列からも検索する | VLOOKUPでは難しい左方向検索が可能 |
| 横方向の表から検索する | HLOOKUPのような使い方も可能 |
| 最後から検索する | 最新データや直近データを探せる |
特に便利なのは、検索する列と取り出す列を別々に指定できる点です。
XLOOKUPの基本構文
XLOOKUP関数の基本形は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)
引数が多く見えますが、初心者の方はまず以下の3つだけ覚えれば大丈夫です。
=XLOOKUP(検索値, 検索範囲, 戻り範囲)
- 基本の3要素
- 1. 検索値
- 2. 検索範囲
- 3. 戻り範囲
- 4. 見つからない場合
- 5. 一致モード
- 6. 検索モード
- 「該当なし」と表示する
- 空白にする
- 確認メッセージを表示する
- 1. 商品コードから商品名を表示する
- 2. 商品コードから単価を表示する
- 3. 社員番号から氏名を表示する
- 4. 社員番号から部署名を表示する
- 5. 顧客IDから住所を表示する
- 1. #N/Aエラーが出る
- 主な原因
- 対処法
- 2. #VALUE!エラーが出る
- 悪い例
- 対処法
- 3. 思った値が返らない
- 主な原因
- 対処法
- 1. 古いExcelでは使えない場合がある
- 2. 検索範囲と戻り範囲のサイズをそろえる
- 3. 検索キーの重複に注意する
- 4. データ形式をそろえる
- 1. マスタ表を整える
- 2. 範囲はわかりやすく指定する
- 3. 見つからない場合の表示を入れる
- 4. テーブル機能と組み合わせる
- 商品名を表示する
- 単価を表示する
- 見つからない場合は空白にする
- 左方向に検索する
- 横方向に検索する
- 最後から検索する
基本の3要素
| 引数 | 意味 | 例 |
|---|---|---|
| 検索値 | 探したい値 | 商品コード、社員番号など |
| 検索範囲 | 検索値を探す範囲 | 商品コードの列 |
| 戻り範囲 | 取り出したい値がある範囲 | 商品名の列、単価の列 |
つまり、XLOOKUPは次のように考えるとわかりやすいです。
この値を探して → この列から検索して → 対応する値をこの列から返す
XLOOKUPの基本例
次のような商品マスタがあるとします。
| A列 | B列 | C列 |
|---|---|---|
| 商品コード | 商品名 | 単価 |
| A001 | ノート | 120 |
| A002 | ペン | 80 |
| A003 | ファイル | 250 |
| A004 | 消しゴム | 60 |
別の表で、E2セルに商品コードを入力し、F2セルに商品名を表示したい場合は、F2セルに次の数式を入力します。
=XLOOKUP(E2,A2:A5,B2:B5)
この数式の意味は次のとおりです。
| 部分 | 意味 |
|---|---|
| E2 | 探したい商品コード |
| A2:A5 | 商品コードを探す範囲 |
| B2:B5 | 見つかった行から返す商品名の範囲 |
E2セルに「A002」と入力すると、F2セルには「ペン」と表示されます。
XLOOKUPで単価を表示する方法
商品コードから単価を表示したい場合は、戻り範囲をC列に変更します。
=XLOOKUP(E2,A2:A5,C2:C5)
この数式では、E2の商品コードをA2:A5から探し、対応する単価をC2:C5から返します。
たとえば、E2に「A003」と入力すると、「250」が表示されます。
XLOOKUPの引数を詳しく解説
1. 検索値
検索値とは、探したい値のことです。
例:
=XLOOKUP(E2,A2:A5,B2:B5)
この場合、検索値はE2です。
検索値には、次のようなものを指定できます。
- 商品コード
- 社員番号
- 顧客ID
- 管理番号
- 部署コード
- 郵便番号
2. 検索範囲
検索範囲とは、検索値を探す場所です。
例:
=XLOOKUP(E2,A2:A5,B2:B5)
この場合、検索範囲はA2:A5です。
VLOOKUPでは検索列が表の一番左にある必要がありましたが、XLOOKUPではその制限がありません。
3. 戻り範囲
戻り範囲とは、検索値が見つかったときに返したい値が入っている範囲です。
例:
=XLOOKUP(E2,A2:A5,B2:B5)
この場合、戻り範囲はB2:B5です。
商品名を返したいなら商品名の列、単価を返したいなら単価の列を指定します。
4. 見つからない場合
XLOOKUPでは、検索値が見つからない場合に表示する内容を指定できます。
例:
=XLOOKUP(E2,A2:A5,B2:B5,"該当なし")
この数式では、E2の商品コードが見つからない場合、「該当なし」と表示されます。
VLOOKUPではIFERROR関数を組み合わせることが多いですが、XLOOKUPでは関数内で指定できます。
5. 一致モード
一致モードでは、完全一致や近似一致などの検索方法を指定できます。
| 指定値 | 意味 |
|---|---|
| 0 | 完全一致。見つからない場合はエラー |
| -1 | 完全一致。なければ次に小さい値 |
| 1 | 完全一致。なければ次に大きい値 |
| 2 | ワイルドカード一致 |
通常は省略して問題ありません。
XLOOKUPは、省略すると完全一致で検索します。
これは、VLOOKUPよりも初心者にやさしいポイントです。
6. 検索モード
検索モードでは、検索する方向を指定できます。
| 指定値 | 意味 |
|---|---|
| 1 | 先頭から検索 |
| -1 | 末尾から検索 |
| 2 | 昇順データを高速検索 |
| -2 | 降順データを高速検索 |
通常は省略して問題ありません。
XLOOKUPとVLOOKUPの違い
XLOOKUPは、VLOOKUPの弱点を補う便利な関数です。
| 比較項目 | VLOOKUP | XLOOKUP |
|---|---|---|
| 検索方向 | 基本的に右方向のみ | 左右どちらも可能 |
| 列の指定方法 | 何列目かを番号で指定 | 戻り範囲を直接指定 |
| 完全一致 | FALSEを指定する必要がある | 省略で完全一致 |
| エラー時の表示 | IFERRORと組み合わせる | 関数内で指定可能 |
| 横方向検索 | HLOOKUPが必要 | XLOOKUPで対応可能 |
| 列追加への強さ | 列番号がずれることがある | 戻り範囲指定なのでずれにくい |
| 使いやすさ | 慣れが必要 | 直感的に使いやすい |
特に大きな違いは、XLOOKUPは左側の列も検索できることです。
VLOOKUPでは難しい「左方向検索」もできる
VLOOKUPでは、検索列より左側にある値を取り出すのが苦手です。
たとえば、次のような表があるとします。
| 商品名 | 商品コード |
|---|---|
| ノート | A001 |
| ペン | A002 |
| ファイル | A003 |
商品コードから商品名を取り出したい場合、商品コードは右側、商品名は左側にあります。
VLOOKUPでは扱いにくい形ですが、XLOOKUPなら簡単です。
=XLOOKUP(E2,B2:B4,A2:A4)
この数式では、E2の商品コードをB列から探し、対応する商品名をA列から返します。
別シートの表を参照するXLOOKUP
実務では、商品マスタや社員マスタを別シートに置くことがよくあります。
たとえば、「商品マスタ」シートに次のような表があるとします。
| A列 | B列 | C列 |
|---|---|---|
| 商品コード | 商品名 | 単価 |
| A001 | ノート | 120 |
| A002 | ペン | 80 |
| A003 | ファイル | 250 |
入力用シートで、A2セルの商品コードから商品名を表示する場合は、次のように入力します。
=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!B:B,"該当なし")
単価を表示する場合は次のようにします。
=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!C:C,"該当なし")
ただし、データ量が多い場合は、列全体を指定するよりも必要な範囲に絞った方が動作が軽くなることがあります。
例:
=XLOOKUP(A2,商品マスタ!$A$2:$A$100,商品マスタ!$B$2:$B$100,"該当なし")
XLOOKUPで複数列をまとめて表示する方法
XLOOKUPでは、戻り範囲を複数列にすると、複数の値を一度に返すことができます。
たとえば、商品コードから商品名と単価を同時に表示したい場合です。
| A列 | B列 | C列 |
|---|---|---|
| 商品コード | 商品名 | 単価 |
| A001 | ノート | 120 |
| A002 | ペン | 80 |
| A003 | ファイル | 250 |
E2セルに商品コードを入力し、F2セルから商品名と単価を表示したい場合は、F2セルに次の数式を入力します。
=XLOOKUP(E2,A2:A4,B2:C4,"該当なし")
この場合、F2セルに商品名、G2セルに単価が表示されます。
これは、VLOOKUPよりも便利な使い方のひとつです。
XLOOKUPで横方向の表を検索する方法
XLOOKUPは、縦方向だけでなく横方向の検索にも使えます。
たとえば、次のような月別売上表があるとします。
| B列 | C列 | D列 | |
|---|---|---|---|
| 1行目 | 4月 | 5月 | 6月 |
| 2行目 | 100000 | 120000 | 90000 |
F1セルに月を入力し、F2セルに売上を表示したい場合は、次の数式を使います。
=XLOOKUP(F1,B1:D1,B2:D2,"該当なし")
F1に「5月」と入力すると、F2には「120000」と表示されます。
従来はHLOOKUP関数を使う場面でしたが、XLOOKUPなら同じ関数で対応できます。
XLOOKUPで「見つからない場合」を表示する
検索値が見つからない場合、何も設定していないとエラーが表示されます。
そこで、4つ目の引数に表示したい文字を指定します。
「該当なし」と表示する
=XLOOKUP(E2,A2:A5,B2:B5,"該当なし")
空白にする
=XLOOKUP(E2,A2:A5,B2:B5,"")
確認メッセージを表示する
=XLOOKUP(E2,A2:A5,B2:B5,"コードを確認してください")
実務では、未入力のセルがある場合も考えて、空白にする設定がよく使われます。
XLOOKUPでワイルドカード検索をする方法
XLOOKUPでは、ワイルドカードを使った検索もできます。
ワイルドカードとは、あいまい検索に使う記号です。
| 記号 | 意味 |
|---|---|
* |
任意の文字列 |
? |
任意の1文字 |
たとえば、商品名に「ペン」を含むデータを探したい場合は、次のようにします。
=XLOOKUP("*ペン*",B2:B10,C2:C10,"該当なし",2)
この数式では、B2:B10の中から「ペン」を含む商品名を探し、対応する値をC2:C10から返します。
最後の2は、ワイルドカード一致を指定するためのものです。
XLOOKUPで近似一致を使う方法
XLOOKUPでは、点数や金額に応じてランクを判定することもできます。
たとえば、次のような評価表があるとします。
| 点数 | 評価 |
|---|---|
| 0 | D |
| 60 | C |
| 70 | B |
| 80 | A |
| 90 | S |
E2セルに点数が入っている場合、評価を表示するには次のようにします。
=XLOOKUP(E2,A2:A6,B2:B6,"該当なし",-1)
この数式では、完全一致がない場合、検索値以下で最も近い値を探します。
たとえば、E2が85なら、80以上90未満なので「A」と表示されます。
XLOOKUPで最後の一致データを取得する
XLOOKUPでは、検索モードを指定することで、後ろから検索できます。
たとえば、同じ商品コードが複数回登場する売上履歴から、最新のデータを取得したい場合に便利です。
=XLOOKUP(E2,A2:A100,C2:C100,"該当なし",0,-1)
この数式では、A2:A100を下から検索し、最後に見つかったデータに対応するC列の値を返します。
| 引数 | 指定内容 |
|---|---|
| E2 | 検索値 |
| A2:A100 | 検索範囲 |
| C2:C100 | 戻り範囲 |
| “該当なし” | 見つからない場合 |
| 0 | 完全一致 |
| -1 | 末尾から検索 |
XLOOKUPの実務で使える例
1. 商品コードから商品名を表示する
=XLOOKUP(A2,商品マスタ!$A$2:$A$100,商品マスタ!$B$2:$B$100,"該当なし")
商品コードを入力するだけで、商品名を自動表示できます。
2. 商品コードから単価を表示する
=XLOOKUP(A2,商品マスタ!$A$2:$A$100,商品マスタ!$C$2:$C$100,"該当なし")
請求書や売上入力表でよく使うパターンです。
3. 社員番号から氏名を表示する
=XLOOKUP(A2,社員マスタ!$A$2:$A$200,社員マスタ!$B$2:$B$200,"該当なし")
社員番号をもとに、氏名を自動表示できます。
4. 社員番号から部署名を表示する
=XLOOKUP(A2,社員マスタ!$A$2:$A$200,社員マスタ!$C$2:$C$200,"該当なし")
勤怠表、研修管理表、名簿などで便利です。
5. 顧客IDから住所を表示する
=XLOOKUP(A2,顧客マスタ!$A$2:$A$500,顧客マスタ!$D$2:$D$500,"該当なし")
請求書、発送リスト、問い合わせ管理などに活用できます。
XLOOKUPでよくあるエラーと対処法
1. #N/Aエラーが出る
#N/Aは、検索値が見つからないときに表示されます。
主な原因
- 検索値がマスタ表に存在しない
- 入力ミスがある
- 余分なスペースが入っている
- 全角と半角が混在している
- 数値と文字列が一致していない
- 検索範囲を間違えている
対処法
- 検索値が本当に存在するか確認する
- 余分なスペースを削除する
- 全角・半角を統一する
- 数値と文字列の形式をそろえる
- 「見つからない場合」の引数を指定する
例:
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"該当なし")
2. #VALUE!エラーが出る
#VALUE!は、指定した範囲のサイズが合っていない場合などに表示されることがあります。
悪い例
=XLOOKUP(A2,E2:E100,F2:G50)
検索範囲はE2:E100で99行ありますが、戻り範囲はF2:G50で49行しかありません。
対処法
検索範囲と戻り範囲の行数、または列数をそろえましょう。
良い例:
=XLOOKUP(A2,E2:E100,F2:F100)
3. 思った値が返らない
エラーは出ていないのに、期待した値と違う結果が返ることがあります。
主な原因
- 検索値が重複している
- 検索範囲が間違っている
- 戻り範囲がずれている
- 近似一致の指定が意図と違う
- データに余分な空白がある
対処法
- 検索キーに重複がないか確認する
- 検索範囲と戻り範囲を見直す
- 完全一致で検索したい場合は一致モードを
0にする - データの表記ゆれを整理する
XLOOKUPを使うときの注意点
1. 古いExcelでは使えない場合がある
XLOOKUPは比較的新しい関数です。
そのため、古いバージョンのExcelでは使えない場合があります。
共有ファイルを作成する場合は、相手のExcel環境でもXLOOKUPが使えるか確認しておくと安心です。
2. 検索範囲と戻り範囲のサイズをそろえる
XLOOKUPでは、検索範囲と戻り範囲のサイズをそろえる必要があります。
良い例:
=XLOOKUP(A2,E2:E100,F2:F100)
悪い例:
=XLOOKUP(A2,E2:E100,F2:F50)
範囲の大きさが違うと、エラーや意図しない結果の原因になります。
3. 検索キーの重複に注意する
XLOOKUPは、通常、最初に見つかった一致データを返します。
同じ商品コードや社員番号が複数あると、意図しない値が表示されることがあります。
検索に使う列は、できるだけ重複しないように管理しましょう。
4. データ形式をそろえる
見た目が同じでも、Excel内部では違うデータとして扱われることがあります。
例:
| 見た目 | 実際の扱い |
|---|---|
| 001 | 文字列 |
| 1 | 数値 |
| Ａ００１ | 全角文字 |
| A001 | 半角文字 |
| A001 | 後ろにスペースが入っている場合あり |
XLOOKUPがうまく動かないときは、データ形式や余分な空白を確認しましょう。
XLOOKUPを使いやすくするコツ
1. マスタ表を整える
XLOOKUPを安定して使うには、参照元のマスタ表を整えることが大切です。
良いマスタ表の条件は次のとおりです。
- 見出しがある
- 検索キーが統一されている
- 重複がない
- 空白行がない
- データ形式がそろっている
- 不要な結合セルがない
2. 範囲はわかりやすく指定する
列全体を指定すると簡単ですが、データ量が多いファイルでは動作が重くなることがあります。
例:
=XLOOKUP(A2,商品マスタ!A:A,商品マスタ!B:B,"該当なし")
データ量が多い場合は、必要な範囲に絞るのがおすすめです。
=XLOOKUP(A2,商品マスタ!$A$2:$A$1000,商品マスタ!$B$2:$B$1000,"該当なし")
3. 見つからない場合の表示を入れる
実務では、検索値が未入力だったり、マスタに存在しないコードが入力されたりすることがあります。
そのため、「見つからない場合」の表示を設定しておくと親切です。
例:
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"")
または、
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"該当なし")
4. テーブル機能と組み合わせる
Excelのテーブル機能を使うと、データ範囲が増えても数式が管理しやすくなります。
たとえば、商品マスタをテーブル化して「商品マスタ」という名前にした場合、次のように書けます。
=XLOOKUP(A2,商品マスタ[商品コード],商品マスタ[商品名],"該当なし")
この書き方は、列名で指定できるため、数式の意味がわかりやすくなります。
XLOOKUPがうまく動かないときのチェックリスト
XLOOKUPでエラーが出る、または正しい値が返らないときは、次の項目を確認しましょう。
| 確認項目 | 対処法 |
|---|---|
| 検索値は存在するか | マスタ表を確認する |
| 検索範囲は正しいか | 検索したい列を指定する |
| 戻り範囲は正しいか | 返したい列を指定する |
| 検索範囲と戻り範囲のサイズは同じか | 行数・列数をそろえる |
| 余分なスペースはないか | TRIM関数などで整理する |
| 全角・半角は統一されているか | 表記をそろえる |
| 数値と文字列が混在していないか | データ形式を統一する |
| 重複データはないか | 検索キーを確認する |
| 古いExcelで開いていないか | XLOOKUP対応環境か確認する |
XLOOKUPの基本パターンまとめ
商品名を表示する
=XLOOKUP(A2,商品マスタ!$A$2:$A$100,商品マスタ!$B$2:$B$100,"該当なし")
単価を表示する
=XLOOKUP(A2,商品マスタ!$A$2:$A$100,商品マスタ!$C$2:$C$100,"該当なし")
見つからない場合は空白にする
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"")
左方向に検索する
=XLOOKUP(A2,$C$2:$C$100,$A$2:$A$100,"該当なし")
横方向に検索する
=XLOOKUP(A1,$B$1:$M$1,$B$2:$M$2,"該当なし")
最後から検索する
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"該当なし",0,-1)
まとめ
XLOOKUPは、Excelでデータを検索して取り出すときに非常に便利な関数です。
基本形は次のとおりです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲)
さらに、見つからない場合の表示を入れると、実務で使いやすくなります。
=XLOOKUP(検索値, 検索範囲, 戻り範囲,"該当なし")
XLOOKUPの主なメリットは次のとおりです。
| メリット | 内容 |
|---|---|
| 直感的に使える | 検索範囲と戻り範囲を直接指定できる |
| 左方向検索ができる | VLOOKUPの弱点を補える |
| 横方向検索もできる | HLOOKUPの代わりにもなる |
| 完全一致が標準 | VLOOKUPよりミスが起きにくい |
| エラー時の表示を指定できる | IFERRORなしでも見やすくできる |
| 複数列を返せる | 商品名と単価などをまとめて表示できる |
VLOOKUPに慣れている方も、これからExcel関数を学ぶ方も、XLOOKUPを覚えることで作業効率が大きく上がります。
まずは、次の基本形から使ってみましょう。
=XLOOKUP(A2,$E$2:$E$100,$F$2:$F$100,"該当なし")
この形を覚えるだけでも、商品管理、顧客管理、社員名簿、売上表など、さまざまなExcel作業に活用できます。
コメント