XLOOKUP（エックスルックアップ）関数は、Excel 2019以降のバージョンで使用可能な検索関数です。従来のVLOOKUP関数やHLOOKUP関数に比べて、より柔軟で強力な機能を持っています。
この記事では、XLOOKUP関数の基本的な使い方から複数条件での使用方法、別シートからのデータ参照、エラー対策まで幅広く解説します。
XLOOKUP関数とは？
XLOOKUP関数の特徴
XLOOKUP関数とはExcel 2019以降のバージョンで使用できる検索関数で、データの行や列から値を簡単に取り出すことができます。これまで広く使われたVLOOKUP関数の欠点を補い、より柔軟で強力な検索機能を備えています。
ExcelにはVLOOKUP関数の他にも便利な検索関数が多くあり、検索関数を選択する際には、自分が "何を" "どのような形式で" 検索したいのかを明確にすることが重要になります。
よく使われる検索関数を一覧で確認したい方は以下の記事をご覧ください。
XLOOKUP関数とVLOOKUP関数との違い
VLOOKUP関数は列単位での検索しかできませんでしたが、XLOOKUP関数は行や列のどちらにも対応しています。
これにより、データ構造が複雑でも柔軟に対応でき、列の位置に関係なく必要な情報を簡単に見つけ出すことができます。
基本のXLOOKUP関数の書き方
まずは基本のXLOOKUP関数をご紹介します。XLOOKUP関数の構成については以下の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからなかった場合の値], [一致モード], [検索モード])
XLOOKUP関数の引数（関数に渡す値や設定）を詳しく見る
-
検索値：検索したい値
-
検索範囲：検索する範囲
-
戻り範囲：取得したい値が含まれる範囲
-
見つからなかった場合の値※：検索値が見つからなかった場合に返す値
-
一致モード※：検索の一致モード（0、-1、1、2）
【探している値が「cat」の場合の具体例】
0（完全一致）：リストが ["cat", "bat", "rat"] の場合、"cat"と完全に一致するものを見つける。
-1（次に小さい値）：リストが ["bat", "cat", "rat"] の場合、"cat"よりも小さい値の中で一番近い"bat"を見つける。
1（次に大きい値）：リストが ["bat", "cat", "rat"] の場合、"cat"よりも大きい値の中で一番近い"rat"を見つける。
2（ワイルドカード一致）：リストが ["cat", "bat", "rat"] の場合、"c*t"を探すと、"cat"が見つかる。"b?t"を探すと、"bat"が見つかる。 -
検索モード※：検索の方向や方法（1、-1、2、-2）
【探している値が「3」の場合の具体例】
1（最初から最後まで探す）：リストが [1, 2, 3, 4, 5] の場合、1から順番に1、2、3と見ていき、3を見つける。
-1（最後から最初まで探す）：リストが [1, 2, 3, 4, 5] の場合、5から順番に5、4、3と見ていき、3を見つける。
2（データが小さい順に早く見つける）：リストが [1, 2, 3, 4, 5] の場合、3を見つける。
-2（データが大きい順に早く見つける）：リストが [5, 4, 3, 2, 1] の場合、3を見つける。 - ※ 見つからなかった場合の値・一致モード・検索モードは省略可能です。
例えば、リストから特定の商品の名前を検索して、その商品の値段を取得するという基本的な使い方ができます。
▼（例）C3のセルにC2のセルで指定した「ぶどう」を範囲内から見つけて、指定した範囲（戻り範囲）にある「値段」を返したい場合。
各引数（関数に渡す値や設定）を詳しく見る
- C2：検索値。C2セルの値を基に検索を行う。
- B6:B9：検索範囲。B6からB9までの範囲内でC2の値を探す。
- C6:C9：戻り範囲。検索値が見つかった場合に、対応するC6からC9までの範囲の値（値段「150円」）を返す。
- 例えば、C2セルに「りんご」と入力されている場合、B6:B9の範囲内で「りんご」を探し、見つかった場合は対応するC6:C9の値（値段「130円」）を返す。
XLOOKUP関数の応用編
XLOOKUP関数では、以下のような特別な記号や条件を使って細かな検索をすることができます。
- *：どんな文字列にも一致します。
- ?：どんな1文字にも一致します。
- >：指定した値より大きい値を検索します。
- <：指定した値より小さい値を検索します。
これらの記号や条件の演算子を関数に入れることで、より柔軟な検索が可能になります。
特別な記号や条件の演算子をもっと見る
| 演算子 | 意味 | 使用例 | 使用例の説明 |
|---|---|---|---|
| * | 任意の文字列に一致 | *りん* | *りん* は「りんご」や「みかんりんご」に一致 |
| ? | 任意の1文字に一致 | りん? | りん? は「りんご」や「りんご」に一致 |
| <> | 等しくない | A1<>B1 |
A1の値とB1の値が違うなら「正しい」、同じなら「間違い」。 例：テストの点数が違うかどうかを確認。 |
| > | より大きい | A1>B1 |
A1の値がB1の値より大きいなら「正しい」、違うなら「間違い」。 例：成績が基準を超えたかどうかを判定。 |
| < | より小さい | A1<B1 |
A1の値がB1の値より小さいなら「正しい」、違うなら「間違い」。 例：在庫が一定数以下かどうかを確認。 |
| >= | 以上 | A1>=B1 |
A1の値がB1の値以上なら「正しい」、違うなら「間違い」。 例：目標値に達しているかどうかを確認。 |
| <= | 以下 | A1<=B1 |
A1の値がB1の値以下なら「正しい」、違うなら「間違い」。 例：予算内に収まっているかどうかを確認。 |
複数条件の検索
ここからは、複数条件の検索について解説します。IF関数と組み合わせることで、単一の条件だけでなく、複数の条件を同時に満たすデータを検索することができます。
例えば、売上データから特定の条件を満たす結果を見つける場合、次のように設定します。
▼（例）C3のセルに、C2のセルで指定した商品名の「ぶどう」と、個数が「5」個以上の商品の「値段」を抽出したい場合 。
各引数（関数に渡す値や設定）を詳しく見る
- IF：もし～ならば。条件が正しい（TRUE）場合に値を返し、正しくない（FALSE）場合は別の値を返す。
- XLOOKUP(C2,B6:B9,D6:D9)：検索範囲。C2セルの値を基に、B6:B9の範囲内で検索し、見つかった場合は対応するD6:D9の値（個数）を探す。
- >=5：条件。演算子>=で〇以上を指定し、指定した数値「5」に対して、個数が「5以上」か確認する。
- XLOOKUP(C2,B6:B9,C6:C9)：値段範囲。条件が真（TRUE）の場合に、対応するC6:C9の値（値段「150円」）を返す。
- 例えば、C2セルに「りんご」と入力されている場合、B6:B9の範囲内で「りんご」を探し、見つかった場合は対応するD6:D9の値の個数が5以上であれば、対応するC6:C9の値（値段「130円」）を返し、5未満の場合は空白を返します。
部分一致の検索
部分一致機能を使えば、特定の文字列の一部に基づいてデータを検索することができます。これにより、より柔軟にデータを抽出でき、必要な情報を簡単に見つけることができます。
例えば、商品名に「ジュース」を含むデータに対応する「値段」を検索する場合、次のように設定します。
▼（例）C3のセルに、商品名に「ジュース」が含まれるデータに対応する「値段」を抽出したい場合 。
各引数（関数に渡す値や設定）を詳しく見る
- "ジュース"：検索値。「ジュース」という文字列を基に検索を行う。
- B6:B9：検索範囲。B6からB9までの範囲内で「ジュース」を探す。
- C6:C9： 戻り範囲。検索値が見つかった場合に、対応するC6からC9までの範囲の値（値段「130円」）を返す。
- 例えば、「ぶどう」を検索すると、B6:B9の範囲内で「ぶどう」を含む商品名を探し、見つかった場合は対応するC6:C9の値（値段「150円」）を返す。
-
条件が見つからない場合: 空文字列 "" を指定しているため、空白が返される。
いろいろな方向に検索できる
XLOOKUP関数の大きな特徴の一つは、検索方向が自由であることです。従来のVLOOKUPやHLOOKUP関数では、検索方向が制限されていましたが、XLOOKUP関数では上下左右どの方向にも検索が可能です。
例えば、次のように設定することで、任意の方向に検索を行うことができます。
▼（例）C3のセルにC2のセルで指定した「ぶどう」を範囲内から見つけて、指定した範囲（戻り範囲）にある「個数」を返したい場合。
各引数（関数に渡す値や設定）を詳しく見る
- C2：検索値。C2セルの値を基に検索を行う。
- D6:D9：検索範囲。D6からD9までの範囲内でC2の値を探す。
- B6:B9：戻り範囲。検索値が見つかった場合に、対応するB6からB9までの範囲の値（個数「8」）を返す。
- 例えば、C2セルに「りんご」と入力されている場合、D6:D9の範囲内で「りんご」を探し、見つかった場合は対応するB6:B9の値（個数「1」）を返す。
将来的に検索方向が変わる可能性がある場合、XLOOKUP関数を使っておくことで、柔軟に対応することができます！
別シートからデータを参照する方法
XLOOKUP関数を使って、別シートからデータを参照する方法を解説します。これにより、複数のシートに分散したデータを効率的に統合し、分析することができます。
例えば、次のようにシート名と感嘆符「!」を関数の前に追加することで、別シートからデータを参照することができます。
▼（例）C3のセルにC2のセルで指定した「ぶどう」を別シート「Sheet2」の範囲内から見つけて、指定した範囲（戻り範囲）にある「個数」を返したい場合。
各引数（関数に渡す値や設定）を詳しく見る
- C2：検索値。C2セルの値を基に検索を行う。
- Sheet2!C2:C5：検索範囲。Sheet2のC2からC5までの範囲内でC2の値を探す。
- Sheet2!A2:A5：戻り範囲。検索値が見つかった場合に、対応するSheet2のA2からA5までの範囲の値（個数「1」）を返す。
- 例えば、C2セルに「りんご」と入力されている場合、Sheet2のC2:C5の範囲内で「りんご」を探し、見つかった場合は対応するSheet2のA2:A5の値（個数「1」）を返す。
XLOOKUP関数のトラブルシューティング
XLOOKUP関数を使うときに起こるかもしれない4つのよくあるエラーと、その解決策を説明します。
よくあるエラーとその解決策
１｜#VALUE!エラーの原因と解決策
- 原因： 検索値や範囲が正しく指定されていない。
- 解決策：
- 検索値や範囲が正しく指定されているか確認。
- 数字と文字が混ざっていないか確認。
- 必要なら、データの種類をそろえる。
▼（例）IFERROR関数を使ってエラー時はセルの表示を「空白」にする場合。
２｜探している値が見つからないとき
- 原因：検索値が正しく入力されていない、または探す範囲が間違っている。
- 解決策：
- 検索値が正しく入力されているか確認。
- 探す範囲が正しいか確認。
- 見つからないときの代わりの値を設定。
▼（例）探している値が見つからないときに「データが見つかりません」と表示する場合。
３｜XLOOKUP関数を使うとエラーになるとき
- 原因：関数の書き方が間違っている、または探す範囲や戻す範囲が間違っている。
- 解決策：
- 関数の書き方が正しいか確認。
- 探す範囲や戻す範囲が正しいか確認。
- ExcelのバージョンがXLOOKUP関数に対応しているか確認。
４｜XLOOKUP関数が使えないとき
- 原因：ExcelのバージョンがXLOOKUP関数に対応していない。
- 解決策：
- ExcelのバージョンがXLOOKUP関数に対応しているか確認。
- Excelを最新バージョンに更新。
- 他の関数（VLOOKUP関数、HLOOKUP関数、INDEX関数+MATCH関数）を使う。
まとめ
XLOOKUP関数は、大量のデータの中から必要な情報を簡単に見つけることができます。特定の条件に合ったデータをすぐに見つけることができるので、仕事の効率が大幅に向上するでしょう。
さらに、XLOOKUP関数はとても柔軟で、数字や文字、日付など、いろいろな種類のデータに対応できるため、幅広い用途で活用できます。
ただし、ExcelのバージョンがXLOOKUP関数に対応していない場合は使用できないため、事前に確認が必要です。
サポート
導入支援
導入事例
製品