XLOOKUP関数（VLOOKUP関数を拡張した新関数）

公開日：2020-03-14　最終更新日：2024-02-27

XLOOKUP関数（VLOOKUP関数を拡張した新関数）

XLOOKUP関数は、範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。
VLOOKUP関数とHLOOKUP関数の両方の機能を備え、さらに新しい機能まで追加された新関数です。

VLOOKUP関数、HLOOKUP関数の引数は4個でしたが、XLOOKUP関数の引数はなんと6個もあります。
しかし単純な使い方の場合は、省略値が上手く設定されているためVLOOKUPより簡単に使える場合も多くあります。
引数が増えたので難しく見えてしまいますが、かえってVLOOKUP関数より簡単に使える場合が多くあります。

XLOOKUP関数はスピルで登場した新しい関数です。

・スピルとは・スピルの数式例・ゴースト・スピル範囲での独特な挙動について・スピルのエラー表示・スピル範囲演算子・暗黙的なインターセクション演算子・従来のスピルしないエクセルとの互換性についての注意点・スピル関連記事

以下では、かなり詳しく解説しています。
さくっと簡単に使いたいという場合は、以下のページを先にお読みください。

XLOOKUP関数とスピル入門

・スピルとは・XLOOKUP関数の基本・スピルと新関数の練習

ページ内の目次

XLOOKUP関数の書式

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

検索値

必須です。
検索する値を指定します。

VLOOKUP関数は255文字の制限がありましたが、
XLOOKUP関数は文字数制限がなくなりました。
ただし、エクセルの仕様として32768文字以上はセルに入れられません。

検索範囲

必須です。
検索する範囲または配列を指定します。
指定範囲または配列は、1行または1列のみです。
複数行かつ複数列を指定した場合は、#VALUE!のエラーになります。

戻り範囲

必須です。
見つかった場合に戻す範囲または配列を指定します。
検索範囲が縦の場合は、同じ縦数の範囲または配列を指定してください。
検索範囲が横の場合は、同じ横数の範囲または配列を指定してください。
検索範囲と戻り範囲の大きさが違っている場合は、#VALUE!のエラーになります。

見つからない場合

省略可能。
検索値が検索範囲で見つからなかった場合に元す値を指定します。
省略した場合には、見つからなかった場合は#N/A が戻されます。
通常は、省略するか""を指定する使い方が多くなります。

一致モード

省略可能。
どのような状態を一致とするかを指定します。
0 : 完全一致
-1 : 完全一致または次に小さい項目
1 : 完全一致または次に大きい項目
2 : ワイルドカード文字との一致
省略した場合は、0（完全一致）になります。

ワイルドカード文字

(?)半角の疑問符：任意の 1 文字
(*)半角のアスタリスク：任意の文字列
通常の文字として疑問符やアスタリスクを検索する場合は、その文字の前に、"~*" のように半角のチルダ (~) を付けます。

検索モード

省略可能。
検索の向きを指定します。
1 : 先頭から末尾へ検索
-1 : 末尾から先頭へ検索
2 : バイナリ検索（昇順で並べ替え）
-2 :バイナリ検索（降順で並べ替え）
省略した場合は、1 （先頭から末尾）になります。

従来の関数の代わりとして

VLOOKUP関数の代わり

※なんちゃって個人情報です。

G2セルの名前でB列を検索し、対応する年齢をH2セルに出力します。

VLOOKUP関数

=VLOOKUP(G2,B:E,3,FALSE)
FALSEは0でも良い。

XLOOKUP関数

=XLOOKUP(G2,B:B,D:D)
XLOOKUPでも列指定できます。

HLOOKUP関数の代わり

A7セルの名前で2行を検索し、対応する年齢をA8セルに出力します。

HLOOKUP関数

=HLOOKUP(A7,2:5,3,FALSE)
FALSEは0でも良い。

XLOOKUP関数

=XLOOKUP(A7,2:2,4:4)
XLOOKUPでも行指定できます。

検索列より左の列を返す

G2セルの名前でB列を検索し、対応する都道府県をH2セルに出力します。
VLOOKUP関数は検索列より左の列を返せなかったので、INDEX+MATCH等で対応する必要がありましたが、XLOOKUP関数は戻り列を指定できるので、検索列より左の列も戻せます。
（VLOOKUPでも配列を使う事で無理やり左の列を取得できますが、ここでは割愛）

INDEX関数+MATCH関数

=INDEX(A:A,MATCH(G2,B:B,0))

OFFSET関数+MATCH関数

=OFFSET(A1,MATCH(G2,B:B,0)-1,0)

XLOOKUP関数

=XLOOKUP(G2,B:B,A:A)

256文字以上の検索

VLOOKUP関数、HLOOKUP関数は、255文字までしか検索できませんでした。
しかし、XLOOKUP関数は文字数制限がなくなりました。
XLOOKUP関数なら文字数は気にする必要がありません。
今まで仕方なく他の方法でやっていたのなら、間違いなくXLOOKUPで簡単かつ確実な数式にすることができます。

256文字以上の場合の、旧来の代替方法については以下を参照してください。

VLOOKUPを他の関数でやる方法

・VLOOKUP関数の問題点・解決方法・解決方法の具体例・解決方法ごとの長所短所

XLOOKUP関数をスピルさせる

横にスピルさせる

※XLOOKUP関数の戻り（スピル範囲）は書式設定されないので日付はシリアル値で表示されています。

=XLOOKUP(G2,B:B,C:E)

戻り範囲に複数列を指定することで、スピルにより複数列を出力できます。
これはVLOOKUPをスピルさせることでも同じことができます。

=VLOOKUP(G2,B:E,{2,3,4},FALSE)

VLOOKUPの{2,3,4}の部分を他の関数にすることも出来ます。

=VLOOKUP(G2,B:E,SEQUENCE(1,3,2),FALSE)

それでもやはり、XLOOKUPの方が分かりやすいと思います。

縦にスピルさせる

=XLOOKUP(G2:G4,B:B,C:C)

検索範囲に複数セル範囲を指定することで、スピルにより複数行を出力できます。
これはVLOOKUPをスピルさせることでも同じことができます。

=VLOOKUP(G2:G4,B:C,2,FALSE)

これはどちらを使ってもあまり変わらないかもしれません。

縦横にスピルさせる

XLOOKUP関数は縦横にスピルさせられない

=XLOOKUP(G2:G4,B2:B51,C2:E51)

このように、検索範囲と戻り範囲の両方を範囲指定にすると、縦だけスピルして横にスピルしません。
XLOOKUP関数では縦横に同時にスピルさせることはできません。
これについては一番最後で考察しています。

縦横同時にスピルさせる方法

※INDEX関数の戻りは書式設定されないので日付はシリアル値で表示されています。

XLOOKUPを諦めて他の関数を使えば縦横スピルさせて目的の事ができます。

=INDEX(C2:E51,MATCH(G2:G4,B2:B51,0),MATCH(H1:J1,C1:E1,0))

2つのMATCHは、名前の行位置と項目の横位置を取得しています。
INDEXもMATCHも検索値に範囲を指定することでスピルします。
これは、単純にINDEXの行番号と列番号に配列を指定して縦横スピルさせたものになります。
これなら、MATCHで検索できさえすれば自由に条件設定できますし、項目順序の入れ替えも可能です。

さらに指定範囲を列全体にしたり、取得列を連続させることもできます。

=INDEX(C:E,MATCH(G2:G4,B:B,0),SEQUENCE(1,COLUMNS(B:D)))

INDEX+MATCHはとても汎用的で、今後も使われ続けることになると思います。

見つからない場合

検索値が検索範囲で見つからなかった場合、VLOOKUPでは#N/Aのエラーとなります。
これに対応するには、IFERRORを使います。

=IFERROR(VLOOKUP(E2,A:C,2,FALSE),"")

しかし、XLOOKUPでは引数で指定するだけで済みます。

=XLOOKUP(E2,A:A,B:B,"")

これは数式がすっきりして、とても良いです。
この機能だけでもVLOOKUPではなくXLOOKUPを使う理由になりえます。

一致モードの使い方

0 : 完全一致

省略時は0（完全一致）です。
ここまでの使用例が全て完全一致です。

-1 : 完全一致または次に小さい項目

※検索値の順序をわざと崩しています。

=XLOOKUP(D2:D7,A2:A7,B2:B7,,-1)

検索値と完全に一致していればその行になりますが、
一致する値が無かった場合は、検索値を超えない最初に見つかった行になります。
例えば、検索値が5の場合は、5以下で5に最も近い0の行になっています。
「見つからない場合」を指定していないので、0未満の検索値では#N/Aとなります。
これは、VLOOKUPの近似一致と同様の一致方法になります。

=VLOOKUP(D2,A5:B7,2,TRUE)

VLOOKUPとの大きな違いは、検索範囲が昇順に並んでいる必要が無い事です。
上の図のデータは昇順に並んでいないのでVLOOKUPが使えません。
ただし本質的な話としては、大小比較で検索するなら検索範囲は並べ替えておくべきでしょう。
そうしておかないと、結果を見た時に分かりづらくなってしまいます。

1 : 完全一致または次に大きい項目

=XLOOKUP(D2:D7,A2:A7,B2:B7,,1)

-1（完全一致または次に小さい項目）との結果の違いを確認してください。
検索値と完全に一致していればその行になりますが、
一致する値が無かった時は、検索値を超えた最初に見つかった行になります。
例えば、検索値が5の場合は、5以上で5に最も近い10の行になっています。
「見つからない場合」を指定していないので、1000000超の検索値では#N/Aとなります。

2 : ワイルドカード文字との一致

=XLOOKUP(D2:D5,A2:A13,A2:A13,,2)

ワイルドカード文字

(?)半角の疑問符：任意の 1 文字
(*)半角のアスタリスク：任意の文字列

XLOOKUPでワイルドカードを使うことはあまりないと思いますが、ワイルドカードは他の関数でも使いますので覚えておいた方が良いでしょう。

検索モードの使い方

1 : 先頭から末尾へ検索

省略時は1（先頭から末尾）です。
ここまでの使用例が全て1（先頭から末尾）です。
検索値が見つかった最初の行の項目が返されます。

-1 : 末尾から先頭へ検索

=XLOOKUP(D2,A:A,B:B,,,-1)

検索値が検索範囲に複数ある場合は、一番下の行が採用されます。
このように検索値が複数存在し、その一番下の行の項目を取得したい場合に使います。

2 : バイナリ検索（昇順で並べ替え）

【奥義】大量データでの高速VLOOKUP

・高速VLOOKUPに使用するサンプルデータ・高速VLOOKUPの数式・高速VLOOKUPの数式解説・高速VLOOKUPの補足

ここで説明しているように、大量のVLOOKUPはとても時間がかかります。
これはXLOOKUPが改善されたと言っても、やはり時間がかかります。
検索範囲のどこに検索値があるか分からないのですから、全て探すしかありません。
最初の方で見つかれば良いですが、一番最後の方にあったとしたら・・・

検索範囲が昇順に並んでいればバイナリ検索で高速検索が可能になるという事です。
先のページのように、今までは検索値の各行に数式を入れる必要がありましたが、スピルによって先頭行に数式を入れるだけで済んでしまいます。

=XLOOKUP(D2:D200001,A2:A200001,B2:B200001,,,2)

この例であれば、VLOOKUPでもほとんど同じです。

=VLOOKUP(D2:D200001,A2:B200001,2,TRUE)

違いは、XLOOKUPは一致モードを省略しているので完全一致ですが、VLOOKUPの検索方法TRUEは近似一致となります。
従って、上記VLOOKUPをXLOOKUPで書き換えるなら、
=XLOOKUP(D2:D200001,A2:A200001,B2:B200001,,-1,2)
このように一致モードを-1で指定すれば同じ結果が得られます。

XLOOKUPでもVLOOKUPでも、このスピルは非常に高速です。
上図の20万件が一瞬で処理されています。

-2 :バイナリ検索（降順で並べ替え）

1:バイナリ検索（昇順で並べ替え）と並び順が違うだけです。
検索範囲が降順に並んでいる場合は、こちらを指定してください。
これは、VLOOKUPにはなかった機能になります。

XLOOKUP関数をネストして戻り列を可変にする

H1:I1のセル値に応じて、XLOOKUP関数の取得列を変更しています。

H2=XLOOKUP($G$2:$G$4,$B$2:$B$51,XLOOKUP(H1,$A$1:$E$1,$A$2:$E$51))
I2=XLOOKUP($G$2:$G$4,$B$2:$B$51,XLOOKUP(I1,$A$1:$E$1,$A$2:$E$51))
※XLOOKUPは縦横スピルさせられないので、列ごとに数式を入れる必要があります。
※H2をI2にコピーできるように絶対参照を適宜入れています。

内側のXLOOKUPでは、H1セルをA1:E1の範囲で検索し見つかった列を戻します。
=XLOOKUP(H1,$A$1:$E$1,$A$2:$E$51)
エクセル Excel XLOOKUP スピル

外側のXLOOKUPでは、G列の名前でB列を検索し、見つかった行の内側XLOOKUPの範囲を返しています。
少々複雑な数式になっていますが、XLOOKUPだけで実現できている点は良いと思います。
これは、内側のXLOOKUPをFILTERに書き直すことでもできます。

H2=XLOOKUP($G$2:$G$4,$B$2:$B$51,FILTER(A2:E51,A1:E1=H1))
I2=XLOOKUP($G$2:$G$4,$B$2:$B$51,FILTER(B2:F51,B1:F1=I1))

縦横同時スピルでやったように、INDEX+MATCHでも当然できます。

H2=INDEX(C2:E51,MATCH(G2:G4,B2:B51,0),MATCH(H1:I1,C1:E1,0))

この場合は、最後のINDEX+MATCHが一度で済んでいますし、一番良いのではないかと思われます。

XLOOKUP関数の戻りセル範囲を別の関数で使う

計算範囲として使う

=SUM(XLOOKUP(D2,A:A,B:B):XLOOKUP(D3,A:A,B:B,,,-1))

このパターンを使う事は少ないとは思いますが、XLOOKUPならではの機能なので、ぜひ覚えておきたい使い方です。
これはXMACHとINDEXもしくはOFFSETとの組み合わせでもできます。

=SUM(INDEX(B:B,XMATCH(D3,A:A,),0):INDEX(B:B,XMATCH(D4,A:A,,-1),0))
=SUM(OFFSET(B1,XMATCH(D3,A:A,0)-1,0):OFFSET(B1,XMATCH(D4,A:A,0,-1)-1,0))

ここは、MATCHでは出来ないのでXMATCHが必要です。
このように見比べると、XLOOKUPが一番数式がすっきりしていて良さそうに見えます。

OFFSETでずらす

このような表が良いかどうかは、この際置いときましょう。
P2セルの商品でA列を検索して、各月の売上、予算、予算比を取得します。

=OFFSET(XLOOKUP(P2,A:A,C:N),0,0,3)

XLOOKUPの結果をOFFSETで3行に変更しています。
この場合は、OFFSET+MATCHでも同じ事ができます。

=OFFSET(C1:N1,MATCH(P2,A:A,0)-1,0,3)

MATCHはXMATCHでも良いですし、この場合ならどちらでも良いように思います。

XLOOKUP関数が縦横に同時にスピルしないことについて

XLOOKUPが縦横スピルさせられないのは、かなり不満が残ります。
あくまで感想レベルの想像ですが、
上記で縦スピルが優先されていることと、複数列を戻り範囲とした場合のXLOOKUPの戻り値が配列ではなくセル範囲になっていることが関係しているように思われます。

=OFFSET(XLOOKUP(F2,A2:A11,B2:C11),0,1)

このように、XLOOKUPの戻りは連続範囲でもありOFFSETすることができます。
しかし、縦スピルさせた場合は、OFFSET出来ません。

=OFFSET(XLOOKUP(F2:F3,A2:A11,B2:B11),0,1)

当然ですが、XLOOKUPの縦スピルは連続範囲になりません。
そして、このような関数結果は配列で返していると想像できます。
つまり、XLOOKUPの縦スピルは配列を戻し、戻り範囲の複数列は範囲を戻していると考えられます。
この仕様の違いはとても大きいように感じます。

XLOOKUPにおいて、この配列と範囲を同時に縦横スピルさせるのはかなり困難だと思います。
INDEX関数が縦横スピル出来ているのは、複数値を返す場合は配列で返しているからだと考えられます。
したがって、もちろんエクセルのプログラムの組み方次第の話ではありますが、XLOOKUPが今後縦横スピルするようになることはあまり期待できないと思っています。
※ひょっとして、あっと驚くような指定方法で実現できるといったことを僅かに期待してはいますが・・・

ただしXLOOKUPが範囲を返す仕様は、先の例のように別の関数との組み合わせの幅が広がっているので、どちらが良いかは難しいところだと思います。

上記の理由は、INDEX関数と共通したものとも言えます。

=INDEX(範囲,行番号,0)
この数式は、列番号が0なので、範囲の指定された行の参照を返します。
したがってOFFSETすることができます。
しかし、行番号を配列や複数セル参照にした時には横スピルしません。

範囲を戻す関数について

VLOOKUPは値を返しているので、OFFSET出来ません。
配列ではなく範囲を返す関数は極めて少ないです。
関数の戻り結果をOFFSET関数でずらすこができる関数としては、

INDIRECT関数
OFFSET関数
INDEX関数
XLOOKUP関数

主なところではこんなところでしょうか。
ただし、
XLOOKUPは縦スピルしている場合はOFFSET出来ません。
INDIRECT関数は、まさにその為の関数ですので当然ですね。

スピルと新関数の練習

スピルとは：スピル基礎から応用までの問題集 ・・・解答は別ページになっています。

・目次・スピルって何だ編1 ・スピルって何だ編2 ・スピルって何だ編3 ・スピルって何だ編4 ・スピルって何だ編5 ・スピルって何だ編6 ・ハイレツテイスウっておいしいの編1 ・ハイレツテイスウっておいしいの編2 ・イフでウフフ編1 ・イフでウフフ編2 ・イフでウフフ編3 ・かー‼うんと頑張ってみよう編・サムはイフと恋をした編・君はヤマダなのかい編・毎月月末は彼女とデートなんだよ編・SEQUENCE編1 ・SEQUENCE編2 ・FILTER編1 ・FILTER編2 ・SORTBY編1 ・SORTBY編2 ・UNIQUE（そろそろLETも）編・XLOOKUP編1 ・XLOOKUP編2 ・ここまでの総合演習1 ・ここまでの総合演習2 ・ここまでの総合演習3 ・ここまでの総合演習4 ・ここまでの総合演習5 ・ここまでの総合演習6

スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）

・スピルとXLOOKUP関数の練習問題・従来の関数+スピルで数式を作る・ XLOOKUP関数に書き換える・LET関数に書き換える・VBAでユーザー定義関数を作成・スピルと新関数の練習のまとめ

縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）

・2つの1次元配列から、それをクロス結合した結果を返す・A列が同じ行のB列の値を連結して、A列の一意な値とともに出力・名前定義を使わずにLAMBDA関数で再帰する方法・文字列を大文字小文字変換して大小文字の全組み合わせを出力・2つのテーブルの片方しかない行を縦に連結出力・3連単、3連複のフォーメーション買目を全て列挙する・FILTER関数の出力行数・列数を指定する・月が縦で日が横の表を、曜日（7列）で縦に折り返す・文字列内の括弧（）（）の中の文字を取り出して列挙・表内にあるセル内改行を複数行に展開・文字列の中から1文字削除の全パターン出力・クロスABC分析・月利が毎月変動する月複利の計算・レーベンシュタイン距離・文字列を数字と数字以外で分割・縦横スピルしないXLOOKUP代替（MATCH+INDEX,FILTER,CHOOSEROWS）・直積（クロス結合、…

XLOOKUP関数のVBA使用例

スピルって速いの?スピルの速度について｜VBA技術解説

・速度検証に使う関数・速度検証に使うシート・スピルの速度を検証するVBAコード・スピルの速度を検証した結果・スピルの速度の最後に

VBAでシート関数使用時の配列要素数制限｜VBA技術解説

・FILTER関数・SORT関数、SORTBY関数・UNIQUE関数・XLOOKUP関数、HLOOKUP関数、VLOOKUP関数・XMATCH関数、MATCH関数・TRANSPOSE関数・VBAでシート関数使用時の配列要素数制限まとめ

スピルによって新しく追加された関数

関数名	説明
FILTER	フィルターは定義した条件に基づいたデータ範囲です。
SORT	範囲または配列の内容を並べ替えます。
SORTBY	範囲または配列の内容を、対応する範囲または配列の値に基づいて並べ替えます。
UNIQUE	一覧表または範囲内から重複データを削除した一覧を返します。
RANDARRAY	0から1までのランダムな数値の配列を返します。
SEQUENCE	1、2、3、4など、配列内の連続した数値の一覧を生成します。
XLOOKUP	範囲または配列を検索し、見つかった最初の一致に対応する項目を返します。一致が存在しない場合、XLOOKUP は最も近い (概算) 一致を返すことができます。
XMATCH	配列またはセル範囲内の項目の相対的な位置を返します。

ワークシート関数一覧

・文字列関数・日付と時刻の関数・情報関数・論理関数・検索/行列関数・数学/三角関数・統計関数・財務関数・エンジニアリング関数・データベース関数・アドイン/オートメーション・キューブ・Microsoft Office のサポート

ワークシート関数の一覧（2010以降）

・Excel2010で追加されたワークシート関数・Excel2013で追加されたワークシート関数・Excel2016/Excel2019で追加されたワークシート関数・Excel2021で追加されたワークシート関数・Excel2021より後の365で追加されたワークシート関数・Microsoft 365 Insider ・Microsoft「Office のサポート」

同じテーマ「エクセル入門」の記事

UNIQUE関数（一意の値）
RANDARRAY関数（ランダム数値）
SEQUENCE関数（連続数値）
XLOOKUP関数（VLOOKUP関数を拡張した新関数）
XMATCH関数（MATCH関数を拡張した新関数）
LET関数（数式で変数を使う）
スピルと新関数の練習（XLOOKUP関数、LET関数、VBAまで）
スピルとは：スピル基礎から応用までの問題集
 スピルとは：スピル入門の問題と解説
 スピルとは：旧関数でスピルを使う問題と解説
 スピルとは：スピルの新関数を使う問題と解説

アクセスランキング・・・ランキング一覧を見る

1.最終行の取得（End,Rows.Count）｜VBA入門
2.セルのコピー&値の貼り付け（PasteSpecial）｜VBA入門
3.RangeとCellsの使い方｜VBA入門
4.ひらがな⇔カタカナの変換｜エクセル基本操作
5.繰り返し処理（For Next)｜VBA入門
6.変数宣言のDimとデータ型｜VBA入門
7.ブックを閉じる・保存（Close,Save,SaveAs）｜VBA入門
8.並べ替え（Sort）｜VBA入門
9.セルのクリア（Clear,ClearContents）｜VBA入門
10.Findメソッド（Find,FindNext,FindPrevious）｜VBA入門

ホーム
エクセル全般
エクセル入門
XLOOKUP関数（VLOOKUP関数を拡張した新関数）

このサイトがお役に立ちましたら「シェア」「Bookmark」をお願いいたします。

記述には細心の注意をしたつもりですが、
間違いやご指摘がありましたら、「お問い合わせ」からお知らせいただけると幸いです。
掲載のVBAコードは動作を保証するものではなく、あくまでVBA学習のサンプルとして掲載しています。
掲載のVBAコードは自己責任でご使用ください。万一データ破損等の損害が発生しても責任は負いません。

エクセル入門
XLOOKUP関数（VLOOKUP関数を拡張した新関数）