業務効率を大幅アップ! Excel XLOOKUP 関数の使い方早わかり解説

「検索値」とは、検索対象となる値のことで、何を探すのかを指します。たとえば、商品コードや社員番号など、データを探すためのキーとなる値を指定します。
例では、A3 のセル、つまり［商品コード］10001 が「検索値」にあたります。

なお、XLOOKUP 関数の検索値では、全角文字、半角文字、英字の大文字と小文字は区別されず同じ値として認識されます。

また、「検索値」ではセルを指定するだけではなく、数字や文字列を直接指定することも可能です。文字列を指定するときには式の中で文字列を「“」  (ダブル クォーテーション) で、「”10001“」のように囲みます。

「検索範囲」は、検索値を探す範囲のことです。
例では、商品マスタの［商品コード］の列が「検索範囲」にあたります。

「戻り範囲」は、取得したい値の範囲を指定します。
例では、商品マスタの［商品名］と［単価］の列を指しています。複数の行列が「戻り範囲」に指定されている場合、複数の値を返します。
「検索値」と「検索範囲」が一致した場合「戻り範囲」の値がセルに表示されます。

「検索値」と「検索範囲」が一致しない場合に表示する値を指定します。
例では「該当なし」と表示されるように指定しています。
ただしこの表示は、次に指定する因数である「一致モード」の値に依存します。

「一致モード」では、一致の種類を「0」「-1」「1」「2」のいずれかの値で指定します。
例では、［商品コード］10001 とまったく同じ値を検索し、見つからない場合は「該当なし」と表示したいので、「0」  (完全一致) を入力しています。

【「0」「-1」「1」「2」の意味】

• 「0」＝完全一致。 見つからない場合は、「4．見つからない場合」の値 になります。これが既定の設定となるため、省略した場合は「0」を入力したことになります。
• 「-1」＝完全一致。 見つからない場合は、次に見つかった小さな値になります。
• 「1」＝完全一致。 見つからない場合は、次に見つかった大きな値になります。
• 「2」＝「*」  (アスタリスク)、「?」  (クエスチョン) および「~」  (チルダ) をワイルド カードとして使用できる一致方式です。複雑な条件で対象を探すことができます。

「検索モード」では、検索の種類を「1」「-1」「2」「-2」のいずれかの値で指定します。
例では、先頭から順に検索したいため、「1」を入力しています。

【「-1」「1」「2」「-2」の意味】

• 「1」＝先頭の項目から検索を実行します。 これが既定の設定となるため、省略した場合は「1」を入力したことになります。
• 「-1」＝末尾の項目から逆方向に検索を実行します。
• 「2」＝先頭の項目から高速な検索を実行します。昇順で並べられていない場合、エラーとなり無効な結果となります。
• 「-2」＝末尾の項目から高速な検索を実行します。 降順で並べられていない場合、エラーとなり無効な結果となります。

「2」、「-2」の高速検索は正式にはバイナリ サーチ、二分探索といい、膨大なデータから探し出すのが得意な方式です。

この方式では、データが昇順に並んでいる必要があり、「検索値」と「検索範囲」の真ん中に位置するデータを比較して、小さければ前半、大きければ後半に検索値と一致するデータがあると仮定し、検索対象を絞り込みます。

これを「検索値」と一致するデータが見つかるまで繰り返すため、降順、昇順に整列されている必要があるのです。

表を用意する際の検索したい値は、行方向でも列方向でも構いませんが、「検索範囲」は、基本的には昇順または降順で並べ替えを行っておくことが望ましいです。

昇順、降順のどちらにすればよいかは、「検索モード」によります。昇順にしておくと、既定の値となるため、「検索モード」の指定を省略できます。

ただし、データ量が多くもともと降順になっている場合などは、「検索値」と一致するデータを取り出しやすくするため降順にするとよいでしょう。

XLOOKUP 関数は、ここまで紹介した以外にもさまざまな応用が可能です。ここではいくつかの応用例を紹介します。

複数の行と列を含む「戻り範囲」を指定することで、1 つの式で複数の値を抽出することが可能です。先ほどの例を使用しながら「戻り範囲」に［商品名］と［単価］の 2 つの列を指定します。

「検索範囲」が［商品コード］10001 の行の「戻り範囲」には［商品名］ABCねじと［単価］1000 があるため、2 つの値が取得されました。

絶対参照とは、セルをコピーしても参照する場所が変わらないようにする参照方法のことです。

この絶対参照を使い、式のコピー アンド ペーストで値を抽出してみましょう。

例として、前の項目で報告書の商品名を求めた式を利用して、［商品コード］20002 に対応する売上数、売上金額を抽出します。

［売上数］320、［売上金額］576000 が表示されました。

今回ご紹介したように、XLOOKUP 関数では、行や列からデータを取得し抽出することができます。XLOOKUP 関数を使いこなして、Excel を活用していきましょう。