【Excel】XLOOKUP関数を徹底解説！基本から応用まで完全マスター

ExcelのXLOOKUP関数は、VLOOKUP関数の進化版として2019年に登場した画期的な関数です。表やデータベースから必要な情報を瞬時に検索・抽出できるため、業務効率化に大きく貢献します。

本記事では、関数の基本構造から実務で役立つ応用テクニックまで、具体例を交えて詳しく解説します。

この記事は次のような人におすすめ

表やリストからアイテムを検索してアイテム名や金額を表示したい人
Excelの時短術を身に着けたい人

様々な場面で使用する事が出来る為、絶対に覚えておきたい関数の一つです。

しかし、XLOOKUPの登場は他の関数と比べるとつい最近の事で2019年8月に発表されたばかり。
つまり、この関数を知っていても会社のパソコンで使えない事もありますのでご注意下さい。

XLOOKUP関数とは？
1. XLOOKUP関数の読み方
2. XLOOKUP関数では何ができるの？
VLOOKUP関数とXLOOKUP関数の違い
XLOOKUP関数の使い方
よくあるエラーと解決法
さらなるスキルアップを目指そう！
まとめ
1. 関数一覧
2. エラー値についてのまとめ記事

XLOOKUP関数とは？

XLOOKUP関数の読み方

読み方は「エックスルックアップ」関数です。

XLOOKUP関数では何ができるの？

XLOOKUP関数では、指定した「検索値」をもとに表やデータベースを検索し、対応するデータを抽出することが出来ます。

例えば、商品コードから商品名や価格を検索したり、従業員IDから氏名や部署情報を取得したりする際に活用できます。
職場によって使い方はさまざまですが、在庫管理システムの商品検索や顧客管理表の情報抽出、売上報告書の作成等に使用されることがあります。

サブスクリプション型のOffice365または、Excel2021以降のバージョンで使用することができます。
今お持ちの環境では使用できないという方も、VLOOKUP関数で同様のことが出来ます。
こちらの記事を参考にしてみて下さい。

【Excel】VLOOKUP関数を徹底解説！初心者でも使いこなせる完全ガイド

本記事では、VLOOKUP関数の基本構文から実務で役立つ応用テクニックまで、詳細に解説していきます。エラーが出た時の対処法や他の関数との組み合わせ方など、実践的なノウハウを余すところなくお伝えしていきます。

VLOOKUP関数とXLOOKUP関数の違い

XLOOKUP関数は、従来のVLOOKUP関数の機能をより使いやすく強化した関数です。
従来のVLOOKUP関数と比べて以下のようなメリットがあります。

VLOOKUP関数のように検索範囲が左端に限定されておらず、任意の列を検索範囲に指定できる。
検索値と一致する値がない場合に表示する値を指定出来る。
VLOOKUP関数では、上から下へ順番に検索しか出来ないが、下から上に検索が可能になった。
スピルを使えるようになった。

こんな所でしょうか。特にスピルを使えるようになったのは良いですね！

VLOOKUP関数のように列番号をセル毎に手作業で変えたり、COLUMN関数を使って指定したり、、、といった事をしなくても良いのは非常に楽です。

XLOOKUP関数の使い方

関数の基本構文をマスター

まずはXLOOKUP関数の基本構文を確認しましょう。
基本構文は以下の6つの引数で構成されています。

構文

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

VLOOKUP関数の引数は6つあり、引数が多いように感じますが、必ず指定する引数は3つです。
実際に利用する場面では、以下のような式を入力することになります。

使用例

すべての引数を指定する場合
=XLOOKUP(K2,B2:B9,C2:C9,”値が見つかりません”,0,1)
最小限の引数のみを指定する場合
=XLOOKUP(K2,B2:B9,C2:C9)

注意が必要な点は、検索値と範囲の指定方法です。
関数を入力後、数式を別のセルにコピーする場合、絶対参照（$記号）にしていないと範囲がずれてしまいます。
範囲が変わっても問題ない場合を除き、検索範囲がずれないように絶対参照（$記号）をアルファベットと数字の前に一つずつ入力しておきましょう。
F4キーを押すことで$記号を自動挿入することもできます。

関数の引数について知ろう

第1引数：検索値 ※必須

「検索値」には検索したい値を指定します。

値は文字列で関数に直接”ましゅかぶろぐ”のように記述することも出来ますし、
別のセルに検索する値が入力されているようなケースではセルを指定することも出来ます。

第2引数：検索範囲 ※必須

「検索範囲」には「検索値」を検索する配列か範囲を入力します。

この引数で指定した部分でのみ検索をする為、範囲外の値には反応しません。

指定した範囲が実際の表よりも少し狭くなっており、表示しているデータが間違っていたなんて事が発生しますのでご注意下さい。

第3引数：戻り配列 ※必須

「戻り配列」には検索結果として返したいデータの範囲を指定します。

検索範囲と同じ行数・列数である必要があるので注意してください。
この引数に価格や在庫数など、取得したい情報の列を選択します。

この引数で複数列を指定するとスピルしてくれるため、何度も関数を入力する手間を省くことが出来ます。

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

「見つからない場合」には指定された範囲内で「検索値」が見つからない時に表示する文字を指定できます。

通常、範囲内に検索値が見つからない場合は「#N/A」が返されます。
この文字の代わりに「見つかりません」を表示させる事が可能です。

ただし、「#N/A」ではなく「0」が表示されている場合は状況が異なります。
検索値が範囲内で見つかっていますが、「戻り配列」に指定した列に値がないことが原因です。
空白の代わりに「0」が返されているため、「戻り配列」に指定した範囲を見直してください。

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

「一致モード」では検索する時のオプションを設定する事が出来ます。
普通に検索するだけなら0を指定して問題ありません。

モード	内容
0	完全一致するものを検索。見つからない場合は#N/Aを表示。引数省略時は自動的にこのモード
-1	完全一致するものを検索。見つからない場合は次の小さなアイテムを表示。
1	完全一致するものを検索。見つからない場合は次の大きなアイテムを表示。
2	(*) (?) (~)等のワイルドカードで検索。

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

「検索モード」では「検索値」で指定した引数を検索する時に「検索範囲」をどの順番で検索するか指定する事が出来ます。

モード	内容
1	先頭の項目から検索を実行。引数を省略した場合は自動的にこのモード
-1	末尾の項目から逆方向に検索を実行。
2	昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。
-2	降順で並べ替えられた検索範囲を使用してバイナリ検索を実行。

実際にやってみよう！実践編

ネストしたXLOOKUP関数

部署コードと役職コードの2つの表から情報を検索する場合は、
以下のようにXLOOKUP関数の中で、さらにXLOOKUP関数を使うことで実現することが可能です。

=XLOOKUP(XLOOKUP(B3, 部署マスタ!A:A, 部署マスタ!B:B), 役職マスタ!A:A, 役職マスタ!C:C)

1	=XLOOKUP(XLOOKUP(B3, 部署マスタ!A:A, 部署マスタ!B:B), 役職マスタ!A:A, 役職マスタ!C:C)

最終更新情報の取得

A列に更新日、B列に更新の情報について記載されている表の中から、最新のデータを検索する場合は以下のようにMAX関数と組み合わせて利用します。
MAX関数を利用し、A列内の最新の日付を取得しています。

=XLOOKUP(MAX(A2:A100), A2:A100, B2:B100, , -1)

1	=XLOOKUP(MAX(A2:A100), A2:A100, B2:B100, , -1)

複数の条件で検索

商品カテゴリとサイズの組み合わせで検索する場合は、補助列を作成
=A2&B2 // 商品コード列とサイズ列を結合
この補助列を検索値として使用します。

よくあるエラーと解決法

#N/Aエラーの原因と対策

エラー原因	解決策
検索値が存在しない	引数「見つからない場合」に代替の文字を指定

#VALUE!エラーの原因と対策

エラー原因	解決策
検索範囲と戻り範囲のサイズ不一致	範囲のサイズを揃える

間違った値が返される

原因	解決策
範囲や検索値を絶対参照にしていない	範囲指定時にF4キーで$を追加
引数「戻り配列」に指定した範囲の誤り	正しい範囲に修正

さらなるスキルアップを目指そう！

XLOOKUP関数を学習した後は、更に以下の関数も内容を把握しておきましょう。
用途が似ている関数や、一緒に使うとさらにできることの幅が広がる関数です。

関数名	用途
VLOOKUP	指定した値をデータから検索する
FILTER	条件に合うデータだけを抽出する
MAX	範囲内の最大値を返す
INDEX(MATCH())	関数の組み合わせによるXLOOKUPに類似の検索
LAMBDA	カスタム関数を作成する

まとめ

少し難しい関数ですが、XLOOPUPを使える環境であれば絶対に覚えておきたい関数です。

XLOOKUP関数は、VLOOKUPの弱点を克服した次世代の検索関数です。
3つの必須引数を覚えるだけで、表検索が驚くほど簡単になります。

一緒に絶対参照の適切な使用、スピル機能の活用、エラー処理の組み込みなど、実務で役立つテクニックを習得しましょう。まずは基本形から始めて、徐々に高度な機能を試していくのが上達のコツです。

未だに古いExcelを使用している企業は多くありません。
もしこの関数を使える環境ではない人は、ゆっくり学習を進めましょう。

関数一覧

下記どちらの関数一覧からでも各関数の解説記事へアクセス出来ます。

エラー値についてのまとめ記事

関数でエラーが発生した際に表示される、エラーの種類を以下の記事でまとめています。

【Excel】セルに表示されるエラーの種類と原因、対処方法を解説

この記事ではエクセル関数のエラー値と種類、それぞれの対処方法について解説しています。この記事は次のような人におすすめ表示されているエラーから大体の原因を知りたいエラーの種類と対処方法を知っておきたい無駄なところで業務につまずきたくないエクセ...