g713｜Excel XLOOKUP関数：表引き検索の決定版！VLOOKUP/HLOOKUPを超える柔軟性

Excelで表形式のデータから特定の値を検索する際、従来のVLOOKUP関数やHLOOKUP関数にはいくつかの制約がありました。そこで登場したのがXLOOKUP関数です。XLOOKUP関数は、これらの制約を克服し、より柔軟で強力な検索機能を提供します。この記事では、XLOOKUP関数の基本的な使い方から応用までを、画像例を交えて詳しく解説します。

XLOOKUP関数とは？

XLOOKUP関数は、指定された範囲または配列で値を検索し、最初に見つかった一致に対応する項目を返す関数です。見つからない場合は、代替値を返すこともできます。

基本的な構文

Excel

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

各引数の意味は以下の通りです。

• 検索値（必須）: 検索する値を指定します。画像例では、セルB2の「品番号」が検索値です。
• 検索範囲（必須）: 検索を行う範囲を指定します。画像例では、D3:D7の「商品番号」の範囲です。
• 戻り範囲（必須）: 結果として返す値が含まれる範囲を指定します。画像例では、E3:E7の「商品名」の範囲です。
• 見つからない場合（省略可能）: 検索値が見つからない場合に返す値を指定します。省略すると#N/Aエラーが返されます。画像例では「該当なし」が指定されています。
• 一致モード（省略可能）: 一致の種類を指定します。
  • 0（既定値）：完全一致。
  • -1：完全一致。見つからない場合は、次に小さい項目を返します。
  • 1：完全一致。見つからない場合は、次に大きい項目を返します。
  • 2：ワイルドカード文字（*、?、~）を使用した部分一致。
• 検索モード（省略可能）: 検索の方向を指定します。
  • 1（既定値）：先頭から末尾へ検索。
  • -1：末尾から先頭へ検索。
  • 2：昇順で並べ替えられた範囲を使用したバイナリ検索。
  • -2：降順で並べ替えられた範囲を使用したバイナリ検索。

＜Excelサンプルデータダウンロード＞
Excel XLOOKUP関数：表引き検索の決定版！VLOOKUP/HLOOKUPを超える柔軟性

画像例の具体的な解説

画像例では、セルB3に以下の数式が入力されています。

Excel

=XLOOKUP(B2,D3:D7,E3:E7,"該当なし")

この数式は、以下の処理を行っています。

1. セルB2に入力されている「物品番号」を検索値として使用します。
2. D3:D7の範囲（物品リストの「物品番号」欄）を検索範囲として検索を行います。
3. E3:E7の範囲（物品リストの「商品名」欄）から、対応する物品名称を返します。
4. 検索値が見つからない場合は、「該当なし」と表示します。
5. 一致モードと検索モードは省略されているため、既定値の「完全一致」と「先頭から末尾へ検索」が適用されます。

Excelのサンプルデータ【ダウンロード】

以下は、上記画像のExcelデータですので、ダウンロードして練習などに使用してください。

Excel-g713-1.xlsx  （ダウンロード）

XLOOKUP関数の利点

• 柔軟な検索: 検索方向（上から下、下から上）や一致モード（完全一致、近似一致、ワイルドカード一致）を自由に指定できます。
• エラー処理が簡単: 見つからない場合引数で、検索値が見つからない場合のエラー表示を簡単に制御できます。IFERROR関数と組み合わせる必要がありません。
• 戻り範囲が検索範囲と隣接していなくても良い: VLOOKUP関数のように、戻り範囲が検索範囲の右側にある必要はありません。
• 近似一致検索がより直感的: 近似一致検索で、次に小さい項目または次に大きい項目を明確に指定できます。
• バイナリ検索による高速化: 大規模なデータに対して、バイナリ検索を使用することで高速に検索できます。

XLOOKUP関数とVLOOKUP/HLOOKUP関数の比較

特徴	XLOOKUP関数	VLOOKUP関数	HLOOKUP関数
検索方向	縦方向、横方向、双方向	縦方向（左から右）	横方向（上から下）
戻り範囲の位置	検索範囲と隣接している必要なし	検索範囲の右側にある必要あり	検索範囲の下側にある必要あり
近似一致	次に小さい/大きい項目を指定	検索範囲の並べ替えが必要、挙動がやや複雑	検索範囲の並べ替えが必要、挙動がやや複雑
エラー処理	見つからない場合引数で簡単に指定	IFERROR関数との組み合わせが必要	IFERROR関数との組み合わせが必要
検索モード	先頭から、末尾から、バイナリ検索	先頭からのみ	先頭からのみ

XLOOKUP関数が使えるExcelのバージョン

XLOOKUP関数は、すべてのExcelバージョンで使えるわけではありません。以下のバージョンで利用可能です。

• Microsoft 365: Microsoft 365のExcelでは、常に最新の機能が提供されるため、XLOOKUP関数を使用できます。
• Excel 2021以降の永続ライセンス版: Excel 2021以降のバージョン（Excel 2021、Excel 2024など）の永続ライセンス版でもXLOOKUP関数が利用可能です。
• Excel Online (Web版): Webブラウザで使用するExcel OnlineでもXLOOKUP関数を使用できます。

重要な注意点:

• Excel 2019以前のバージョンではXLOOKUP関数は使用できません。 古いバージョンのExcelを使用している場合は、VLOOKUP関数やHLOOKUP関数を使用するか、Excelのバージョンアップを検討する必要があります。
• 他のユーザーとファイルを共有する場合: ファイルを共有する相手がExcel 2019以前のバージョンを使用している場合、XLOOKUP関数を使った数式は正常に機能しません。互換性を考慮する必要がある場合は、VLOOKUP関数などを使用するか、ファイルをPDFなどで共有することを検討しましょう。

Excelのバージョン確認方法:

使用しているExcelのバージョンを確認するには、以下の手順を実行します。

1. Excelを起動します。
2. 「ファイル」タブをクリックします。
3. 左側のメニューから「アカウント」または「ヘルプ」をクリックします。
4. 「Excel のバージョン情報」または「バージョン情報」をクリックすると、詳細なバージョン情報が表示されます。

XLOOKUP関数の使用を検討する際のポイント

• 最新バージョンのExcelを使用しているか？: XLOOKUP関数を使用できる環境かを確認しましょう。
• ファイルの共有相手の環境は？: ファイルを共有する相手が古いバージョンのExcelを使用している場合は、互換性に注意が必要です。
• 複雑な検索が必要か？: XLOOKUP関数はVLOOKUP関数よりも多機能ですが、簡単な検索であればVLOOKUP関数でも十分な場合があります。

まとめ

XLOOKUP関数は、従来のVLOOKUP関数やHLOOKUP関数の欠点を克服し、より強力で使いやすい検索機能を提供します。特に、柔軟な検索方向、簡単なエラー処理、直感的な近似一致検索は大きな利点です。ただし、使用できるExcelのバージョンに制限があるため、環境を確認してから使用するようにしましょう。最新バージョンのExcelまたはMicrosoft 365をお使いの場合は、積極的にXLOOKUP関数を活用することをお勧めします。

補足説明

上記では、XLOOKUP関数の構文、引数の説明、一致モードと検索モードの説明、具体的な数式例などを記載しました。特に以下の点が重要です。

• 見つからない場合引数でエラー処理を行っている点。
• 一致モードと検索モードで検索方法を細かく制御できる点。
• VLOOKUP関数と比較して、XLOOKUP関数がエラー対策を単独で行える点が強調されている点。
• 画像下部の説明で、VLOOKUPではIFERROR関数と組み合わせる必要があったエラー処理が、XLOOKUPでは単独でできることが強調されています。
• 一致モードの「2 ワイルドカード文字との一致」。
• 検索モード。