はじめに
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,"該当なし")
この数式は、以下の処理を行っています。
- セルB2に入力されている「物品番号」を検索値として使用します。
- D3:D7の範囲（物品リストの「物品番号」欄）を検索範囲として検索を行います。
- E3:E7の範囲（物品リストの「商品名」欄）から、対応する物品名称を返します。
- 検索値が見つからない場合は、「該当なし」と表示します。
- 一致モードと検索モードは省略されているため、既定値の「完全一致」と「先頭から末尾へ検索」が適用されます。
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のバージョンを確認するには、以下の手順を実行します。
- Excelを起動します。
- 「ファイル」タブをクリックします。
- 左側のメニューから「アカウント」または「ヘルプ」をクリックします。
- 「Excel のバージョン情報」または「バージョン情報」をクリックすると、詳細なバージョン情報が表示されます。
XLOOKUP関数の使用を検討する際のポイント
- 最新バージョンのExcelを使用しているか？: XLOOKUP関数を使用できる環境かを確認しましょう。
- ファイルの共有相手の環境は？: ファイルを共有する相手が古いバージョンのExcelを使用している場合は、互換性に注意が必要です。
- 複雑な検索が必要か？: XLOOKUP関数はVLOOKUP関数よりも多機能ですが、簡単な検索であればVLOOKUP関数でも十分な場合があります。
まとめ
XLOOKUP関数は、従来のVLOOKUP関数やHLOOKUP関数の欠点を克服し、より強力で使いやすい検索機能を提供します。特に、柔軟な検索方向、簡単なエラー処理、直感的な近似一致検索は大きな利点です。ただし、使用できるExcelのバージョンに制限があるため、環境を確認してから使用するようにしましょう。最新バージョンのExcelまたはMicrosoft 365をお使いの場合は、積極的にXLOOKUP関数を活用することをお勧めします。
補足説明
上記では、XLOOKUP関数の構文、引数の説明、一致モードと検索モードの説明、具体的な数式例などを記載しました。特に以下の点が重要です。
見つからない場合引数でエラー処理を行っている点。一致モードと検索モードで検索方法を細かく制御できる点。- VLOOKUP関数と比較して、XLOOKUP関数がエラー対策を単独で行える点が強調されている点。
- 画像下部の説明で、VLOOKUPではIFERROR関数と組み合わせる必要があったエラー処理が、XLOOKUPでは単独でできることが強調されています。
- 一致モードの「2 ワイルドカード文字との一致」。
- 検索モード。
コメント