【Excel】XLOOKUP関数で複数列返却・逆方向検索をする方法

Excelでのデータ検索で、VLOOKUP関数では手間がかかると感じていませんか。複数の列から情報を一度に取得したい、あるいはリストの末尾から最新データを見つけたい場面はよくあります。

XLOOKUP関数を使えば、これらの複雑な検索も簡単な数式で実現できます。この関数は、従来の検索関数よりも柔軟で強力なデータ検索機能を提供します。

この記事では、XLOOKUP関数を使って複数列のデータを一度に返したり、逆方向から検索したりする具体的な方法を解説します。これにより、Excelでのデータ検索が格段に効率化できます。

XLOOKUP関数とは?柔軟なデータ検索機能

XLOOKUP関数は、Excel for Microsoft 365で利用できる強力な検索関数です。従来のVLOOKUP関数やHLOOKUP関数の後継として、その制限の多くを解消しています。

この関数は、検索値を左右どちらの列からでも探すことができます。さらに、複数列のデータを一度に取得する「スピル」機能に対応しています。スピルとは、数式を入力したセルから結果が自動的にあふれ出て表示される動作のことです。

また、検索の方向や一致の種類を細かく指定できる「検索モード」や「一致モード」といった引数も持っています。これにより、より高度な検索条件に対応できます。

この関数はExcel for Microsoft 365で導入されました。そのため、Excel 2019やExcel 2021などの永続ライセンス版では利用できません。利用環境のバージョンを確認してください。

XLOOKUP関数で複数列のデータを一度に取得する手順

XLOOKUP関数では、検索値に一致する行のデータを複数列にわたって一度に取得できます。これにより、VLOOKUP関数のように複数回数式を入力する手間が省けます。

基本の数式構造とスピル機能

XLOOKUP関数の基本的な構文は=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])です。このうち、複数列を返すには「戻り範囲」の指定が重要です。

「戻り範囲」には、取得したいすべての列を含む範囲を指定します。XLOOKUP関数を単一セルに入力するだけで、結果が右隣のセルにも自動的に表示されます。これがスピル機能です。

複数列を返す具体的な入力手順

1. データと検索条件の準備
   商品ID、商品名、単価、在庫数などが入力されたデータ範囲を用意します。検索したい商品IDも別のセルに入力しておきましょう。
2. 数式入力セルの選択
   XLOOKUP関数の結果を表示させたい最初のセル、例えば商品名を表示したいセルを選択します。
3. XLOOKUP関数の入力
   選択したセルに=XLOOKUP(と入力します。
4. 検索値の指定
   検索したい商品IDが入力されたセル、例えばA2セルを検索値として指定し、A2,と入力します。
5. 検索範囲の指定
   商品IDが入力されている列全体、例えばB列全体を検索範囲として指定し、B:B,と入力します。
6. 戻り範囲の指定
   取得したい商品名、単価、在庫数の列全体、例えばC:E列をまとめて戻り範囲として指定し、C:E)と入力します。
7. 数式の確定
   Enterキーを押すと、数式を入力したセルに商品名が表示されます。そして、右隣のセルには単価、さらに右隣のセルには在庫数が自動的に表示されます。

XLOOKUP関数で逆方向からデータを検索する手順

データリストの末尾にある最新の情報や、重複する項目の中で最後に出現する値を探したい場合があります。XLOOKUP関数なら、このような逆方向からの検索も簡単に実行できます。

検索モードの指定方法

XLOOKUP関数の最後の引数である「検索モード」は、検索の方向や種類を制御します。逆方向検索を行うには、この引数に「-1」を指定します。

「-1」は、「最後から最初へ検索」を意味します。つまり、データ範囲の末尾から先頭に向かって検索値を探します。デフォルトの検索モードは「1」であり、これは「最初から最後へ検索」を意味します。

逆方向検索の具体的な入力手順

1. データと検索条件の準備
   日付と取引データが入力された範囲を用意します。最新の取引を見つけたい商品名などを検索値として準備しておきましょう。
2. 数式入力セルの選択
   XLOOKUP関数の結果を表示させたいセルを選択します。
3. XLOOKUP関数の入力
   選択したセルに=XLOOKUP(と入力します。
4. 検索値の指定
   検索したい商品名が入力されたセル、例えばA2セルを検索値として指定し、A2,と入力します。
5. 検索範囲の指定
   商品名が入力されている列全体、例えばB:B列を検索範囲として指定し、B:B,と入力します。
6. 戻り範囲の指定
   取得したい取引日の列全体、例えばC:C列を戻り範囲として指定し、C:C,と入力します。
7. 省略可能な引数の指定
   見つからない場合の引数を省略し、0,と入力します。一致モードは完全一致である0を指定し、0,と入力します。
8. 検索モードの指定
   逆方向検索を行うため、検索モードに-1を指定し、-1)と入力します。
9. 数式の確定
   Enterキーを押すと、データリストの末尾から検索された最新の取引日が表示されます。

XLOOKUP関数でよくある入力ミスと対処

XLOOKUP関数は強力ですが、使い方を誤ると期待通りの結果が得られないことがあります。ここでは、よくある失敗例とその対処法を解説します。

#N/Aエラーが表示されてしまう

XLOOKUP関数が#N/Aエラーを返す場合、検索値が検索範囲内で見つからないことが主な原因です。入力ミスやスペースの有無を確認してください。

対処法:

1. 検索値と検索範囲の確認
   検索値が入力されたセルと、検索対象となる範囲が正しく指定されているか確認します。
2. データ形式の確認
   検索値と検索範囲内のデータが同じ数値形式か文字列形式かを確認します。見た目は同じでも異なる場合があります。
3. ワイルドカード検索の利用
   部分一致で検索したい場合は、一致モードに「2」を指定し、検索値にアスタリスク「*」などのワイルドカード文字を含めます。

期待したデータと異なる結果が返る

XLOOKUP関数が間違ったデータを返す場合、戻り範囲の指定ミスや、一致モードの設定が不適切である可能性があります。

対処法:

1. 戻り範囲の再確認
   取得したいデータが含まれる列が、戻り範囲として正確に指定されているか確認します。
2. 一致モードの確認
   デフォルトでは完全一致ですが、意図せず近似一致になっている場合があります。完全一致が必要な場合は、一致モード引数を「0」に明示的に指定します。

Excel 2019/2021でXLOOKUP関数が使えない

XLOOKUP関数はExcel for Microsoft 365の機能であり、Excel 2019やExcel 2021などの永続ライセンス版では利用できません。

対処法:

1. VLOOKUP関数やINDEX+MATCH関数の代替
   XLOOKUP関数が使えない環境では、VLOOKUP関数や、より柔軟なINDEX関数とMATCH関数を組み合わせた方法で同様の検索を実現できます。
2. Excel for Microsoft 365への更新検討
   XLOOKUP関数のメリットを享受したい場合は、Excel for Microsoft 365への契約や更新を検討することをおすすめします。

XLOOKUP関数とVLOOKUP関数の機能比較

XLOOKUP関数はVLOOKUP関数の多くの課題を解決するために設計されました。両関数の主な違いを比較します。

項目	XLOOKUP関数	VLOOKUP関数
検索方向	左右どちらの列も検索対象にできる	検索値は必ず左端の列にある必要があった
複数列取得	戻り範囲を複数列指定することで一度に複数列のデータを取得できる	1つの数式で1列しか取得できず、複数回入力が必要
エラー処理	見つからない場合の引数を指定できる	#N/Aエラーを抑制するにはIFERROR関数などの併用が必要
検索モード	最初から/最後から、昇順/降順など、柔軟な検索方法を指定できる	最初から検索するのみ
対応バージョン	Excel for Microsoft 365のみ	ほとんどのExcelバージョンで利用できる

まとめ

この記事では、XLOOKUP関数を使って複数列のデータを一度に返したり、逆方向から検索したりする方法を解説しました。VLOOKUP関数では難しかった柔軟なデータ検索を、XLOOKUP関数によって簡単に実現できます。

今回の操作を習得することで、データ集計や分析の効率が大きく向上するでしょう。さらに、XLOOKUP関数にはワイルドカード検索や近似一致など、他にも多くの便利な機能があります。

ぜひこれらの機能を試して、業務でのExcel活用をさらに深化させてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】