ExcelのXLOOKUP関数とは？ VLOOKUPとの違いや使い方を解説

VLOOKUP関数の後継ともいえる「XLOOKUP」関数について、VLOOKUP関数との違いを比較しながら、使用方法や基本＆補足ポイントを分かりやすく解説します。
   

XLOOKUP関数とは

「XLOOKUP」（読み方：エックスルックアップ）は、戻り範囲として指定したデータ範囲から、検索範囲で検索したデータの行位置に該当するデータを取り出す関数です。

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

XLOOKUP関数は、VLOOKUP関数と同じように表引きができる新しい関数です。VLOOKUP関数よりも分かりやすく、使いやすくなっています。この記事では、VLOOKUP関数と比較しながら、その使用方法、3つの基本ポイント、3つの補足ポイントを紹介します。

※本記事で紹介しているXLOOKUP関数およびスピル機能が使用できるのは、Microsoft 365（旧Office 365 ProPlus）のExcelです。Excel 2019では使用できないのでご注意ください。

 

XLOOKUP関数の使い方

早速、XLOOKUP関数のサンプルを紹介しましょう。ここでは、ガイド記事「ExcelのVLOOKUP関数の使い方！指定列からデータを取り出す方法を初心者向けにサンプルでわかりやすく解説」で紹介したサンプルと同じ題材を使用して、VLOOKUP関数との違いが分かるようにXLOOKUP関数の使い方を紹介します。
 XLOOKUP関数が入力されているセルのうち、商品名を表示しているセルC16の内容を詳しく見ていきましょう。
 
 

XLOOKUP関数で指定する引数の内容とは

セルC16に入力されている数式は
=XLOOKUP(B16,B3:B12,D3:D12)
です。  
1番目の引数に指定しているのは、検索したいデータを入力するセル（検索値）です。サンプルでは、セルB16を指定しています。  
2番目の引数には、検索値を検索するデータ範囲（検索範囲）を指定します。サンプルでは、セル範囲B3:B12を指定しています。表の見出しを含めずに指定してください。
3番目の引数には、データを取り出す範囲（戻り範囲）を指定します。検索値が検索範囲内で見つかったらどの範囲からデータを取り出すのかを指定しています。検索されたデータの行位置のデータのうち、この戻り範囲に指定したセル範囲の列からデータが取り出されます。検索範囲と同様に、表の見出しを含めずに指定してください。また、戻り範囲と検索範囲の行数は同じである必要があります。  

VLOOKUP関数とXLOOKUP関数の違いとは

XLOOKUP関数の作成方法がわかったところで、VLOOKUP関数との違いについて整理してみましょう。VLOOKUP関数では、指定したセル範囲内を左端の列で検索し、見つかった行位置のデータのうち、列番号（指定したセル範囲内の左端から数えた番号）で指定されたデータを取り出していました。指定するセル範囲は1つだけであり、検索する列は左端の列に固定されていて、取り出す列は列番号で指定しています。  
一方、今回紹介しているXLOOKUP関数では、検索範囲内を検索して、見つかった行位置のデータのうち、戻り範囲内からデータを取り出しています。VLOOKUP関数では、指定するセル範囲が1つだけだったのに対し、XLOOKUP関数では、指定するセル範囲を検索する範囲と取り出す範囲と2つに分けているのです。この指定方法によって、検索範囲を自由に指定できるようになり、取り出す列位置もセル範囲で指定できるようになりました。  
この引数の指定方法によって、より柔軟に表引きができるようになっています。その基本的なポイントを3つ紹介しましょう。
 
 

【基本ポイント①】検索範囲は自由に指定できる

VLOOKUP関数では、検索列が左端に固定されていましたが、XLOOKUP関数では、その検索範囲を自由に指定できます。例えば、下図のように「商品名」のセル範囲を検索して、該当する「分類」のデータを取り出すことができます。左端の列ではないセル範囲を検索し、検索列より左側のセル範囲からデータを取り出すことができています。検索範囲と戻り範囲を分けて指定できるメリットといえるでしょう。  

【基本ポイント②】戻り範囲は元データ範囲の変更の影響を受けない

VLOOKUP関数では、データを取り出す列を列番号で指定していました。そのため、元データ範囲の列が増減すると列位置が変わってしまうため、VLOOKUP関数の列番号の指定を更新する必要がありました。  
一方、XLOOKUP関数では、データを取り出す列をセル範囲で指定するため、元データ範囲の列が増減しても、XLOOKUP関数を更新する必要はありません。指定先のセル範囲は、列が増減したときに一緒にスライドするので、XLOOKUP関数は変更の影響を受けないのです。たくさんの列の表引きをVLOOKUP関数で作成していたとき、このメンテナンスは面倒な作業でした。この手間が省ける点もXLOOKUP関数の大きなメリットといえます。  

【基本ポイント③】スピル機能によって複数列の取り出しが簡単にできる

取り出す列が連続している（飛び飛びでなく隣接している）場合、スピル機能によってXLOOKUP関数の結果表示の範囲が自動拡張されます。XLOOKUP関数を入力するセルは1つだけですが、戻り範囲に指定したセル範囲の分まで関数の結果が自動表示されるのです。入力した数式を「オートフィルで隣接セルへコピーするように結果表示の範囲が自動拡張される」と考えると分かりやすいでしょう。

VLOOKUP関数の場合、入力した数式をオートフィルでコピーしたあと、取り出す列番号の指定を一つひとつ書き換える必要がありました（COLUMN関数を活用する方法もあります）。この手間が省ける点もXLOOKUP関数の大きなメリットといえるでしょう。
 この3つの基本ポイントを押さえておけば、ひとまず、XLOOKUP関数を使用できます。続いて、XLOOKUP関数を活用する上で、ぜひ覚えておきたい補足ポイントを3つ紹介します。

 

【補足ポイント①】XLOOKUP関数は既定で「完全一致」で検索する

XLOOKUP関数は、検索範囲内で検索値を検索するとき、完全に一致するデータを検索する方法（完全一致）が既定（デフォルト）になっています。XLOOKUP関数の引数で何も指定しなくても完全一致で検索できるということです。※XLOOKUP関数で完全一致以外の検索方法を指定したい場合は、5番目の引数「一致モード」に検索モードを指定します。検索モードについては、別の機会に紹介します
 
VLOOKUP関数の場合、完全一致で検索するには4番目の引数で「FALSE」を指定する必要がありました。この指定を忘れると、既定の「TRUE」が指定されて近似一致による検索になります。ガイド記事「ExcelのVLOOKUP関数の使い方！指定列からデータを取り出す方法を初心者向けにサンプルでわかりやすく解説」でも紹介した通り、近似一致だと勘違いの原因となる場合があるため、既定が完全一致であるXLOOKUP関数は比較的安心して使用できるといえるでしょう。  

【補足ポイント②】検索値が見つからなかった場合に表示するデータを指定できる

XLOOKUP関数では、検索値が見つからなかった場合に表示するデータを4番目の引数で指定できます。  
同様の処理をVLOOKUP関数で実現するには、ISERROR関数（Excel 2007より前）やIFERROR関数（Excel 2007以降）を組み合わせる必要がありました。この使用例と比べると、XLOOKUP関数は手間がかからなくて使いやすいですね。  

【補足ポイント③】XLOOKUP関数は列単位でデータを取り出せる

使用頻度は高くないと思いますが、XLOOKUP関数では列単位でデータを取り出すことも可能です。これは、特定の行内を（ヨコ方向に）検索し、見つかったデータの列位置にあるデータを戻り範囲から取り出すことができます。つまり、これまで紹介してきたサンプルのタテとヨコを逆にしたような使用方法であり、従来のHLOOKUP関数と同等の使用方法となります。

下図は、これまで紹介してきたサンプルの商品マスタの行と列を入れ替えて、XLOOKUP関数をHLOOKUP関数のように使用した例です。「No」の行をヨコ方向に検索し、見つかった列位置のデータのうち、「商品名」の行のデータを取り出しています。  
この使い方とスピル機能を合わせると、表の見出しを検索して、見つかった列全体を取り出すことができます。このパターンを活用すると、列数が多い（横方向に長い）表を使用しているとき、必要な列だけを取り出してコンパクトに表示する、といったことが可能です。

下図は、商品マスタの行と列を元に戻したサンプルによる使用例です。商品マスタの見出し行をヨコ方向に検索し、見つかった見出しの列全体のデータを取り出しています。  
その他、XLOOKUP関数には5番目と6番目の引数があったり、XLOOKUP関数がセルそのものを返している性質を活用した実践的な使用方法があったりします。これらの活用方法については、別の機会に紹介したいと思います。