【Excel】もうVLOOKUPには戻れない？ 新定番「XLOOKUP」関数の使い方：Tech TIPS
「Microsoft Excel（エクセル）」のデータ検索といえば、VLOOKUP関数が定番だ。商品の発注や在庫管理などに多用している人も多いのではないだろうか。しかしVLOOKUP関数は、「検索範囲の左端しか検索できない」「列番号を数えるのが面倒」といった、幾つかの弱点がある。その弱点を解決した「XLOOKUP」関数が利用可能になっている。本Tech TIPSでは、XLOOKUP関数の基本的な使い方を中心にVLOOKUP関数の代わりにXLOOKUP関数を使うメリットなどについて紹介していく。
対象：Excel 2021／2024／365
「XLOOKUP」関数の使い方
Excelのデータ検索で定番の「VLOOKUP」関数。商品の発注や在庫管理などに多用している人も多いと思う。しかしVLOOKUP関数は、「検索範囲の左端しか検索できない」「列番号を数えるのが面倒」といった、幾つかの弱点がある。その弱点を解決した「XLOOKUP」関数が利用可能になっている。本Tech TIPSでは、XLOOKUP関数の基本的な使い方を中心にVLOOKUP関数の代わりにXLOOKUP関数を使うメリットなどについて紹介していく。
「Microsoft Excel（エクセル）」のデータ検索といえば、VLOOKUP関数が定番だ。商品リストの中から商品コードで商品名や価格を検索して抽出するといった際に利用される。商品の発注や在庫管理などに多用している人も多いのではないだろうか。
しかしVLOOKUP関数は、「検索範囲の左端しか検索できない」「列番号を数えるのが面倒」といった、幾つかの弱点がある。そんなVLOOKUPの課題を解決する後継として登場したのが「XLOOKUP（エックスルックアップ）」関数だ。XLOOKUP関数は、Microsoft 365やExcel 2021以降のバージョンで利用できる。
本Tech TIPSでは、XLOOKUP関数の基本的な使い方を中心にVLOOKUP関数の代わりにXLOOKUP関数を使うメリットなどについて紹介していく。
XLOOKUPの基本的な使い方
XLOOKUPは、範囲または配列を検索し、最初に見つかった一致に対応する項目を返す関数だ。一致するものがない場合、XLOOKUP関数はエラーを返す（「一致モード」の設定によっては最も近い一致を返す）。
=XLOOKUP(検索値, 検索範囲, 戻り配列, ［見つからない場合］, ［一致モード］, ［検索モード］)
| 引数 | 説明 |
|---|---|
| 検索値 | 検索する値 |
| 検索範囲 | 検索する配列または範囲 |
| 戻り配列 | 返す配列または範囲 |
| 見つからない場合（オプション） | 有効な一致が見つからない場合は、指定した［見つからない場合］テキストを返す。有効な一致が見つからず、［見つからない場合］を設定していない場合、「#N/A」エラーを返す |
| 一致モード（オプション） | 一致の種類を指定 0：完全一致、見つからない場合は、「#N/A」エラーを返す（既定の設定） -1：完全一致、見つからない場合は、次の小さなアイテムを返す 1：完全一致、見つからない場合は、次の大きなアイテムを返す 2：「*」「?」「~」が特別な意味を持つワイルドカードによる一致 |
| 検索モード（オプション） | 使用する検索モードを指定 1：先頭の項目から検索を実行（既定の設定） -1：末尾の項目から逆方向に検索を実行 2：昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行、並べ替えられていない場合、無効な結果を返す -2：降順で並べ替えられた検索範囲を使用してバイナリ検索を実行、並べ替えられていない場合、無効な結果を返す |
| XLOOKUP関数の引数 | |
XLOOKUPの最もシンプルな構文は、「検索値」「検索範囲」「戻り配列」の3つの引数を指定すればよい。
A列に「商品コード」、B列に「商品名」、C列に「価格」で構成される下画面のような表で、XLOOKUP関数を使った基本的な検索を試してみよう。
ここでは商品リストから、商品コード「003」の商品名を調べてみよう。数式を入力するセル（「F2」セル）に以下のように入力する。「E2」セルには商品コードが入力されているとする。
=XLOOKUP(E2, A2:A21, B2:B21)
「E2」セルに入力されている「003」を「検索値」として、商品コードの列「A2:A21」を検索範囲として検索、見つかった行に対する商品名の列「B2:B21」の範囲（戻り配列）から値を取り出す。この例では、商品コード「003」に対応する「じゃがいも」が返される。
XLOOKUPの基本的な使い方
XLOOKUP関数では、検索に使う値（検索値）、検索するセル範囲（検索範囲）、検索結果を取り出すセル範囲（戻り配列）の3つを指定するのが基本的な使い方だ。検索範囲は列と行を関係なしに指定できるので、VLOOKUP関数とHLOOKUP関数のように検索方向で関数を使い分ける必要はない。行と列を入れ替えても、同じようにデータの取り出しが可能だ。
XLOOKUPがVLOOKUPより優れる点
上記のような使い方は、VLOOKUP関数でも実現可能で、XLOOKUP関数に置き換えるメリットはほとんどない。XLOOKUP関数の真価は、VLOOKUP関数が苦手としていたことを簡単に実現できる点にある。
その一つが、左方向への検索が可能な点だ。VLOOKUPでは、検索範囲（2番目の引数で指定する範囲の左端列）より左側の列からデータを取り出すことができなかった。上記の表では、商品名から商品コードを取り出したい場合、列を入れ替えた表を作成したり、INDEX関数とMATCH関数を組み合わせたりする必要があり、かなり面倒であった。
XLOOKUP関数では、検索範囲と戻り配列が独立しているため、列の順番を気にせずに検索が可能だ。
例えば、前出の商品リストから、「トマト」の商品コードを調べてみよう。数式を入力するセル（「E3」セル）に以下のように入力する。「F3」セルには商品名（トマト）が入力されているとする。
=XLOOKUP(F3, B2:B21, A2:A21)
「F3」セルに入力されている「トマト」を「検索値」として、商品名の列「B2:B21」を検索範囲で検索、見つかった行に対する商品コードの列「A2:A21」の範囲から値を取り出す。この例では、商品名「トマト」に対応する「005」が返される。
検索値の左側の列の値を取り出す
VLOOKUPでは、検索範囲より左側の列からデータを取り出すことができなかった。XLOOKUP関数では検索結果を取り出すセル範囲（戻り配列）を左側の列に指定すればよい。この例のように表の商品名から商品コードを検索するといったことも簡単に実現できる。
ちなみに、XLOOKUP関数を使わない場合、VLOOKUP関数では左方向への検索ができないため、INDEX関数とMATCH関数を使って以下のように記述する必要がある。少し面倒な数式となることが分かるだろう。
=INDEX(A2:C21,MATCH(F2,B2:B21,0),1)
このようにXLOOKUP関数では左側にある列に対してもデータの取り出しが可能となっている。
IFERROR関数は不要、検索値が見つからない場合の処理
検索値が見つからなかった場合、VLOOKUPは「#N/A」エラーを返す仕様となっている。これを避けるためには、VLOOKUPをIFERROR関数で囲み、エラーが発生した際の処理を記述しておく必要があった（Tech TIPS「【Excel】エラー表示を分かりやすく、IFERROR関数で『#DIV/0!』や『#N/A』をスマートに回避する方法」参照のこと）。これに対し、XLOOKUP関数では、4番目の引数でエラー時の表示が指定できるようになっている。
前出の商品リストに存在しない、「024」で検索を実行してみよう。「E4」セルに「024」が入力された状態で、以下の数式を「F4」セルに入力する。
=XLOOKUP(E4, A2:A21, B2:B21, "該当なし")
商品コード「024」は存在しないため、「#N/A」エラーとなるはずだが、この数式では検索値が見つからなかった場合は「該当なし」という文字列を入力するように指定しているため、セルには「該当なし」と入力される。なお、XLOOKUP関数では、「#N/A」エラーのみが文字列に置き換え可能だ。取り出したデータが「#DIV/0!」エラーなどの場合は、そのままエラーがセルに表示されるので注意してほしい。
このようにXLOOKUP関数ではエラーが発生した場合の処理が記述できるため、わざわざIFERROR関数を使って処理を記述する必要がない。数式がシンプルになる大きなメリットがある。
検索値が見つからない場合の処理
VLOOKUP関数では検索値が見つからなかった場合の「#N/A」エラーを別の文字列などに置き換えたいとき、VLOOKUPをIFERROR関数で囲って、エラーが発生した場合の処理を記述する必要があった。XLOOKUP関数では第4引数に検索値が見つからなかった場合の処理が記述できるため、IFERROR関数を組み合わせる必要がなくなり、数式がシンプルになった。
複数項目を一度に取得可能、XLOOKUP関数の優れた点
XLOOKUPは、戻り範囲を複数列にすることで、関連するデータをまとめて取得できる。
商品コードに対する商品名と価格を同時に取り出したいというのはよくあることだろう。このような場合、商品名と価格のそれぞれのセルにVLOOKUP関数を記述していたはずだ。XLOOKUP関数では、これらをまとめて取り出すことが可能だ。
例えば、前出の商品リストから「002」の商品名と価格を取り出したい場合、数式を入力するセル（「F5」セル）に以下のように入力する。「E5」セルには商品コード（002）が入力されているとする。
=XLOOKUP(E5, A2:A21, B2:C21)
複数項目を一度に取得する
XLOOKUP関数では、検索結果を取り出すセル範囲（戻り配列）を複数列にすることで、自動的に隣接するセルに結果が展開されるようになった。商品コードから商品名と価格を取り出すといった場合、商品名と価格の2つの列を戻り配列に指定することで、1つの数式で2つの結果を取り出すことができる。
このように戻り配列をB2:C21（商品名と価格の2列）として指定すると、結果は自動的に隣接するセルに展開（スピル）され、上記の例では「F5」セルに「にんじん」、「G5」セルに「220」が入力される。
このように1つの数式で複数の結果が得られるため、非常に効率的にデータの取り出しができる。ただし、エラーが発生した場合は、XLOOKUP関数を入力したセルにしか「該当なし」などの指定した文字列が表示されないので注意してほしい（エラーはスピルされない）。
複数列を指定してもエラーはスピルされない
上述のようにXLOOKUP関数では、複数列の戻り配列を指定することで、複数の結果を一度に取り出すことができるようになっている。ただし、エラー処理はスピルされず、数式を入力したセルのみにエラー（や代替の文字列）が入力される。
このようにXLOOKUP関数は、VLOOKUP関数の課題を解決した便利なものとなっている。ただし、XLOOKUP関数が利用できるのは、Excel 365とExcel 2021以降となるので注意してほしい。古いExcelでシート／ブックを開く可能性がある場合は、XLOOKUP関数が入力されているセルはエラーとなるので注意してほしい。
Copyright© Digital Advantage Corp. All Rights Reserved.