リモートワーク ホームリモートワーク コラム > 業務効率を大幅アップ! Excel XLOOKUP 関数の使い方早わかり解説

2021 年 8 月 26 日

XLOOKUP は、Microsoft 365 ユーザー向けにリリースされた Microsoft Excel の新しい関数で、ビジネスにおける利用頻度が高い関数です。データの検索速度が速いだけでなく、データ分析も簡単に行うことができます。この記事では、XLOOKUP 関数の概要や便利な使い方などを解説します。


Excel のセルに入力する XLOOKUP 関数

1. XLOOKUP とは何か

XLOOKUP は「エックス ルックアップ」と読みます。特定の値で表を検索し、一致する値に対応するデータを返すので、表の中から必要な情報を抽出することができる関数です。具体的には、商品コードから商品情報を抽出したり、顧客コードから氏名や住所などの顧客情報を取り出したりできます。

元となるデータから値を取得することで、数字や文字の間違いや表記のブレを防ぐことも可能です。

以下の例では、XLOOKUP 関数を使っておすすめ商品の商品名を商品マスタから取得しています。[商品コード]10001 を商品マスタで検索して、対応する商品マスタの行から[商品名]ABCねじを取得し、おすすめ商品一覧の商品名のセルに表示しています。

XLOOKUP 関数は、表の範囲指定の仕方によって複数のデータを抽出することもできます。例では、[商品名]の列に XLOOKUP を使用することで、[商品名]ABCねじと同時に[単価]1000 も抽出しています。

  

[商品名]の列に XLOOKUP を使用することで、[商品名]ABCねじと同時に[単価]1000 も抽出

また以下の例では、XLOOKUP 関数の中でさらに XLOOKUP 関数を利用し、[商品コード]20002 の行と[商品名]の列が交差するセルである C9 のデータ  (DEFプレート) を、報告書の[商品名]の列に抽出しています。この例については、後半の「便利な使い方」で詳しく解説します。

  

[商品コード]20002 の行と[商品名]の列が交差するセルである C9 のデータ  (DEFプレート) を、報告書の[商品名]の列に抽出

2. XLOOKUP 関数の式と引数

XLOOKUP 関数は、「検索値」「検索範囲」「戻り範囲」「見つからない場合」「一致モード」「検索モード」の 6 つの値を入れて使用します。ただし、後半の 3 つは省略することも可能です。ちなみに、関数を使うために入力するこれらの値を「引数」と言います。

それでは XLOOKUP 関数を構成する 6 つの引数を、次の例を見ながら確認していきましょう。

  

XLOOKUP 関数を構成する 6 つの引数

1. 検索値 : 何を探すのか

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

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

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

2. 検索範囲 : どこを探すのか

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

3. 戻り範囲 : どの値を取得するのか

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

4. 見つからない場合 : もし値が見つからなかったら

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

5. 一致モード : 一致の種類と返す値を指定

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

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

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

6. 検索モード : 検索の種類を指定

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

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

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

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

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

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

3. XLOOKUP 関数の入力前の準備

次に、XLOOKUP の実際の利用手順を解説します。今回は XLOOKUP 関数を使用しておすすめ商品の表を作成する場合を例にします。セルに商品名を直接入力せずに、商品コードを検索し、商品マスタの表から商品名を取得します。

まずは商品マスタの表を用意します。この際、引数の「検索範囲」及び「戻り範囲」に該当する表を作成しておく必要があります。

例では[商品コード]を「検索範囲」とし、列[商品名][単価]を「戻り範囲」とする表を用います。

  

[商品コード]を「検索範囲」とし、列[商品名][単価]を「戻り範囲」とする表

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

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

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



4. XLOOKUP 関数の入力

次に、XLOOKUP 関数を入力する手順を解説します。関数の入力方法には、数式バーの関数ボタン (fx) を利用する方法と、セルまたは数式バーに「=XLOOKUP(」を入力する方法があります。

先ほど作成した表を用い、商品マスタのデータを元に [商品コード] 10001 の商品名を取得してみます。必須の引数である「検索値」「検索範囲」「戻り範囲」の 3 つを指定します。

4-1. 関数ボタン (fx) で入力する方法

関数ボタンを利用する方法は入力項目が分かりやすいため、初めて XLOOKUP 関数を利用する場合におすすめです。

1. XLOOKUP 関数を使用するセルを選択する。
(例では[商品コード] 10001 に対応する商品名を取得したいので、商品名のセルを選択)

  

XLOOKUP 関数を使用するセルを選択

2. 関数ボタン (fx)をクリックする。

  

関数ボタン (fx) をクリック

3. 関数ウィザードが表示されるので、「XLOOKUP」と入力し「検索開始」ボタンをクリックする。

  

関数ウィザードで「XLOOKUP」と入力し「検索開始」ボタンをクリック

4. 「OK」ボタンをクリックする。

  

「OK」ボタンをクリック

5. セルの中に「=XLOOKUP()」が入力され、関数の引数画面が表示される。

  

セルの中に「=XLOOKUP()」が入力され、関数の引数画面が表示される

6. カーソルが「検索値」にあるのを確認し、「検索値」を選択する。
(例では[商品コード] 10001 に該当するセル A3 を選択)

  

カーソルが「検索値」にあるのを確認し、「検索値」を選択

7. カーソルを「検索範囲」に移動する。

  

カーソルを「検索範囲」に移動

8. 「検索範囲」を選択する。
(例では商品マスタの列[商品コード]を選択)

  

「検索範囲」を選択

9. カーソルを「戻り範囲」に移動する。

  

カーソルを「戻り範囲」に移動

10. 「戻り範囲」を選択する。
(例では商品マスタの列[商品名]を選択)

  

「戻り範囲」を選択

11. 「OK」ボタンをクリックする。

  

「OK」ボタンをクリック

12. B3 のセルに[商品名]ABCねじが表示される。

  

B3 のセルに[商品名]ABCねじが表示される

関数ボタンから XLOOKUP 関数を使って、目的の値を取得することができました。

4-2. セルに「=XLOOKUP(」を入力する方法

セルまたは数式バーに直接入力する場合は、半角で入力するとサジェスト (入力補助) が表示されるため、何を入力すればよいのかすぐわかり便利です。

この方法で XLOOKUP 関数を使用する場合は半角英数字モードになっている必要があるため、入力時に日本語モードになっていないかを確認しましょう。

1. XLOOKUP 関数を使用するセルに「=XLOOKUP (」を入力する。

  

XLOOKUP関数を使用するセルに「=XLOOKUP (」を入力

このとき、関数を入力したいセルを選択し、数式バーに「=XLOOKUP(」と入力することも可能です。

  

数式バーに「=XLOOKUP(」と入力することも可能

2. 「検索値」を選択する。
(例では[商品コード] 10001 に該当するセル A3 を選択)

  

「検索値」を選択

3. カンマ (,) を入力し、「検索範囲」を選択する。
(例では商品マスタの列[商品コード]を選択)

  

カンマ (,) を入力し、「検索範囲」を選択

4. カンマ (,) を入力し「戻り範囲」を入力する。
(例では商品マスタの列[商品名]を選択)

  

カンマ (,) を入力し「戻り範囲」を入力

5. Enter キーで確定すると、[商品コード] 10001 の行の商品名のセルに[商品名] ABCねじが表示される。

  

[商品コード] 10001 の行の商品名のセルに[商品名] ABCねじが表示される

5. 便利な使い方

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

5-1. 一度に複数の値を抽出する

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

1. 式を入力したセルを選択し「戻り範囲」を選択する。
(例では商品マスタの列[商品名][単価]を選択)

  

式を入力したセルを選択し「戻り範囲」を選択

2. Enter キーで確定すると、10001 の行の商品名のセルに[商品名]ABCねじ、単価のセルに[単価]1000 が表示される。

  

10001 の行の商品名のセルに[商品名]ABCねじ、単価のセルに[単価]1000 が表示される

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

5-2. 表の行列両方を一挙に検索する

ここでは例として、売上表の中で、商品コードの行と商品名の列が交差する値を報告書に抽出します。

  

売上表の中で、商品コードの行と商品名の列が交差する値を報告書に抽出

1. 1 つの表 (例では売上表) と新たに作る表 (例では報告書) を用意する。

  

1つの表 (例では売上表) と新たに作る表 (例では報告書) を用意

2. XLOOKUP 関数を使用するセルに「=XLOOKUP(」を入力する。

  

XLOOKUP 関数を使用するセルに「=XLOOKUP(」を入力

3. [商品コード]20002 の商品名を取得するため、「検索値」と「検索範囲」を入力する。
(例では「検索値」=[商品コード]20002、「検索範囲」=売上表の商品コードの列)

  

[商品コード]20002 の商品名を取得するため、「検索値」と「検索範囲」を入力

4. 「戻り範囲」にあたる部分に XLOOKUP 関数を入力する。
(「検索値」=報告書の[商品名]と書かれたセル、「検索範囲」=売上表の見出し行、「戻り範囲」=売上表の見出しを除く全体)

  

「戻り範囲」にあたる部分に XLOOKUP 関数を入力

5. Enter キーで確定すると、[商品コード]20002 と商品名が交差した[商品名]DEFプレートが表示される。

  

[商品コード]20002 と商品名が交差した[商品名]DEFプレートが表示される

5-3. 戻り範囲が横並びでない値を抽出する

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

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

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

1. XLOOKUP 関数を入力したセルを選択する。

  

XLOOKUP 関数を入力したセルを選択

2. 式の値を絶対参照及び絶対行参照に変更する。
(絶対参照=[商品コード]20002 の「検索値」、売上表の[商品コード]の列、売上表の見出し行の「検索範囲」、売上表のデータ部分の「戻り範囲」の値、絶対行参照=報告書の[商品名]の「検索値」)

※絶対参照は、範囲を指定したときに「F4」キーを押下するか、範囲の列名と行名の頭に「$」を入力します。

  

式の値を絶対参照及び絶対行参照に変更

3. 報告書の[商品名]の列に入力した XLOOKUP 関数を[売上数][売上金額]の列にコピー アンド ペーストする。

  

報告書の[商品名]の列に入力した XLOOKUP 関数を[売上数][売上金額]の列にコピー アンド ペースト

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

6. まとめ

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

【参考】関連する関数

リモートワーク・ハイブリッドワークに適した環境設置のために

リモートワーク・テレワーク・在宅勤務環境を安全・快適に実現するためには、「セキュリティの確保」「Web 会議のためのデバイス選択」「グループワークのためのアプリケーション」など検討する課題も多く、またこれらを潤沢な資金で準備するのではなくコスト削減につなげることが大切です。

これらの達成のための Microsoft 365、Excel の使い方や、リモートワーク・ハイブリッドワーク環境を充実させるために以下の記事が参考になります。

お電話で購入相談

受付時間: 9:00 - 17:30 (土日祝日、弊社指定休業日を除く)

0120-167-400

Web フォームで購入相談

  

フォームを開く

本情報の内容 (添付文書、リンク先などを含む) は、作成日時点でのものであり、予告なく変更される場合があります。