VLOOKUP関数の限界！左側を参照するならXLOOKUP関数

VLOOKUP関数は便利ですが、左側の値を取得することができないですよね。XLOOKUP関数を使えば、そのような問題を解決できます！

この記事では、XLOOKUP関数の基本構文と使い方について分かりやすく解説します。

XLOOKUP関数では、オプションで値が見つからない場合にテキストを返すように指示することが出来るので便利です

自己紹介

IT業界でのキャリアを活かし、業務効率化に欠かせないMicrosoft365や便利ツールを紹介しています。少しのコツで膨大な可能性を最大限引き出すためのヒントやテクニックを提供し、読者のビジネスパフォーマンス向上に貢献することを目指しています！

Contents

XLOOKUP関数とは
1. VLOOKUP関数とXLOOKUP関数の比較
2. XLOOKUP関数の基本構文
XLOOKUP関数の使い方 4選
まとめ

XLOOKUP関数とは

読み方：XLOOKUP（エックスルックアップ）

※Excel2016・2019では使用できません

XLOOKUP関数説明

指定した条件に基づいて範囲内から値を検索し、関連する値を返すことができる関数です。
従来のVLOOKUP関数よりも検索の柔軟性が高いところが魅力的です。
私がXLOOKUP関数で感動したところは、VLOOKUP関数で実現できなかった、左方向への検索が出来るところです。

XLOOKUP関数とVLOOKUP関数の比較を表を使って見ていきましょう。

VLOOKUP関数とXLOOKUP関数の比較

以下がVLOOKUP関数とXLOOKUP関数の比較を表形式にしたものになります。
それぞれの特徴を見ていきましょう。

項目	VLOOKUP関数	XLOOKUP関数
検索の柔軟性	左から右への検索のみ対応	上下左右の検索が可能
指定する範囲	指定する範囲は1つ	検索範囲と戻り範囲の2つ
エラーの場合	#N/Aエラーが返される	エラーの場合のテキストを指定できる

比較表を見ると、XLOOKUP関数はVLOOKUP関数に比べて、より多くの機能と柔軟性があることが分かりますね。「左から右, 右から左へ」検索ができることや、エラーの場合に#N/Aを表示するのではなく、任意のテキストを返すことが出来るのは魅力的です。

それでは、XLOOKUP関数の基本構文を見ていきましょう。

XLOOKUP関数の基本構文

XLOOKUP関数構文

=XLOOKUP(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

検索値	検索する値
検索範囲	検索する値が含まれる範囲
戻り範囲	どこの範囲の値を返すか
[見つからない場合]	値が見つからない場合に返したいテキストがあれば指定
[一致モード]	一致の種類を指定 ※デフォルトは完全一致[０]
[検索モード]	検索の順序を指定 ※基本的にはデフォルトの先頭から検索でOK

検索は完全一致が多い＆検索の順序は先頭の文字からが基本になるので、[一致モード] と [検索モード] は、数式に入力しないでも大丈夫です。

XLOOKUP関数の使い方 4選

こちらの章では、XLOOKUP関数の使い方を解説します。

解説する内容はに以下になります。

XLOOKUP関数の基本的な使い方
戻り範囲の列を複数選択
複数の条件を使った検索方法
エラーの場合に任意のテキストを返す

それでは、XLOOKUP関数の使い方を見ていきましょう。

XLOOKUP関数の基本的な使い方

XLOOKUP関数の「検索値, 検索範囲, 戻り範囲」を入力してデータの紐づけをしていきましょう。

こちら章では、下記の表を使っていきます。
内容としては、A4セルからE9セルまで商品コードと1Q~4Qまでの売上が記載されている表があるとします。A2セルには任意の商品コードとB2セルに対象の商品の1Q売上をXLOOKUP関数を使って紐づけていきたいと思います。
※今回はA2セルに商品コード「B001」を入力していきたいと思います。

XLOOKUP関数式

=XLOOKUP(A2,E5:E9,A5:A9)

A2（検索値）	検索する値
E5:E9（検索範囲）	検索する値が含まれる範囲
A5:A9（戻り範囲）	どこの範囲の値を返すか

商品コード「B001」の1Qの売上をXLOOKUP関数を使って紐づけることが出来ました。
関数を入力した状態で、A2セルを別の商品コード名に変更したらどうなるでしょうか？
商品コード「D001」に変更してみましょう。

商品コード「D001」に変更しても売上データが紐づきましたね。
このようにXLOOKUP関数では、データの検索や抽出をすることができます。

実務では、特定の商品コードの1Q~4Q売上を一気に紐づけたい場面もあります。
一気に紐づける方法を次の章で詳しく説明をしていきます。

戻り範囲の列を複数選択

特定の値のデータを複数一気に紐づけたい場合にもXLOOKUP関数は活用できます。
一気にデータを紐づける方法は、XLOOKUP関数の戻り範囲を複数の列で指定してあげることで実現可能です。

こちら章では、下記の表を使っていきます。
内容としては、A4セルからE9セルまで商品コードと1Q~4Qまでの売上が記載されている表があるとします。A2セルには任意の商品コードとB2セル~E2セルに対象の商品の1Q~4Q売上をXLOOKUP関数を使って紐づけていきたいと思います。
※今回はA2セルに商品コード「B001」を入力していきたいと思います。

それでは、B2セル~E2セルに一気にデータを紐づけていきましょう。
※関数を入力するのはB2セルのみ。B2セルに関数を入力出来たらEnterを押すだけです。

XLOOKUP関数式

=XLOOKUP(A2,E5:E9,A5:D9)

A2（検索値）	検索する値
E5:E9（検索範囲）	検索する値が含まれる範囲
A5:D9（戻り範囲）	どこの範囲の値を返すか

商品コード「B001」の1Q~4Qの売上をXLOOKUP関数を使って一気に紐づけることが出来ました。
関数を入力した状態で、A2セルを別の商品コード名に変更したらどうなるでしょうか？
商品コード「E001」に変更してみましょう。

商品コード「E001」に変更しても売上データが紐づきましたね。
範囲を複数指定することが出来ました。
XLOOKUP関数では、複数のセルを連結して検索値を指定することも出来るので見ていきましょう。

複数の条件を使った検索方法：アンド（＆）で連結

この章ではXLOOKUP関数の検索値の指定方法について解説します。
具体的には、2個のセルをアンド（＆）で連結して検索値を指定する方法をご紹介します。
※検索値を＆で連結する場合、検索範囲も＆で連結します。

アンド（＆）で連結とは？
A1セルに「エクセル」、B2セルに「時短術」と入力されているとします。
[ A1 & B2 ] と入力するとA1セルとB2セルが連結され「エクセル時短術」と表示させることができます。

こちら章では、下記の表を使っていきます。
内容としては、A4セル~C9セルに日付・担当者・売上が記載されている表があります。A2セルとB2セルに入力された売上をC2セルに紐づけたい場合、XLOOKUP関数の検索値と検索範囲をアンド（＆）を使って数式を組むことが出来ます。

では、検索値と検索範囲でアンド（＆）を使ってXLOOKUP関数を組んでいきましょう。
※今回は「2024/1/10」の「村上」の売上を紐づけていきたいと思います。

XLOOKUP関数式

=XLOOKUP(A2&B2,A5:A10&B5:B10,C5:C10)

A2&B2（検索値）	検索する値
A5:A10&B5:B10（検索範囲）	検索する値が含まれる範囲
C5:C10（戻り範囲）	どこの範囲の値を返すか

「2024/1/10」の「村上」の売上を紐づけることが出来ましたね。

ここでのポイントは下記になります。
・検索値と検索範囲で連結する項目は同じ順序にする

アンド（＆）で連結するときのよくない例といい例を置いておきます。
難しくはないので、ぜひ見てください。

よくない例
検索値…[ 日付 & 名前 ]
検索範囲…[ 名前 & 日付 ]
この場合、数式の結果はエラーになります。

いい例
検索値…[ 日付 & 名前 ]
検索範囲…[ 日付 & 名前 ]
アンド（＆）で連結する順序が同じなので、データを紐づけることが出来ます。
※最初に [ 名前 & 日付 ] でも問題ないです。検索値と検索範囲が同じ順序であればOK！

エラーの場合に任意のテキストを返す

VLOOKUP関数でエラーが表示される場合、IFERROR関数でエラーを任意のテキストや空白に変えなければいけません。

一方、XLOOKUP関数でエラーになる場合、IFERROR関数を使わずに任意のテキストや空白を返すことが出来ます。

さっそく数式を見ていきましょう。

XLOOKUP関数式

=XLOOKUP(A2,E5:E9,A5:A9,”データなし“)

A2（検索値）	検索する値
E5:E9（検索範囲）	検索する値が含まれる範囲
A5:A9（戻り範囲）	どこの範囲の値を返すか
データなし（見つからない場合）	値が見つからない場合に返したいテキストがあれば指定