【エクセル時短】は「少しでも早く仕事を終わらせたい!」というビジネスパーソンのみなさんに、Excelの作業効率をアップするワザをお届けする連載です。
注目・最新の記事一覧

Office 365に新関数「XLOOKUP」が登場

Excelを仕事で使っているなら、VLOOKUP(ブイ・ルックアップ)関数はご存じですよね。ほとんどの人が「名前くらいは知っている」と答えるであろう、とてもメジャーな関数です。

VLOOKUP関数は表を縦方向(Vertical)に検索し、特定のデータに対応する値を取り出します。これと同様に、表を横方向(Horizontal)に検索するHLOOKUP(エイチ・ルックアップ)関数もよく知られています。

そして2020年1月30日、VLOOKUP関数とHLOOKUP関数の機能を兼ね備えつつ、大幅に機能が強化された新関数が、Office 365ユーザー向けにリリースされました。その名も「XLOOKUP(エックス・ルックアップ)関数」です。

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

数式バーに「=X」と入力し、一覧に「XLOOKUP」と表示されれば、利用可能な状態になっています。

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

Office 365ユーザーなのに表示されない場合は、[ファイル]→[アカウント]の順にクリックして[アカウント]画面を表示し、[更新オプション]から[今すぐ更新]を選択しましょう。ただし、企業では社内ポリシーにより、最新のアップデートを配信していないこともあります。

XLOOKUP関数は、VLOOKUP関数に代わる新関数として今後大きな注目を集めるでしょう。【エクセル時短】第132回では、以下の3つのポイントに注目してXLOOKUP関数の使い方を紹介します。

XLOOKUP関数の3つのポイント

  1. 引数の指定方法が分かりやすい
  2. 検索範囲が左端でなくてもOK
  3. 複数のセルに計算結果を表示できる

引数の指定方法が分かりやすい

よくある利用例として、表内の「顧客コード」を検索し、対応する「氏名」を取り出すケースを考えてみましょう。

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

セルB2にXLOOKUP関数が入力されています。セルA2に入力済みの顧客コード「T007」に対応する氏名を表(顧客リスト)から取り出しています。

ここで、XLOOKUP関数の構文を確認します。


=XLOOKUP検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード


引数が6つもあり、一見難しそうに見えますが、必須の引数は最初の3つのみ。4つめ以降の引数は省略可能です。それぞれの引数には以下の意味があります。

  1. 検索値] ... どのデータで
  2. 検索範囲] ... どこを検索して
  3. 戻り範囲] ... どの範囲の値を取り出すか
  4. 見つからない場合] ... 検索値が見つからない場合、何を表示するか
  5. 一致モード] ... 完全一致か、近似値も検索するか(初期値:完全一致)
  6. 検索モード] ... どのような順序で検索するか(初期値:先頭から末尾)

先ほどの例でも、指定している引数は3つのみです。[検索値]には探したい顧客コードが入力されているセルA2、[検索範囲]には顧客コードの入力されているセル範囲(セルA6〜A105)、[戻り範囲]には氏名の入力されているセル範囲(セルB6〜B105)を指定しています。

ここで1つめのポイントです。VLOOKUP関数では[列番号]となっている第3引数が、XLOOKUP関数では[戻り範囲]となっています。左から順に列番号を数える必要がなく、値を取り出すセル範囲を直接指定すればいいので、XLOOKUP関数のほうが分かりやすいのではないでしょうか。

ただし、注意点もあります。XLOOKUP関数では、[検索範囲]と[戻り範囲]で指定するセル範囲は、同じ高さにしてください。例えば「=XLOOKUP(A2,A6:A105,B6:B1000)」のような指定はNGです。

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

検索範囲]と[戻り範囲]で、指定しているセル範囲の高さが異なるとエラーになります。

では、XLOOKUP関数の基本的な入力の流れを以下の手順で見ていきましょう。

1XLOOKUP関数を入力する

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

XLOOKUP関数で値を求めたいセル(ここではセルB2)を選択し、数式バーに「=XLOOKUP」と入力します。

2第1引数[検索値]を入力する

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「(」を入力し、第1引数[検索値]となるセル(ここではセルA2)を指定します。指定したいセルをクリックしてもOKです。

3第2引数[検索範囲]を入力する

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「,」を入力し、第2引数[検索範囲]となるセル(ここではセルA6~A105)を指定します。指定したいセル範囲をドラッグして選択してもOKです。

4第3引数[戻り範囲]を入力する

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「,」を入力し、第3引数[戻り範囲]となるセル(ここではセルB6~B105)を指定します。第2引数[検索範囲]とセル範囲の高さをそろえてください。

5数式の入力を完了する

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「)」を入力して[Enter]キーを押し、XLOOKUP関数の数式の入力を完了します。結果、セルB2に顧客コード「T007」に対応する氏名が表示されました。

完成した数式を書き出すと、以下のようになります。

        =XLOOKUP(A2,A6:A105,B6:B105)

検索範囲が左端でなくてもOK

VLOOKUP関数では、検索値の検索対象が、検索範囲の左端の列(左から1列目)となっていることが必須でした。

例えば、氏名を検索値としたいときは、検索範囲の左端の列が「氏名」となるように、あらかじめ整えておく必要があったのです。

このVLOOKUP関数の最大の弱点が、XLOOKUP関数では解決されました! それが2つめのポイントです。

以下の例を見てください。先ほどの例とは逆に、「氏名」から「顧客コード」を検索しています。表(顧客リスト)の左端の列(A列)は「顧客コード」ですが、問題なく結果を取り出せています。

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「氏名」を検索値として「顧客コード」を検索しています。

数式の内容は以下のようになります。

        =XLOOKUP(A2,B6:B105,A6:A105)

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

第1引数[検索値]は氏名(セルA2)です。第2引数[検索範囲]はセルB6~B105、第3引数[戻り範囲]はセルA6~A105です。

複数のセルに計算結果を表示できる

3つめのポイントは、XLOOKUP関数が「スピル」に対応していることです。

スピルとは、数式の計算結果として複数の値が求められる場合、隣接するセルに複数の値を配置するExcelの機能のことを指します。この機能が非常に便利なのです。

例えば、「顧客コード」から「氏名」だけでなく、「居住地」と「年齢」まで取り出したいケースを考えてみましょう。

以下の例ではセルB2に以下の数式が入力されており、「顧客コード」から「氏名」を取り出しています。

        =XLOOKUP(A2,A6:A105,B6:B105)

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

顧客コードを検索して「氏名」を取り出しています。

この「氏名」に加えて「居住地」と「年齢」も取り出したいとき、VLOOKUP関数では数式をコピーし、引数を変更していくことになります。しかし、XLOOKUP関数では数式をコピーする必要はありません。

今回の例でいえば、「顧客コード」を検索した結果、複数の値が求められるように第3引数[戻り範囲]を指定すると、結果が一気に表示されるようになります。

以下は、セルB2の数式の第3引数[戻り範囲]を、以下のように「居住地」と「年齢」のセル範囲も含むように書き換えたところです。

        =XLOOKUP(A2,A6:A105,B6:D105)

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

セルB2の数式を「居住地」と「年齢」も含まれるように「=XLOOKUP(A2,A6:A105,B6:D105)」と書き換えると......

【エクセル時短】もう使った? VLOOKUPの後継として注目される新関数「XLOOKUP」の注目点3つ

「居住地」と「年齢」も一気に取り出せました。

以上、注目の新関数「XLOOKUP関数」の使い方を解説しました。今後も活用シーンは増えていくと予想されるので、Office 365ユーザーのみなさんは、今のうちに試して使い方に慣れておくことをオススメします。

なお、ほかのバージョンのExcelでXLOOKUP関数の入力されたファイルを開くと、XLOOKUP関数を含む数式は「=_xlfn.XLOOKUP(A2,A6:A105,B6:B105)」のように変換され、結果のみが表示されます。

関連まとめ記事