新しいXLOOKUP関数とは？VLOOKUP関数との違いもあわせてご紹介

Office 365に新関数「XLOOKUP」が登場したのはご存知でしょうか。

営業事務やデータ分析のような職種で、Excelを仕事で使っている方なら、VLOOKUP関数はご存じですよね。ほとんどの人が「名前は知っている」と答えるであろうとてもメジャーな関数です。

この名前にあるとおり、VLOOKUP関数は表を縦方向（Vertical）に検索し、特定のデータに対応する値を取り出します。これと同様に、表を横方向（Horizontal）に検索するHLOOKUP関数もよく知られています。

そのような中、VLOOKUP関数とHLOOKUP関数の機能を兼ね備えた関数がOffice 365ユーザー向けにリリースされたのが、XLOOKUP関数です。

今回の記事では、VLOOKUP関数の復習をしながら、このXLOOKUP関数の使い方をお伝えしていきます。

ぜひマスターして、業務効率をあげていきましょう。

XLOOKUP関数とは？

XLOOKUP関数とは、表や範囲から行ごとに情報を検索するVLOOKUP関数の後継的な関数です。

たとえば、パソコン部品の価格を部品番号で検索するか、営業担当IDに基づいて営業担当名を検索します。 

このときにXLOOKUP を使用すると、検索語句の1つの列を検索し、戻るべき列がどの側にあるかに関係なく、別の列の同じ行の結果を返すことができます。

XLOOKUP関数の使い方

まずはXLOOKUP関数の構文を確認しましょう。

引数が6つもあるので一見複雑そうに見えますが、必須で入力しなければならない引数は最初の3つのみです。

そのため、4つめ以降の引数は省略可能です。

なお、それぞれの引数には以下のような意味があります。

VLOOKUP関数では［列番号］となっている第3引数が、XLOOKUP関数では［戻り範囲］となっています。

左から順に列番号を数える必要がなく、値を取り出すセル範囲を直接指定すればいいので、XLOOKUP関数のほうがVLOOKUP関数よりも使い勝手がいいといえます。

それでは実際の例題でみていきましょう。

XLOOKUP関数を使用して範囲内の国名を検索して、その地域コードを返したい場合、これには、検索値 (セル F2)、検査範囲 (範囲 B2: B11)、および戻り配列 (範囲 D2: D11) の引数が含まれます。

 XLOOKUP関数は既定では完全一致を生成するため、一致モード引数は含まれません。

XLOOKUP関数は検索範囲と戻り配列を使用するのに対し、VLOOKUP関数は列インデックス番号に続く単一の表の配列を使用します。

この場合、同等のVLOOKUP関数数式は次のようになりますので、試してみてください。

続いては国番号から国を抽出したり、国番号から空港で使われるイニシャルなどの情報を取り出したりできます。

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

下図ではXLOOKUP関数を使っておすすめ海外旅行先の国名を国名マスタから取得しています。［国番号］86を商品マスタで検索して、対応する商品マスタの行から［国名］中国を取得し、おすすめ海外旅行一覧の国名のセルに表示しています。

XLOOKUP関数は、表の範囲指定の仕方によって複数のデータを抽出することもできます。

例題では、［国名］の列に XLOOKUP関数を使用することで、［国名］中国と同時に［イニシャル］CNも抽出することができます。

VLOOKUP関数とは？

VOOKUP関数とは、「Vertical」 (垂直) を意味するVと「LOOKUP」 の略で、表の縦方向にデータを検索し一致した値と同じ行にあるデータを返す関数のことです。

特定の値で表を検索し、表の中の必要な情報を抽出することができます。また、元となるデータから値を取得することで、数字や文字の間違いや表記のブレを防ぐこともできます。

VOOKUP関数の活用例としては、商品コードから商品情報を抽出する、顧客コードから氏名や住所などの顧客情報を取り出すなどがあります。

さまざまな表から必要な情報を抽出することが主な用途ですが、応用としてデータの抜け漏れチェックや表の結合などにも利用できます。

VLOOKUP関数の使い方

VLOOKUP関数は、「検索値、範囲、列番号、検索方法」の 4つの値を入れて使用します。関数を使うために入れるこれらの値のことを引数といいます。

VLOOKUP関数を構成する4つの引数には以下のような意味があります。

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

検索値とは、検索対象となる値のことで、何を探すのかを示します。商品コードや顧客コードなど、マスタからデータを探す際のキーとなる値を指定します。

検索値は全角文字と半角文字は区別されますが、英字の大文字と小文字は区別されず同じ値として認識されるため、検索値の指定の際には注意が必要です。

セルを指定するだけではなく、数字や文字列を直接指定することもできます。文字列を指定するときには式の中で文字列を「“」 (ダブルクォーテーション) で、「”10001“」のように囲む必要があります。

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

範囲では、検索値を探す範囲を指定します。例では、商品マスタの表の赤い枠で囲まれている範囲を指します。左端の列に検索値が含まれるようにすること、返す値の列が含まれるよう範囲を指定することの2点に注意しましょう。

3.列番号 : どの列の値を取得するのか

上述の範囲の中で、取得したい値が左から数えて何列目かを指定します。一番左の列を取得する場合は「1す」、検索値の列の隣の値を取得する場合は「2」、その隣の列の値を取得する場合は「3」といった具合で入力します。

4.検索方法 : まったく同じ値か

「TRUE」または「FALSE」の文字を入力します。小文字で入力しても自動的に大文字に変換されます。「TRUE」と入力すると、範囲の列の中で検索値に一番近い値が表示されます。重さの幅に応じて金額が決まっているような、範囲に応じた結果を表示する際に利用します。「FALSE」と入力すると、検索値に対応する列の値が表示されます。VLOOKUP 関数を利用する場合、一般的には「FALSE」を選択すると考えて問題ないでしょう。

出典：Microsoftサポート｜VLOOKUP 関数の式と引数

XLOOKUP関数にしかない追加機能

XLOOKUP関数でとても便利になった追加機能があります。

それは、結果が見つからない場合の表記が簡単になったという点です。

XLOOKUP関数の引数として「見つからない場合」がありますが、これはVLOOKUP関数やHLOOKUP関数にはなかった機能です。

ほかにも、検索値が左端になくてもよい、という大きな特徴があります。

今までのVLOOKUP関数を使う場合には、検索値が選択範囲の一番左にないといけませんでした。

さらに、VLOOKUP関数は縦・横どちらでも対応することが可能です。

これまでは垂直方向の検索ではVLOOKUP関数、水平方向ではHLOOKUP関数と、いわゆるVerticalとHorizonで使い分ける必要がありました。しかしながら、XLOOKUP関数では一つの関数で両方に対応できます。

検索範囲と戻り配列の指定が縦方向なのか横方向なのかで使い分けができますので、画期的なアップデートポイントといえます。

そして、XLOOKUP関数が「スピル」に対応していることです。

スピルとは、数式の計算結果として複数の値が求められる場合、隣接するセルに複数の値を配置するExcelの機能のことを指します。聞き馴染みがあまりない言葉かもしれませんが、この機能が非常に便利なのです。

スピル（spil）は「こぼれる」「あふれる」という意味です。

従来では、配列数式（※）を利用した場合を除いて、数式を入れた一つのセルに対して一つの結果が表示されるのが常識でした。

ですがスピルは数式を入力したセルだけでなく、隣接するセルにも結果が表示されます。このようにこぼれる、あふれるような現象にちなんでスピルと呼ばれています。

※これまでのExcelでは、複数のセルに値を表示したい場合、結果を表示したい全てのセルを選択し、Ctrl+Shift+Enterを押すことで数式が一括入力されていました。これを「配列数式」と言います。これに対してスピルは、一つのセルに数式を入力すれば隣接するセルにも自動的に結果が表示されるため「動的配列数式」と呼ばれています。

参考：スピルとは？Excel（エクセル）の常識が変わる革新的な機能！

エラーが出たときの対処法

最後にXLOOKUP関数でエラーが出たときのケースをみていきましょう。

よく見かける#N/Aはnot applicable（該当なし）、not available（利用できない）の意味です。該当するデータがないときに出るエラーです。

続いては、#NAME？とこちらに聞いてくるようなエラーですが、「関数名は間違ってない？」というニュアンスのものです。

このエラーが表示されたときには、まずXLOOKUP関数の構文を確認しましょう。

全角半角などのミスはよくある話です。

また、#VALUE!もよく目にするエラーですよね。

これは、表示する範囲や範囲の行数が違っていると出てくるエラーです。

F2を押して指定範囲や桁数が合っているか、今一度確認してみましょう。

そして最後は#SPILL!です。

このエラーは、前述したスピルに関するものです。

たとえば、D列まで結果を表示しようとしているのに、セルD2に何か入っているとエラーがでます。

その際には、該当セルであるD2をクリアに（delete）すれば正しく表示されます。

あわせて読みたい｜HLOOKUP関数とは？

最後にVLOOKUP関数の仲間であるHLOOKUP 関数の書式および使用法について説明します。

HLOOKUP関数とは、テーブルの上端行または配列内の特定の値を検索し、テーブルまたは配列内の指定した行から同じ列の値を返します。 

HLOOKUP関数は、比較する値がデータ テーブルの上端行にあり、指定した行数分だけ下を参照する場合に使用します。 比較する値が検索データの左側の列にある場合は、VLOOKUP 関数を使用してください。

また、HLOOKUP 関数の 「H」は、横方向 (horizontal) を意味します。

構文は次の通りです。

HLOOKUP関数の構文のルールは以下の二つで、両方とも必須入力項目です。

1.検索値    

必ず指定します。 テーブルの上端行で検索する値を指定します。 検索値には、値、参照、または文字列を指定します。

2.範囲    

必ず指定します。 データを検索する情報のテーブルです。 セル範囲への参照またはセル範囲名を使用します。

参考：Microsoftサポート｜HLOOKUP 関数

まとめ

今回の記事では、新たに追加されたXLOOKUP関数についてご紹介してきました。

事務の仕事でExcelを使っていると、一度はVLOOKUP関数を使用したことがあるかと思います。VLOOKUP関数は、縦方向（Vertical）に表を検索して、特定のデータに対応する値を取り出す関数です。

また、横方向（Horizontal）に検索するHLOOKUP関数もあります。それらの機能を網羅し、縦横方向のデータが検索可能になったXLOOKUP関数をマスターすることで、範囲や桁数の処理でエラーを起こすことが格段に減ります。

ぜひ本記事を参考に、XLOOKUP関数を習得してくださいね。