Microsoft Excel | XLOOKUP関数の使い方
wp030915この記事では、Microsoft ExcelのXLOOKUP関数の使い方を解説します。
XLOOKUP関数は、Microsoft 365で使用できる、比較的新しいExcel関数です。
従来あったVLOOKUP関数やHLOOKUP関数と似た機能をもつ関数ですが、両者の機能を統合した上で、さらに使い勝手が良い関数になっています。
以下のような方を対象として、XLOOKUP関数の基本的な使い方と、具体的な使用例を順を追って解説していきます。
- XLOOKUP関数という名前は聞いたことがあるものの、どのような関数かはよくわからない。
- VLOOKUP関数やHLOOKUP関数を使っていて、不便に感じている。
基本的な使い方
まずは、XLOOKUP関数を使ったシンプルな例を見てみましょう。
1行目から5行目までに、商品コードごとの商品名と価格を表形式でまとめています。
そして、A9セルに入力された商品コードを元に価格を取得するXLOOKUP関数を、B9セルに入力しています。
XLOOKUP関数の引数は、以下のように指定しています。
- 第1引数（検索値）：A9
- 第2引数（検索範囲）：A2:A5
- 第3引数（戻り範囲）：C2:C5
第1引数（検索値）に入力された値が、第2引数（検索範囲）の中の何番目にあるのかを検索し、該当する項目を第3引数（戻り範囲）の中から返しています。
上記の例でいうと、A9セルに入力された「A003」がA2:A5の中の3番目にあるため、C2:C5の3番目にある「200」という結果が返されています。
XLOOKUP関数には第6引数までありますが、第4引数以降は任意のため、以上が最も基本的な使い方となります。
XLOOKUP関数の引数
XLOOKUP関数には全部で6つの引数があります。
先の例で触れたものも含めて全部並べると、以下のようになります。
- 第1引数：検索値
- 第2引数：検索範囲
- 第3引数：戻り範囲
- 第4引数：見つからない場合
- 第5引数：一致モード
- 第6引数：検索モード
第1引数から第3引数までは必ず指定しなければいけません。
第4引数以降は、必要な場合のみ指定します。
それぞれの引数について、詳しく見ていきましょう。
第1引数から第3引数まで
基本的な動きとしては、第1引数に指定された値を第2引数の中から検索し、第3引数の中から該当する項目を返す、となります。
具体例は、『基本的な使い方』を参照してください。
第4引数：見つからない場合
第4引数は「見つからない場合」です。
これは、第1引数に指定された検索値が、第2引数に指定された検索範囲の中になかった場合、XLOOKUP関数が最終的に返す値を指定するための引数です。
例を挙げます。
XLOOKUP関数の第4引数に「該当なし」という文字列を指定しています。
その上で、上の表に存在しない「A005」という商品コードをA9セルに入力しました。
その結果、XLOOKUP関数が「該当なし」という文字列を返しています。
なお、第4引数を指定しないと、検索値が検索範囲の中に見つからなかった場合、エラーになります。
第5引数：一致モード
第5引数は「一致モード」です。
「一致モード」の値は、以下の4つの中から1つを入力します。
- ０：完全一致
- -1：完全一致または次に小さい項目
- 1：完全一致または次に大きい項目
- 2：ワイルドカード検索
これは、第1引数の検索値を第2引数で指定した検索範囲から探し出す際に、どういう基準で一致したと判断するかを指定しています。
完全一致
「0」の完全一致については、文字通り、検索値と全く同じ値が検索範囲にあった場合に、一致とみなします。
”一致”という言葉から想像される最もオーソドックスな解釈はこれでしょう。
完全一致または次に小さい項目
では、「-1」の「完全一致または次に小さい項目」というのはなんでしょうか。
これは、検索値と全く同じ値が検索範囲になかった場合に、検索範囲にある値のうち、検索値の次に小さい値を一致したとみなす方法です。
例を見てみましょう。
A11セルに「A005」という存在しない商品コードを入力しています。
その結果、検索範囲にある値のうち、「A005」の次に小さい「A004」が一致したとみなされ、その価格である「120」と言う結果が返されています。
完全一致または次に大きい項目
同様に、「1」の完全一致または次に大きい項目を指定すると、検索範囲にある検索値の次に大きい値が一致したとみなされます。
こちらもひとつ例を挙げます。
存在しない「A005」という商品コードの次に大きい「A006」が一致したと判断され、その価格である「500」が返されています。
ワイルドカード検索
「2」のワイルドカード検索とはなんでしょうか。
これは、ワイルドカード、つまり、「*」や「?」を使用して、パターン一致の検索をする方法です。
「*」と「?」の意味は以下の通りです。
- *：0文字以上の任意の文字列
- ?：任意の1文字
それぞれ例を見ていきましょう。
A11セルには「*4」と入力されています。
最初の「*」が0文字以上の任意の文字列を意味していますので、商品コードの「A004」が条件に該当します。
それにより、「A004」の価格である「120」が返されています。
では次に「?」を使用した例です。
今度は、A11セルに「?004」と入力されています。
「?」は任意の1文字を意味していますので、先ほどと同じく「A004」が該当し、「120」という結果が返されています。
第6引数：検索モード
第6引数は、「検索モード」です。
「検索モード」では、データの先頭から検索するのか、末尾から検索するのかを指定します。
「検索モード」に指定できる値は以下の4つです。
- 1：先頭から検索
- -1：末尾から検索
- 2：先頭から検索（バイナリ検索）
- -2：末尾から検索（バイナリ検索）
まず、「1」と「-1」を指定した例をそれぞれ見ていきましょう。
B11セルには検索モード「1」を指定した関数、B15セルには検索モード「-1」を指定した関数を入力しています。
（C11セル、C15セルのそれぞれに、入力した関数を記載しています）
どちらの関数もA列に入力された価格を検索し、該当する商品名を返すように指定してあります。
同じ「120」という値を検索していますが、B11セルでは上にある商品名「消しゴム」が返されており、B15セルでは下にある商品名の「画用紙」が返されています。
次に「2」と「-2」についてです。
上の説明にある通り、これらはいずれもバイナリ検索をするための指定です。
バイナリ検索の仕組みについての説明は複雑になるため割愛しますが、おおざっぱに言うならば、高速に検索する方法と考えていただければと思います。
検索モード「2」「-2」を指定する際の注意点は、検索範囲のデータが昇順または降順に整列されていなければならない、ということです。
先ほどの例のように価格を検索するならば、価格列の値が以下のように整列されていなければなりません。
検索モード「2」「-2」を指定する検索は、大量のデータの中から検索する必要がある場合に適している方法と言えるでしょう。
XLOOKUP関数を使用するメリット
ここまで、XLOOKUP関数の使い方について解説してきました。
これ以降で、XLOOKUP関数を使用するメリットについて簡単に解説していきます。
XLOOKUP関数を使うメリットは4つあります。
- 左側にある値を返すことができる。
- 複数列を返すことができる。
- VLOOKUP関数とHLOOKUP関数でできることの両方に対応している。
- 値が見つからない場合のエラーハンドリングが容易。
ひとつひとつ順番に見ていきましょう。
左側にある値を返すことができる
従来使われていたVLOOKUP関数と比べたときに最も目立つメリットが、左側にある値を返せることだと思います。
VLOOKUP関数は、その仕様上、検索キーが一番左の列にある必要がありました。
しかし、XLOOKUP関数であれば、検索範囲と戻り範囲を別々の引数で指定できるため、検索範囲よりも左側にある列を戻り範囲として指定することができます。
XLOOKUP関数がなかった頃は、検索範囲の左側にある値を返すには、MATCH関数やINDEX関数などを組み合わせなければできませんでした。
参考：『ExcelのVLOOKUP関数だと右側しか探せない。左側を探すには？』
それが、XLOOKUP関数が登場したことで、XLOOKUP関数ひとつでそれが実現できるようになり、大変使いやすくなりました。
複数列を返すことができる
次は、複数列を返すことができるという点です。
こちらも従来のVLOOKUP関数の比較になりますが、VLOOKUP関数では、表にある一列しか返すことができませんでした。
しかし、XLOOKUP関数では、戻り範囲に複数列を指定することで、複数列の値を返すことができます。
以下に例をあげます。
B11セルに入力されたXLOOKUP関数の戻り範囲（第3引数）に、B列とC列を指定しています。
それにより、ひとつの関数で商品名と価格の両方が返されており、その結果がB11セルとC11セルに表示されています。
VLOOKUP関数とHLOOKUP関数でできることの両方に対応している
表形式のデータの特定の行のデータを検索するのがVLOOKUP関数、特定の列のデータを検索するのがHLOOKUP関数ですが、XLOOKUP関数であればそのどちらも検索も実現できます。
これまで挙げた例では、特定の行を検索するケースを取り上げてきましたので、以下に列を検索する例をあげます。
第2引数の検索範囲に1行目（B1:G1）、第3引数の戻り範囲に2行目（B2:G2）を指定することで、A6セルに入力された商品コードに対応する商品名が検索できています。
値が見つからない場合のエラーハンドリングが容易
最後は、値が見つからない場合のエラーハンドリングが容易な点です。
これは第4引数の「見つからない場合」に関連した内容です。
VLOOKUP関数やHLOOKUP関数は、検索値が検索範囲の中に見つからなかった場合、エラー「#N/A」を返します。
なので、複数あるデータのそれぞれに対して、VLOOKUP関数を使った計算結果を並べて表示した場合、見つかったものについては数値、見つからなかったものについてはエラー「#N/A」が表示されることになります。
数値とエラーが混在したデータが並べて表示されると、非常に見づらくなります。
このような場合、VLOOKUP関数を使っているケースでは、IF関数・ISERROR関数を組み合わせることでエラーハンドリングを行い、「#N/A」を空白に置き換えることが考えられます。
しかし、XLOOKUP関数を使用すると、複数の関数を組み合わせることなく、第4引数の「見つからない場合」を指定するだけで同様のことが実現できるようになります。
おわりに
本記事では、XLOOKUP関数の使い方と、使うメリットについて解説しました。
XLOOKUP関数は、従来使用されていたVLOOKUP関数やHLOOKUP関数と比べて機能が改良され、だいぶ使い勝手が良くなった関数です。
ぜひ、活用してみてください。
