a3｜Excel XLOOKUP関数：縦横検索とVLOOKUPからの進化

ExcelのXLOOKUP関数は、VLOOKUP関数の課題を克服し、より柔軟なデータ検索を可能にする強力なツールです。この記事では、XLOOKUP関数の基本的な使い方から、VLOOKUP関数との違い、さらに応用的な使い方までを解説します。

XLOOKUP関数の基本とVLOOKUP関数との違い

XLOOKUP関数の基本的な使い方

XLOOKUP関数は、検索値、検索範囲、戻り範囲を指定することで、指定した範囲から値を検索します。基本的な構文は「=XLOOKUP(検索値, 検索範囲,戻り範囲, [見つからない場合], [一致モード],[検索モード])」です。例えば、商品コードを検索値として、商品リストから対応する商品名を取得する場合、「=XLOOKUP(A2, $B$2:$B$10,$C$2:$C$10)」のように使用します。A2セルに検索したい商品コードが入力されており、$B$2:$B$10の範囲に商品コード、$C$2:$C$10の範囲に商品名が入力されているとします。この関数は、A2セルの商品コードに対応する商品名を返します。見つからない場合の処理や一致モード、検索モードは必要に応じて指定します。この関数は非常に強力で、VLOOKUP関数やHLOOKUP関数の代わりに、より柔軟にデータ検索が可能になります。

VLOOKUP関数との主な違い

VLOOKUP関数は、検索範囲の左端の列でしか検索値を検索できませんでしたが、XLOOKUP関数では、検索範囲と戻り範囲を別々に指定できるため、検索値が左端にある必要はありません。例えば、VLOOKUP関数では、商品コードが左端の列にないと、商品名を検索できませんでしたが、XLOOKUP関数では、商品コードがどの列にあっても、対応する商品名を検索できます。この柔軟性により、列の挿入や削除を行った際にも、関数を修正する必要がほとんどありません。また、XLOOKUP関数は、見つからない場合の値を指定できるため、エラー処理が簡単です。VLOOKUP関数では、エラーを回避するために、IFERROR関数などを別途使う必要がありましたが、XLOOKUP関数では、その必要がありません。このように、XLOOKUP関数は、VLOOKUP関数と比較して、より柔軟で便利な関数です。

XLOOKUP関数の検索方向の指定方法

XLOOKUP関数は、縦方向の検索だけでなく、横方向の検索も簡単に行えます。これは、VLOOKUP関数やHLOOKUP関数のように、縦横を意識する必要がないためです。例えば、商品名が1行目にあり、各商品の価格が2行目以降に並んでいる場合、XLOOKUP関数を使えば、特定の商品の価格を簡単に検索できます。具体的な構文は、縦検索とほとんど同じで、「=XLOOKUP(検索値,検索範囲,戻り範囲)」となります。検索範囲と戻り範囲を、それぞれ横方向に指定することで、横方向の検索が可能になります。この機能により、様々な形式のデータに対応でき、より柔軟なデータ処理が可能になります。例えば、表形式のデータだけでなく、リスト形式のデータや、様々なレイアウトのデータに対しても、XLOOKUP関数一つで対応できます。

XLOOKUP関数の引数：一致モードと検索モードの活用

一致モードの設定方法

XLOOKUP関数では、完全一致だけでなく、近似値検索やワイルドカード検索も可能です。一致モードは、引数の5番目に指定します。完全一致の場合は「0」、近似一致の場合は「-1」または「1」、ワイルドカード文字検索の場合は「2」を指定します。完全一致は、検索値と完全に一致する値を検索します。近似一致は、検索値より小さい最大の値（-1）または、検索値より大きい最小の値（1）を検索します。ワイルドカード文字検索は、「」や「?」などのワイルドカード文字を使って、曖昧な検索を行うことができます。例えば、「商品名」のように指定することで、「商品名」で始まるすべての値を検索できます。これらの機能を使うことで、より柔軟なデータ検索が可能になります。一致モードを適切に設定することで、様々な検索ニーズに対応できます。

XLOOKUPの一致モードとは

XLOOKUP関数の一致モードは、データを検索する方法を指定する重要な機能です。適切な一致モードを選択することで、完全一致、近似値検索、ワイルドカード検索など、様々な検索ニーズに対応できます。

XLOOKUP関数の基本構文

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

各引数の詳細説明：

1. 検索値（必須）

• 検索したい値を指定する
• 使用可能なデータ：文字列、数値、日付
• セル参照可能（例：A1）
• 数式の結果も使用可能（例：TODAY()）

1. 検索範囲（必須）

• 検索する列や範囲を指定
• 単一列のみ指定可能
• 例：A2:A10、B:B

1. 戻り値の範囲（必須）

• 結果として返したい列や範囲
• 検索範囲と同じ行数が必要
• 複数列の指定が可能
• 例：B2:B10、C2:E10

1. 見つからない場合の値（省略可）

• 検索値が見つからない場合の返り値
• 省略時は#N/A
• テキスト、数値、エラー値などを指定可能

1. 一致モード（省略可）

• 0：完全一致（デフォルト）
• -1：より小さい最大値
• 1：より大きい最小値
• 2：ワイルドカード検索

1. 検索モード（省略可）

• 0：先頭から検索（デフォルト）
• -1：末尾から検索
• 2：バイナリ検索（昇順）
• -2：バイナリ検索（降順）

サンプルデータ：商品マスター

以下のデータがA1:D8のセルに入力されているとします。

一致モードごとの使用例と解説

1. 完全一致モード（0）の使用例

=XLOOKUP("A003", A2:A8, B2:B8, "商品が見つかりません", 0)

実行過程：

1. 商品コード列（A2:A8）から「A003」を検索
2. 完全一致する値を探す
3. A4セルで一致を発見
4. B4セルの値「商品名Cデラックス」を返す

結果: 商品名Cデラックス

以下のExcelデータをダウンロードし、練習用としてお試しください。
【Excelデータ】ダウンロード  

2. 近似一致 – より小さい最大値（-1）の使用例

=XLOOKUP(2200, C2:C8, B2:B8, "該当する商品がありません", -1)

実行過程：

1. 価格列（C2:C8）から2200より小さい最大値を検索
2. 価格を順に比較：

• 1000 < 2200
• 1500 < 2200
• 2000 < 2200（最大値）
• 2500 > 2200

1. 2000円に対応する商品名を返す

結果: 商品名Cデラックス

以下のExcelデータをダウンロードし、練習用としてお試しください。
【Excelデータ】ダウンロード  

3. 近似一致 – より大きい最小値（1）の使用例

=XLOOKUP(2200, C2:C8, B2:B8, "該当する商品がありません", 1)

実行過程：

1. 価格列（C2:C8）から2200より大きい最小値を検索
2. 価格を順に比較：

• 2500 > 2200（最小値）

1. 2500円に対応する商品名を返す

結果: 商品名D

以下のExcelデータをダウンロードし、練習用としてお試しください。
【Excelデータ】ダウンロード  

4. ワイルドカード検索（2）の使用例

=XLOOKUP("商品名*", B2:B8, C2:C8, "該当する商品がありません", 2)

実行過程：

1. 商品名列（B2:B8）から「商品名」で始まる値を検索
2. 最初に一致した商品の価格を返す
3. B2セルの「商品名A001プラス」が該当

結果: 1000

以下のExcelデータをダウンロードし、練習用としてお試しください。
【Excelデータ】ダウンロード  

実務での活用ポイント

1. 完全一致（0）の使用シーン

• マスターデータとの突合
• コード検索
• 厳密な値の照合

1. 近似一致（-1, 1）の使用シーン

• 価格帯での商品検索
• 日付範囲での検索
• 閾値を基準とした検索

1. ワイルドカード（2）の使用シーン

• 商品名の曖昧検索
• 前方一致/後方一致検索
• 部分一致検索

重要な注意点

1. 近似一致使用時

• データは必ず昇順にソート
• 数値データのみ使用可能
• 文字列には使用不可

1. ワイルドカード検索時

• *：任意の文字列
• ?：任意の1文字
• 大文字小文字は区別

1. エラー処理

• 見つからない場合の値を必ず設定
• 適切なエラーメッセージを準備
• データ型の整合性に注意

検索モードの設定方法

XLOOKUP関数では、検索方向を上から下、または下から上へと指定できます。検索モードは、引数の6番目に指定します。上から下へ検索する場合は「1」、下から上へ検索する場合は「-1」を指定します。検索モードを指定しない場合は、デフォルトで上から下の検索となります。例えば、商品リストの中で、同じ商品コードが複数存在する場合、検索モードを下から上に指定することで、リストの最後の商品の情報を取得できます。また、上から下に検索する場合でも、デフォルトで最初の情報が返ってくるため、必要に応じて使い分けることが重要です。検索モードを適切に設定することで、データの特性に合わせた適切な検索方法を選択できます。

XLOOKUPの検索モードとは

XLOOKUP関数の検索モードは、データを検索する方向や方法を指定する機能です。データの特性や目的に応じて検索方向を変更することで、より柔軟なデータ検索が可能になります。

XLOOKUP関数の基本構文

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

各引数の詳細説明：

1. 検索値（必須）

• 検索したい値を指定
• 文字列、数値、日付が使用可能
• セル参照可能（例：A1）
• 数式の結果も使用可能

1. 検索範囲（必須）

• 検索する列や範囲を指定
• 単一列のみ指定可能
• 例：A2:A10、B:B

1. 戻り値の範囲（必須）

• 結果として返したい列や範囲
• 検索範囲と同じ行数が必要
• 複数列の指定が可能

1. 見つからない場合の値（省略可）

• 検索値が見つからない場合の返り値
• 省略時は#N/A
• テキスト、数値、エラー値を指定可能

1. 一致モード（省略可）

• 0：完全一致（デフォルト）
• -1：より小さい最大値
• 1：より大きい最小値
• 2：ワイルドカード検索

1. 検索モード（省略可）

• 0：上から下へ検索（デフォルト）
• -1：下から上へ検索
• 2：バイナリ検索（昇順）
• -2：バイナリ検索（降順）

サンプルデータ：商品マスター（重複データあり）

検索モードごとの使用例と解説

1. 上から下への検索（検索モード = 0）

=XLOOKUP("A001", A2:A11, B2:D11, "商品が見つかりません", 0, 0)

実行過程：

1. 商品コード列（A2:A11）から「A001」を検索
2. 上から順に検索
3. 最初に見つかった一致（A2セル）の値を使用
4. B2:D2の値を返す

結果: 商品名A001プラス, 1000, 50

2. 下から上への検索（検索モード = -1）

=XLOOKUP("A001", A2:A11, B2:D11, "商品が見つかりません", 0, -1)

実行過程：

1. 商品コード列（A2:A11）から「A001」を検索
2. 下から順に検索
3. 最後に登録された一致（A9セル）の値を使用
4. B9:D9の値を返す

結果: 商品名A001プラス(限定), 1200, 10

エラー処理とIFERROR関数の代替

XLOOKUP関数には、検索値が見つからない場合に表示する値を指定する機能があります。これは、IFERROR関数の代替として使用できます。XLOOKUP関数の4番目の引数に、見つからない場合に表示する値を指定することで、エラー表示を回避できます。例えば、「=XLOOKUP(A2,$B$2:$B$10, $C$2:$C$10,’該当なし’)」のように指定した場合、A2セルの商品コードが$B$2:$B$10の範囲に見つからない場合、「該当なし」と表示されます。このように、XLOOKUP関数だけでエラー処理を行えるため、数式が簡潔になり、可読性が向上します。また、IFERROR関数を別途使用する必要がないため、関数のネストが深くなるのを防ぎ、数式の管理が容易になります。エラー処理を効率的に行うことで、より実用的なデータ分析が可能になります。

XLOOKUP関数の応用：スピル機能との組み合わせ

スピル機能の概要

スピル機能とは、Excelの機能の一つで、数式の結果を複数のセルに自動的に展開する機能です。従来のExcelでは、数式をコピー&ペーストする必要がありましたが、スピル機能を使うことで、一度の入力で複数のセルに結果を表示できます。この機能は、特に配列数式を使う際に便利で、複雑な数式でも簡単に扱えます。例えば、ある範囲のデータに対して、特定の計算を適用する場合、スピル機能を使えば、その範囲全体に一度で計算結果を反映させることができます。スピル機能を使うことで、作業効率が大幅に向上し、数式のエラーも減らすことができます。XLOOKUP関数とスピル機能を組み合わせることで、さらに高度なデータ処理が可能になります。

具体的な活用例：商品リストから複数情報を抽出

XLOOKUP関数とスピル機能を組み合わせることで、商品リストから商品名、価格、在庫数など、複数の情報を一度に抽出できます。例えば、商品コードがA列にあり、商品名がB列、価格がC列、在庫数がD列にある場合、以下のような数式で、複数の情報を抽出できます。「=XLOOKUP(F2#,A:A,B:D)」。ここで、F2セルに抽出したい商品コードを入力すると、F2#でスピル機能が有効になり、F列に入力されたすべての商品コードに対応する商品名、価格、在庫数が、G列、H列、I列に自動的に表示されます。これにより、手作業でのデータ抽出の手間が大幅に削減され、正確なデータ抽出が可能になります。スピル機能を使うことで、複数の情報を一度に抽出できるため、作業効率が大幅に向上します。

スピル機能によるデータ抽出の応用

スピル機能を活用することで、抽出されたデータを別の場所で加工したり、グラフを作成したりすることが容易になります。例えば、XLOOKUP関数で抽出したデータを基に、SUM関数で合計値を計算したり、AVERAGE関数で平均値を計算したりすることができます。また、抽出されたデータを基に、グラフを作成することで、データの可視化が容易になります。スピル機能は、抽出したデータの範囲を自動的に調整してくれるため、データの追加や削除があっても、数式を修正する必要がありません。これにより、常に最新のデータに基づいた分析やグラフ作成が可能になります。このように、スピル機能を活用することで、データ分析の幅が広がり、より高度な分析が可能になります。

XLOOKUP関数の注意点と互換性

Excelのバージョンによる制限

XLOOKUP関数は、すべてのExcelバージョンで利用できるわけではありません。この関数は、比較的新しい関数であり、Microsoft365または、Excel2021以降のバージョンでのみ利用可能です。古いバージョンのExcelを使っている場合は、XLOOKUP関数を利用できません。この場合、VLOOKUP関数やINDEX関数、MATCH関数などを組み合わせて代替する必要があります。XLOOKUP関数を使うためには、Microsoft365へのアップグレードを検討するか、最新のExcelバージョンを入手する必要があります。もし、複数のユーザーが異なるExcelバージョンを使用している場合、互換性の問題が発生する可能性があるため、注意が必要です。互換性を考慮して、関数を選択する必要があります。

VLOOKUP関数との移行に関する注意点

VLOOKUP関数からXLOOKUP関数に移行する際は、関数の引数や動作の違いに注意する必要があります。XLOOKUP関数は、VLOOKUP関数よりも引数が多く、柔軟な設定が可能ですが、その分、使い方を間違えると意図しない結果になることがあります。例えば、VLOOKUP関数では、検索範囲の左端列が検索対象でしたが、XLOOKUP関数では、検索範囲と戻り範囲を別々に指定するため、範囲の指定を間違えるとエラーになります。また、一致モードや検索モードの設定も、VLOOKUP関数にはない機能のため、注意が必要です。VLOOKUP関数からXLOOKUP関数に移行する際は、関数の引数や動作をよく理解し、テストを行いながら慎重に進める必要があります。一度XLOOKUP関数を使い慣れてしまえば、VLOOKUP関数よりも効率的なデータ検索が可能になります。

まとめ：XLOOKUP関数を使いこなすために

XLOOKUP関数は、Excelでのデータ検索を効率化する強力なツールです。この関数は、VLOOKUP関数やHLOOKUP関数と比較して、より柔軟で多機能です。XLOOKUP関数の基本的な使い方、一致モードと検索モードの活用、スピル機能との組み合わせ、注意点などを理解することで、より効率的なデータ分析が可能になります。この記事で解説した内容を参考に、ぜひXLOOKUP関数を使いこなしてください。また、さらに深く学びたい場合は、書籍「神速Excel」や、動画サービス「bizplay」もおすすめです。これらの教材を活用することで、XLOOKUP関数だけでなく、Excel全体のスキルアップに繋がります。Excelを使いこなすことで、日々の業務を効率化し、より高度な分析や資料作成が可能になります。この関数をマスターし、業務効率化を目指しましょう。積極的に活用することで、データ分析の精度を高め、ビジネスの意思決定をサポートします。XLOOKUP関数は、Excelユーザーにとって必須のツールと言えるでしょう。