メインコンテンツへスキップ

​
XLOOKUP 関数のご紹介

​
より柔軟な検索関数 - XLOOKUP

スプレッドシートでは、XLOOKUP 関数を使用して検索とマッチングを実現できます。従来の検索関数（LOOKUP、VLOOKUP、HLOOKUP）と比較して、XLOOKUP 関数はより柔軟で、検索速度も高速です。

​
XLOOKUP の概要

XLOOKUP 関数は「検索範囲」内で「目標値」を検索し、検索成功時には「検索範囲」内の検索結果の位置情報に基づいて、「結果範囲」の対応する位置のデータを結果として返します。 XLOOKUP には 6 つの引数があり、順に以下のとおりです。
  • 目標値（必須）
  • 検索範囲（必須）
  • 結果範囲（必須）
  • 目標値が見つからない場合に返す結果
  • マッチングタイプ
  • 検索方法
具体的な引数の説明は、スプレッドシートのツールバー 数式 —> すべての関数 で「XLOOKUP」関数を検索してご確認ください。

​
「検索範囲」と「結果範囲」は任意の位置に配置可能

従来の検索関数では「結果範囲」が「検索範囲」の右側または下方にある必要があり、特定のシーンで不便が生じることがありました。一方、XLOOKUP 関数では「検索範囲」と「結果範囲」が独立した引数（第 2、第 3 引数）となっており、ソースデータの構造制約から解放されています。 図 1 範囲 B2:B5 内で「みかん」を検索し、検索された位置は 2 番目です。 そのため、結果は範囲 A2:A5 の 2 番目のデータ「dd231」となります。 XLOOKUP 関数には「検索方向」という概念がなく、「検索範囲」は単一の行または列である必要があります。マッチした行/列の番号に応じて、結果範囲の対応する番号の値を取得します。例： 図 2 範囲 A1:D1 内で「販売単価」を検索し、検索された位置は 3 番目です。 そのため、結果は範囲 A3:D3 の 3 番目のデータ 2.3 となります。

​
マッチング値が見つからない場合のデフォルト結果

目標値が見つからない場合、従来の検索関数は #N/A エラーを返します。一方、XLOOKUP ではデフォルト値（第 4 引数）を指定でき、目標値が見つからなかった場合に、その値が結果として返されます。 図 3 範囲 B2:B5 内で「チェリー」が見つからなかったため、関数は第 4 引数を結果として返します。

​
より詳細な検索方法

XLOOKUP 関数の第 5、第 6 引数はそれぞれ「マッチングタイプ」と「検索方法」であり、これらを組み合わせることでより詳細な検索方法を指定できます。 例えば、目標売上に最も近い商品を検索する場合： 図 4 関数では第 5 引数に -1 を設定しており、100 または 100 より小さい最大値を検索することを示します。 範囲 D2:D5 内で要件を満たすデータ 77 が見つかったため、対応する位置の結果「スイカ」を返します。

​
応用編！XLOOKUP のネスト利用

XLOOKUP の「結果範囲」は複数行・複数列でも構いません。検索結果に基づき、XLOOKUP は対応する行/列の複数の結果を返します。この特性を活用して、XLOOKUP 関数を他の関数と組み合わせることで、より多くのシーンに対応できます。例： 図 5 図 5 では、「Kaola」が「検索範囲」の 3 行目に位置するため、XLOOKUP 関数は対応して「結果範囲」の 3 行目のデータ「30, 40, 50, 60」を取得します。続いて、SUM 関数が XLOOKUP 関数の結果を合計し、最終結果「180」を得ます。 図 6 図 6 では、内側の XLOOKUP 関数が結果「30, 40, 50, 60」を返し、これが「結果範囲」として外側の XLOOKUP 関数に渡されます。続いて、外側の XLOOKUP 関数が範囲 B2:E2 内で「第三四半期」を検索し、見つかった位置は 3 番目であるため、「30, 40, 50, 60」から結果「50」を取得します。

​
関連情報

同様に、XMATCH もより柔軟な特性とより高速な検索速度を備えており、従来の MATCH 関数の代替として使用できます。スプレッドシートのツールバー-数式-すべての関数で「XMATCH」関数を検索すると、詳細な引数の説明をご確認いただけます。