ExcelにおけるXLOOKUPの紹介
XLOOKUPは、最新バージョンのMicrosoft Excelに導入された最も強力な検索関数の1つです。VLOOKUPやHLOOKUP、さらにはINDEXとMATCHの組み合わせなど、古い関数の代わりとして機能します。シンプルな構文と柔軟性を持つXLOOKUPを使用すると、データセット内の値を迅速かつ正確に検索し、一致する結果を返すことができます。
大規模なスプレッドシート（売上報告書、従業員リスト、在庫表など）を定期的に扱う方にとって、XLOOKUPはワークフローを大幅に簡素化します。このチュートリアルでは、XLOOKUPをステップバイステップで使う方法、構文の理解、実際の例への適用方法を学びます。
XLOOKUPがVLOOKUPより優れている理由
数式に飛び込む前に、多くのExcelのプロが古い検索関数よりもXLOOKUPを好む理由を理解することが重要です。
- 列インデックス番号が不要 – VLOOKUPとは異なり、手動で列をカウントする必要はありません。
- 左または右の方向で機能する – XLOOKUPは、いずれの方向からも値を返すことができます。
- エラーハンドリングが組み込まれている – 一致が見つからない場合に何を返すかを簡単に定義できます。
- より柔軟な検索オプション – 正確な一致、近似一致、およびワイルドカード検索をサポートします。
- 縦横の検索を処理する – VLOOKUPとHLOOKUPの両方を必要としません。
これらの改善により、XLOOKUPは保守が容易で、実際のビジネススプレッドシートでのエラーの原因が少なくなります。
XLOOKUPの数式構文
XLOOKUPの一般的な構文は次のとおりです：
=XLOOKUP(lookup_value, lookup_array, return_array, [if_not_found], [match_mode], [search_mode])
引数の説明
- lookup_value – 検索したい値。
- lookup_array – Excelが検索する範囲。
- return_array – 返したい結果が含まれる範囲。
- if_not_found – 値が見つからない場合のオプションメッセージ。
- match_mode – 一致の種類を定義します（正確、近似、ワイルドカード）。
- search_mode – 検索方向を制御します（先頭から末尾、または末尾から先頭）。
ほとんどの日常的な使用例では、最初の3つの引数だけが必要になります。
例のデータセット
シンプルな従業員テーブルを想像してみましょう：
- 列A: 従業員ID
- 列B: 従業員名
- 列C: 部署
- 列D: 給与
従業員IDを検索し、従業員の名前を自動的に返したいと思っています。
ステップバイステップ：最初のXLOOKUPの数式
ステップ1: 検索値の準備
ユーザーが検索したいIDを入力するセルを選択します。たとえば：
セルF2 = 検索する従業員ID
ステップ2: 検索列の特定
検索したい値を含む列が検索範囲です。私たちの例では：
A2:A100
この列には従業員IDが含まれています。
ステップ3: 返す列の選択
返す配列は、返したいデータを含む列です。従業員名を返すには：
B2:B100
ステップ4: XLOOKUPの数式を入力
結果セルに次のように入力します：
=XLOOKUP(F2, A2:A100, B2:B100)
Excelは、F2に入力した値を範囲A2:A100内で検索します。一致が見つかると、B2:B100の対応する値を返します。
欠損値の処理
検索数式で一般的に発生する問題の1つは、データセットに値が存在しない場合です。XLOOKUPでは、#N/Aというエラーを返すのではなく、カスタムメッセージを表示できます。
例の数式
=XLOOKUP(F2, A2:A100, B2:B100, "従業員が見つかりません")
Excelが従業員IDを見つけられない場合、「従業員が見つかりません」というメッセージを表示します。
複数の列を返す
XLOOKUPは、一度に複数の値を返すこともできます。たとえば、従業員の部署と給与の両方を返したい場合、返す範囲を拡張できます。
例の数式
=XLOOKUP(F2, A2:A100, C2:D100)
この数式は、2つの列を返します：
- 部署
- 給与
Excelは自動的に隣接するセルに結果を表示します。
XLOOKUPを使った左側の検索
VLOOKUPの制限の1つは、左から右のみを検索できることです。XLOOKUPにはこの制限がありません。
例えば、スプレッドシートで従業員名の前にIDがリストされているとしましょう：
- 列A: 従業員名
- 列B: 従業員ID
IDを検索して名前を返すには、次のようにします：
=XLOOKUP(E2, B2:B100, A2:A100)
この柔軟性により、スプレッドシートの設計が大幅に簡単になります。
XLOOKUPでのワイルドカードの使用
XLOOKUPは部分一致のためにワイルドカード文字をサポートしています。
最も一般的なワイルドカードは以下の通りです：
- * – 複数の文字を表す
- ? – 1文字を表す
例
名前が「Sam」で始まる従業員を検索したい場合：
=XLOOKUP("Sam*", B2:B100, C2:C100,,2)
マッチモード引数の2は、ワイルドカードマッチングを有効にします。
最後から最初を検索
データセット内の最新のエントリを見つけたいことがあります。XLOOKUPは、下から上に検索することを可能にします。
例の数式
=XLOOKUP(F2, A2:A100, D2:D100,,0,-1)
この-1の検索モードは、Excelに下から上に検索するように指示します。
実世界での使用ケース
1. 売上報告
売上報告書では、製品コードに基づいて製品価格を取得したいことがあります。
例：
=XLOOKUP(A2, Products!A:A, Products!C:C)
この数式は製品コードを見つけ、その価格を返します。
2. 在庫管理
XLOOKUPは、製品IDが入力されたときに在庫レベルをすぐに表示できます。
=XLOOKUP(E2, A2:A500, D2:D500)
これは倉庫チームが利用可能な在庫を監視するのに役立ちます。
3. 人事従業員記録
人事部門はしばしば、大規模なスプレッドシートに従業員情報を保存します。XLOOKUPは以下を取得できます：
- 従業員の部署
- 入社日
- 給与
- マネージャー名
これにより、人事担当者は迅速な従業員検索ツールを構築できます。
避けるべき一般的な間違い
1. 不一致の範囲
検索配列と返す配列は通常、同じ行数または列数を持っている必要があります。不一致の範囲は、誤った結果を生む可能性があります。
2. 不適切なマッチモード
デフォルトでは、XLOOKUPは正確な一致を実行します。近似一致やワイルドカード検索が必要な場合は、正しいマッチモードを指定する必要があります。
3. 誤った列を検索
検索配列に検索する値が含まれていることを確認してください。Excelが値を見つけられない場合、「見つからない」結果を引き起こします。
XLOOKUPを使用するための実用的なヒント
- 名前付き範囲を使用することで、数式を読みやすくします。
- データ検証と組み合わせることで、検索可能なダッシュボードを作成します。
- 構造化テーブルを使用し、動的に自動で拡張する数式を作成します。
- エラーメッセージを追加し、
if_not_found引数を使用します。
これらの小さな改善により、チームがスプレッドシートを維持するのが容易になります。
XLOOKUPとINDEXおよびMATCH
XLOOKUPが登場する前に、多くの上級ユーザーは、VLOOKUPの制限を克服するためにINDEXとMATCHの組み合わせに依存していました。
例：
=INDEX(B2:B100, MATCH(F2, A2:A100, 0))
XLOOKUPはこのプロセスを大幅に簡素化します：
=XLOOKUP(F2, A2:A100, B2:B100)
その結果は、読みやすく、監査が容易で、構築も早くできます。
XLOOKUPを使用すべき時
XLOOKUPは以下の場合に理想的です：
- 頻繁にデータセットを検索して一致する値を探す場合。
- 古いVLOOKUP数式を置き換えたい場合。
- 異なる方向にわたる柔軟な検索が必要な場合。
- 組み込みのエラーハンドリングが欲しい場合。
ただし、XLOOKUPはMicrosoft 365やExcel 2021以降の新しいExcelバージョンでのみ利用可能であることを覚えておいてください。
結論
XLOOKUPは、データを扱うプロフェッショナルにとって最も便利な現代Excelの関数の1つです。その柔軟な設計により、古い検索数式の多くの制限が解消され、よりクリーンで信頼性の高いスプレッドシートを構築できるようになります。
基本的な構文を理解し、実際の例で練習することにより、従業員検索、製品の照会、売上報告、在庫追跡などのタスクにXLOOKUPを迅速に適用できます。この関数に慣れると、VLOOKUPやHLOOKUPのほとんどを置き換えることになるでしょう。
小規模なデータセットでの練習から始め、ワイルドカードマッチングや逆検索のようなオプション引数を試しながら、徐々にXLOOKUPを日常のExcelワークフローに統合してください。