スプレッドシートのXLOOKUP関数の使用法・注意点を解説！VLOOKUP上位互換な参照関数
VLOOKUPの上位互換であるXLOOKUP関数をご存知でしょうか。
XLOOKUPは2020年からスプレッドシートに加わった、比較的新しい関数です。
ビジネスでもよく使われるVLOOKUP関数と同様、指定の条件を入力することで、自動的に参照したいデータが返されるものです。
VLOOKUP関数と用途は同じですが、引数の指定方法から複数参照まで、便利さが大違いのXLOOKUP。
今回はスプレッドシートのXLOOKUP関数について、使い方や注意点、VLOOKUP(HLOOKUP)との違いをわかりやすく解説します。
スプレッドシートで検索合致したデータを参照
早速、XLOOKUP関数でどのようなことができるのか見てみましょう。
例えば次のような見積書のフォーマットと商品マスタがあるとします。
XLOOKUP関数が使えると、商品コード（A列）の入力だけで自動的に単価（B列）が反映されます。
商品コードと単価の両方を入力する手間がなくなり、間違いを減らせるメリットがあります。
見積書や請求など大切な書類こそ、ミスがないように関数を使った自動化が大切です。
スプレッドシートのXLOOKUP関数が利用可能
XLOOKUP関数は、次の3つの必須入力と3つの任意入力の引数からできています。
※引数とは関数に入力する値のことを指します。
※VLOOKUPやHLOOKUP関数は引数が4つで、この点が異なります。
=XLOOKUP(検索キー, 検索範囲, 範囲の結果, ［見つからない場合の値］, [一致モード], ［検索モード］）
それぞれの引数を、見積書の単価（B3セル）を出す場合を想定して説明します。
| 引数 | 詳細 |
| 検索キー | 検索する「値」を示します。 上図の見積書のA3セルに入力する商品コードを指します。 |
| 検索範囲 | 上記の指定キーを検索する「範囲」を示します。 上図では、商品マスタのF列を指します。 |
| 対応範囲 | 指定した範囲の中から、取得する「行」または「列」の値を示します。 上図では商品マスタ内の単価を出したいので、G列を指します。 |
| 見つからない場合の値 | 検索範囲内に検索キーが見つからない場合に返す値を示します。 |
| 一致モード | 近似値か完全一致かを示します。 近似値を示す場合は、”1”もしくは”True”を入力します。 見積書などの場合は完全一致を表す”0”もしくは省略をします。 |
| 検索モード | 検索する「順序」を示します。 見積書などの場合は”1”もしくは省略で先頭から順に検索をします。 |
XLOOKUP関数の使い方と使用例
実際に他のLOOKUP関数との違いを交えながら、XLOOKUP関数を使った事例をご紹介します。
XLOOKUPは引数の指定方法によって色々な使い方があるため、いくつかのパターンで解説します。
見積書の作成
見積書を作成する際に、XLOOKUP関数は非常に便利です。
１．商品コードの入力
商品マスタから商品コードを選びA3以降に転記します。
２．単価にXLOOKUP関数を入力
B3セルには次の通りに入力すると、商品マスタ表にある商品コード（B001）に一致する単価（500）が返されます。
=XLOOKUP(A3,F:F,G:G)
ここでは次のような指示を与えています。
「A3セルの値（商品コード＝B001）をF列（マスタの商品コード）から検索して、一致する値のG列（単価）を参照せよ」
この結果、G5セルに入力されている”500”が返されたことになります。
VLOOKUP関数では、指定した範囲の中から◯行目（もしくは◯列目）の値を返すよう指示します。
※VLOOKUP関数については以下の記事で解説しています。
一方でXLOOKUP関数では一致する値の行（もしくは列）を直接指定できるので、行数を数える手間がありません。
（例）VLOOKUPの場合
＝VLOOKUP(A3,F:G,2,0)
→ F列からA3と一致する値を検索し、2列目を指定する
（今回の例）XLOOKUPの場合
=XLOOKUP(A3,F:F,G:G)
→ F列からA3と一致する値を検索し、G列を参照する
３．数量を入力し、小計を計算
最後は以下の通り、数量を入力し、小計に単価×数量の式を入力することで完成です。
※上記のように、手入力するセルを色付することで、他の人と共有をした際にわかりやすくなります。
尚、ここまではVLOOKUP関数でも同じことができます。
XLOOKUP関数の特徴の1つに、下図のように見積書と商品マスタの並びが一致していなくても参照可能という点があります。
VLOOKUP関数では選択範囲の左端が検索値でなければなりませんが、XLOOKUPではその必要はありません。
スピル対応でデータのまとめ抽出も可能
スピルとは、英語で「こぼれる」や「あふれる」という意味です。
XLOOKUP関数では検索した結果からあふれた値も一緒に抽出してくれます。
先程の見積りを例にして、C列と商品マスタのI列に「在庫」を追記しました。
１．B3セルには次のとおりに関数を入力します。
=XLOOKUP(A3,G:G,H:I)
指示内容は「A3セルの値をG列から検索して、一致する値のH～I列を参照せよ」です。
２．Enterを押すと下図の通り、C3セルの在庫も自動で入力されます。
３．B3セルの式を残りのB4以降に貼り付ける又はオートフィルで、C列も自動反映されます。
この通り、VLOOKUP関数では参照できるのが1列のみ（今回のケースでは単価のみ）ですが、XLOOKUP関数では複数列(※後述で複数行も)の指定が可能です。
見つからない場合の値を指定することが可能
VLOOKUP関数とのもう1つの違いは、検索キーが検索範囲から見つからない場合に返す値や文字列を指定することができる点です。
例えば次の関数を組むことで、商品コードを間違えた際に、”該当なし”と表示させることができます。
=XLOOKUP(A5,F:F,G:G,"該当なし")
一方VLOOKUP関数の場合は、エラー（#N/A）で返すのみです。
VLOOKUPではエラー（#N/A）を表示させない場合にIFERROR関数を使う必要がありますが、XLOOKUPならその必要もありません。
列方向の検索も可能
ここまでVLOOKUP関数と比較した使い方を紹介しましたが、HLOOKUPのように列方向の参照も可能です。
※HLOOKUP関数については以下の記事で解説しています。
そのため、商品マスタが下図のような並びの場合にも同様に活用できます。
=XLOOKUP(A3,$10:$10,$11:$11)
VLOOKUPとHLOOKUPしかないころは、スプレッドシートの表形式に応じて、参照関数を使い分ける必要がありました。
しかし、2020年から利用可能となったXLOOKUPであれば、どちらのシーンでも対応可能です。
そのため、XLOOKUP関数で処理を統一することができ、関数が混在せずにわかりやすくなります。
スプレッドシートのXLOOKUP関数の注意点
XLOOKUPは非常に便利で、他のLOOKUP系の関数でできることを網羅しています。
ただし、注意すべき点として、「VLOOKUPやHLOOKUP関数とは引数の入れ方が少し異なること」が挙げられます。
VLOOKUP関数に慣れていると、範囲を指定して左からの列数を数えたくなりますが、上述の通り検索範囲と対応範囲を選択するだけで検索結果を出せます。
ただ、VLOOKUPやHLOOKUPでできることはXLOOKUPで実現可能な上位互換の参照関数です。
他のLOOKUP関数を使う必要がなくなるほどXLOOKUPは万能なので、ぜひ引数の指定方法を覚えて活用しましょう。
まとめ・終わりに
今回、スプレッドシートの参照関数であるXLOOKUP関数について紹介しました。
XLOOKUP関数は見積書や請求書を作成する際に便利です。
見積書では入力項目を減らすことができ、在庫数も確認しながら作成ができます。
今回解説したように
- 商品コードだけの入力で、単価を自動で参照できること
- 商品マスタと見積書の並びが異なっていても参照可能なこと
- スピル対応で、欲しいデータをまとめて抽出できること
- エラー時の表示内容を指定できること
- 行方向だけでなく、列方向の参照も可能なこと
これらのことがXLOOKUP関数で実現可能です。
XLOOKUP関数はVLOOKUPやHLOOKUPの上位互換のため、どちらの利用シーンでもXLOOKUPで対応できます。
少し引数が他と異なるため注意が必要ですが、慣れたら間違いなく他のLOOKUPよりも活用範囲が広い関数です。
XLOOKUPをマスターすることで、VLOOKUPよりもシンプルな関数記述と、VLOOKUP/HLOOKUPの混在もなくせます。
XLOOKUP関数はスプレッドシートの効率化アップにつながるので、ぜひ活用してみてください。