XLOOKUP 関数でデータ検索が楽に！従来のVLOOKUP/HLOOKUPとの違いと基本的な使い方

スプレッドシートでデータを検索するのに苦労することありませんか？

特にVLOOKUP や HLOOKUP 関数を使っていると、表に次々とデータを追加していって検索範囲が増えたり、列や行を変更した時に、関数がうまく機能しなくなることがあります。

データが増えて構造が複雑になると、どの列を参照すべきか、または検索方向の制約によって関数を使い続けることが難しくなるかもしれません。

そんな時におすすめなのが、XLOOKUP関数です。

XLOOKUPは、VLOOKUPやHLOOKUPのような従来の検索関数の進化版です。

この記事では、XLOOKUPの概要と基本的な使い方についてご紹介します。

データが増えたり構造が複雑になっても柔軟に対応できるため、効率的なデータ検索が可能になります。

XLOOKUPとVLOOKUP/HLOOKUPの違い

• XLOOKUPは縦横どちらの方向にも対応可能
• データの配置がどのようであっても柔軟に検索できるのが強み

XLOOKUPは、縦横どちらの方向にも対応してデータを検索できる新しい関数です。

従来のVLOOKUPやHLOOKUPは検索方向に制約がありましたが、XLOOKUPは任意の列や行から柔軟に検索できます。

検索結果が見つからなかった場合は、エラーメッセージの代わりに「見つかりません」といった任意のメッセージを設定できるため、ユーザーにとって分かりやすい結果を返すことができます。

この機能により、データが見つからない際の対応が簡単になり、エラーハンドリングが効率的に行えます。

VLOOKUPとの違い

VLOOKUPは縦方向にデータを検索しますが、検索の基準は常に左端の列に限定されており、そこから右側のデータを取得する仕組みです。

このため、検索対象が左端にない場合、シートの構造を変更しなければなりません。

HLOOKUPとの違い

HLOOKUPは横方向にデータを検索しますが、最上行から検索し、対応する値を取得します。

このため、データが縦に多い場合や、複数の条件で検索したい場合には向いていません。

XLOOKUPの構文と基本的な使い方

XLOOKUPの基本的な構文は以下の通りです。

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

• 検索値：検索したい値。
• 検索範囲：検索を行う範囲。
• 戻り値範囲：検索結果に対応する値を返す範囲。
• 見つからない場合：値が見つからなかったときの返り値（省略可能）。

一致の種類

完全一致や近似一致を指定します。

例えば、0を指定すると完全一致、1や-1を指定すると近似一致での検索が可能です。

これにより、検索結果が正確な値でなくても、最も近い値を取得できます。

検索モード

検索を行う順序を指定します。

1は最初から最後まで順に検索し、-1は最後から最初まで逆順で検索します。

このオプションにより、同じ値が複数ある場合に取得したいデータの順序をコントロールできます。

基本的な使い方①

従来のVLOOKUPでは、左から右方向への検索しかできませんでしたが、XLOOKUPを使うと、任意の列から任意の方向に検索が可能です。

商品名を入力すると、商品IDを検索できるようにしたい場合、

• E2：商品名
• F2：商品IDの検索結果

商品名「ノート」から対応する商品IDを取得するには、XLOOKUPを使って次のように検索できます。

=XLOOKUP(E2, B2:B, A2:A, "商品が見つかりません")

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

商品名 「ノート」 に対応する商品ID 「102」 を取得できます。

基本的な使い方②（データを下から検索）

• A列：ミーティング日
• B列：参加者の名前
• C列：ミーティング内容

XLOOKUPを使って下から検索する方法があります。

この場合、検索モードを-1に設定することで、最後から最初に向かって検索を行うことができます。

例えば、ミーティングの実施日を記録した表があるとします。

最新のミーティング情報を取得したい場合、XLOOKUPの検索モードを使用して、下から上に検索することができます。

• E2：検索する参加者の名前
• F2：直近のミーティング日

ここで、「田中」さんの直近のミーティング日を知りたい場合、次のようにXLOOKUP関数を使用します。

=XLOOKUP(E2, B2:B, A2:A, "ミーティングが見つかりません", 0, -1)

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

• 検索値：E2（探したい名前）
• 検索範囲：B2:B（名前の列）
• 戻り値範囲：A2:A（日付の列）
• 見つからない場合：”ミーティングが見つかりません”（検索結果がない場合のメッセージ）
• 一致の種類：0（完全一致）
• 検索モード：-1（下から上に検索）

この数式で、「田中」さんの直近のミーティング日である「2024/11/20」が返されます。

直近のミーティング内容が知りたい場合は、次のような数式にします。

=XLOOKUP(E2, B2:B, C2:C, "ミーティングが見つかりません", 0, -1)

XLOOKUPを使うことで、下から上に検索し、直近のデータを簡単に取得することができます。

XLOOKUPを使う場面と他の関数の使い分け

まとめ

XLOOKUPは、VLOOKUPやHLOOKUPの欠点を解消し、柔軟なデータ検索を実現できる関数です。

縦横どちらの方向にも対応できる点や、エラーハンドリングが容易な点が大きな魅力です。

表のデータが追加されて複雑になっていた時ほど便利に使えます。ぜひお試しください。