XLOOKUP関数の使い方｜複数条件に合うデータを抽出する方法など

エクセルのXLOOKUP関数の使い方を紹介します。XLOOKUP関数はVLOOKUP関数やHLOOKUP関数、IFERROR関数、INDEX関数、MATCH関数などの機能を兼ね備えたとても使い勝手のよい関数です。

この記事では複数条件に合うデータを効率よく抽出する方法も解説していますので、ぜひ参考にしてください。

XLOOKUP関数とは

エクセルのXLOOKUP関数とは、Office2021以降に導入された比較的新しい関数です。VLOOKUP 関数と同様に表や範囲から特定の値を検索し、一致する値に対応するデータを返します。

VLOOKUP 関数と比べてより柔軟に条件を指定することが特徴で、検索範囲を縦横指定なく指定したり、検索範囲が1列目にない表で検索したりする時に便利です。

XLOOKUP関数はExcel 2016／2019では使えない

XLOOKUP関数は現在は使用できるのは「Office365」のみであり旧バージョンのOfficeでは使用できません。Excel 2016／2019といった古いエクセルでは使えませんので注意しましょう。

XLOOKUP関数の書式

XLOOKUP関数の書式は「=XLOOKUP(検索値, 検索範囲, 戻り範囲, 見つからない場合, 一致モード, 検索モード)」です。引数の指定の仕方を詳しく解説していきます。

第1引数：検索値（必須）

第1引数は「検索値」となります。検索したい値を指定します。例えば、2022年の利益を調べたい場合は「2022」が検索値に該当します。検索したい文字列をセル参照するか、直接記述するかして指定しましょう。

第2引数：検索範囲（必須）

第2引数は「検索範囲」です。検索値が含まれる列を指定します。例えば検索値を「2021年」とする場合、「年」という項目の列をセル参照で指定してください。関数をコピーして使用する場合は範囲を絶対参照で指定するようにしておきましょう。

第3引数：戻り範囲（必須）

第3引数は「戻り範囲」です。返す配列または範囲を指定します。例えば2021年の売上を調べたい場合、「2021」が検索値で「売上」が戻り値となります。返して欲しい値が含まれる項目の列や範囲を指定しましょう。

第4引数：見つからない場合（省略可）

第4引数は「見つからない場合」です。検索値と一致するものがない場合、指定したテキストを返します。例えば「”検索値が見つかりません”」と指定すると、有効な一致がなければ「検索値が見つかりません」と表示されます。この引数は省略することができます。

第5引数：一致モード（省略可）

第5引数は「一致モード」です。一致の種類を次のように指定します。

• 「0」完全一致しないと表示しない。見つからない場合は「#N/A」が返される。既定の設定。
• 「-1」完全一致したらその値を表示する。完全一致しなかったら、次に大きい項目を表示する。
• 「1」 完全一致したらその値を表示する。完全一致しなかったら、次に小さい項目を表示する。
• 「2」 *、?、~など、検索値にワイルドカードを使用するときに選択する。

実用的にはほぼ「0」の完全一致が利用されるので、基本的には省略しても構いません。

第6引数：検索モード（省略可）

第6引数は「検索モード」です。使用する検索モードを次のように指定します。

• 「1」 検索範囲の上から下に向かって検索値を探す。既定の設定。
• 「-1」 検索範囲の下から上に向かって検索値を探す。
• 「2」昇順に並んだ検索範囲に対してバイナリ検索する。検索範囲が昇順に並び替えられていない場合、無効な結果が返される。
• 「-2」降順に並んだ検索範囲に対してバイナリ検索する。検索範囲が降順に並べ替えられていない場合、無効な結果が返される。

こちらも第5引数同様、基本的に省略して使用することが多いでしょう。

XLOOKUP関数の基本的な使い方

XLOOKUP関数の基本的な使い方を、例のような売上データを使って解説していきます。

任意の年の利益を、XLOOKUP関数を用いて取り出せるようにしてみましょう。結果を表示させる欄を作成します。

D12に「=XLOOKUP(C12,$C$3:$C$9,$E$3:$E$9)」と入力します。他のセルにも関数をコピーして使用したいので、検索範囲と戻り範囲は「$」を使用してセル範囲を固定しています。

残りのセルもオートフィルを使用して関数を入力します。D12セルの右下にカーソルを移動させましょう。アイコンが白い十字から黒い十字に変化したら、そのまま下の方へドラッグしてください。関数をコピーしたいセルまできたらマウスから指を離します。

これでXLOOKUP関数が全ての欄に入力され、C列に書かれた年の利益を取り出すことができました。

このようにXLOOKUP関数は、引数を最低3つ指定すればLOOKUP関数と同じように使用することができます。

XLOOKUP関数で複数条件に合うデータを抽出する方法

XLOOKUP関数を使って、複数条件に合うデータを抽出してみましょう。複数条件を指定する場合には「文字列データの結合」を行う演算子「＆」を使用します。例のような試験データで、「東京」エリアの「成績上位者（1~3位）」の受験者IDを抽出してみましょう。

XLOOKUP関数の第1引数には「&」と指定します。例では「東京」かつ成績順位が「1」「2」「3」の受験者を検索したいのでそれぞれ「B11&B13」「B11&B14」「B11&B15」となります。

次に第2引数の検索範囲ですが、こちらも「&」を使って範囲をつなげます。例では全て「E3:E9&F3:F9」となります。

第3引数は戻り範囲となります。購入者IDを抽出したいので「B3:B9」となります。

これで東京エリアの成績上位者3名の受験者IDを取り出すことができました。

この例では2つの条件を指定しましたが「＆」で条件をつなげていけば、3つ以上の複数条件を検索することも可能です。

XLOOKUP関数で「#N/A」エラーになる原因

XLOOKUP関数を使用していると「#N/A」エラーが出てくるケースがあります。#N/Aエラーは、数式で参照している数値が存在しない時に表示されるエラーです。

よくある原因として、検索値と一致するデータが見つからないことが考えられます。例えば検索値の文字列の半角と全角が異なってしまうと、一見同じ値に見えても合致するデータなしと判断されることがあります。

また関数をコピーした時、相対参照で記述したセル範囲がズレてしまうことで、検索値が範囲外になってエラーとなることもあります。

#N/Aエラーが表示された際は、検索値や指定した範囲に誤りがないか確認しましょう。

XLOOKUP関数とVLOOKUP関数の違い

XLOOKUP関数とVLOOKUP関数の基本的な機能は同じです。ここでは、類似する2つの関数の違いを整理してみましょう。XLOOKUP関数のみ実行可能な特徴をまとめると次のようになります。

• 検索値を含む列が検索範囲の一番左になくてもOK
• エラー時に表示する値を関数内に入力できる
• 縦方向だけでなく、横方向の検索もできる

このようにXLOOKUP関数は引数が多い分、VLOOKUP関数よりも柔軟な対応ができると言えます。旧バージョンのエクセルとの互換性を気にする必要がなければ、XLOOKUP関数を使うのがおすすめです。

以降では、XLOOKUP関数とVLOOKUP関数で出来ることの違いを、例を用いながら詳細解説していきます。

検索値を含む列が検索範囲の一番左になくてもOK

VLOOKUP関数はデータの一番左に検索値を含む列がなくてはいけませんでしたが、XLOOKUP関数はどの列にあってもOKです。これで元データの形式を整えずにそのまま適用できるのでスムーズに検索をすることが可能です。

その代わりXLOOKUP関数では、検索値と検索範囲、戻り範囲の3つの引数を指定しなくてはならないことを忘れないようにしましょう。

エラー時に表示する値を関数内に入力できる

エラー時に「合致するデータが見つかりません」などの文字列を表示させたい場合、VLOOKUP関数ではIFERROR関数と組み合わせることが必要です。

しかしXLOOKUP関数では第4引数にエラー時の対応を指定することで、IFERROR関数を使用しなくてもエラー時の文字を表示させることができます。

関数1つで対応できるので、見た目にもスッキリしてミスも減るでしょう。

縦方向だけでなく、横方向の検索もできる

XLOOKUP関数は縦方向だけでなく、横方向にも検索も可能です。VLOOKUP関数とHLOOKUP関数を使い分ける必要がなく、XLOOKUP関数1つ使い方をマスターすればよいので覚える負担が減るでしょう。

横方向に検索したい時は画像のように、検索範囲や戻り範囲を列ではなく行指定するだけでOKです。ぜひ一度横方向の使い方も試してみてください。

XLOOKUP関数とスピル機能を組み合わせた使い方

最後にスピル機能と組み合わせて、より効率的にXLOOKUP関数を使う技を紹介します。スピル関数はMicrosoft365から実装された機能で、数式を入力したセルだけでなく隣接するセルにも結果を表示してくれる便利な機能です。

XLOOKUP関数では、検索値にスピルを使うと効率的に検索ができます。例のような画像を使って具体的な使用方法を見てみましょう。第1引数に「C11:C13」と範囲を指定します。

関数を入力し終えたら「Enter」キーを押しましょう。選択した範囲全てに関数の結果が表示されます。

これまではオートフィルなどで関数をコピーして使用していたところを、範囲指定するだけで済みます。検索範囲や戻り範囲の指定を絶対参照にする必要もないため、操作にかかる時間を大幅に短縮することができ、ミスも減らせるでしょう。

XLOOKUP関数の使い方についておさらい

XLOOKUP関数は、これまではVLOOKUP関数やHLOOKUP関数、IFERROR関数、INDEX関数、MATCH関数を組み合わせて実現していた操作を1つの関数で実行することができる強力な関数です。

VLOOKUP関数の上位互換とも言われるとても便利な関数ですので、スピル機能と併せて積極的に使ってみてください。