エクセル入門
XLOOKUP関数(範囲を検索し一致する対応項目を返す)

Excelの初心者向け入門解説
最終更新日:2020-06-02

XLOOKUP関数(範囲を検索し一致する対応項目を返す)


エクセル Excel XLOOKUP スピル

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


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

XLOOKUP関数はスピルで登場した新しい関数です。
2019年にOffice365のExcelに実装された革新的な機能としてスピルがあります。数式を入力したセルから結果があふれて隣接したセルにも出力されるのがスピルです。今までは数式を入れたセルにしか結果を出せませんでしたが、スピルでは隣接するセルにまで結果が表示されます。

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

XLOOKUP関数とスピル入門
2019年Office365に追加された革新的な機能としてスピルがあります。また、少し難しく時々間違った指定をしてしまいがちだったVLOOKUP関数が、より強力で簡単で便利な新関数として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関数の代わり

エクセル Excel XLOOKUP スピル
※なんちゃって個人情報です。

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

VLOOKUP関数
=VLOOKUP(G2,B:E,3,FALSE)
FALSEは0でも良い。
XLOOKUP関数
=XLOOKUP(G2,B:B,D:D)
XLOOKUPでも列指定できます。

HLOOKUP関数の代わり

エクセル Excel XLOOKUP スピル

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

HLOOKUP関数
=HLOOKUP(A7,2:5,3,FALSE)
FALSEは0でも良い。
XLOOKUP関数
=XLOOKUP(A7,2:2,4:4)
XLOOKUPでも行指定できます。

検索列より左の列を返す

エクセル Excel XLOOKUP スピル

G2セルの名前でB列を検索し、対応する都道府県をH2セルに出力します。
VLOOKUP関数は、検索列より左の列を返せなかったので、INDEX+MATCH等で対応する必要がありましたが、XLOOKUP関数は戻り列を指定できるので、検索列より左の列も戻せます。

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関数は、エクセルの関数の中でもとても良く使われる重要な関数ですが、いくつかの問題点があります。以下のような問題点がある場合、VLOOKUP関数を使う事が出来ません。そこで、代替え案を考えてみましょう。

XLOOKUP関数をスピルさせる

横にスピルさせる

エクセル Excel 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の方が分かりやすいと思います。

縦にスピルさせる

エクセル Excel XLOOKUP スピル

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

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

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

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

縦横にスピルさせる

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

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

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

縦横同時にスピルさせる方法
エクセル Excel XLOOKUP スピル
※INDEX関数の戻りは書式設定されないので日付はシリアル値で表示されています。

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

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

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

エクセル Excel XLOOKUP スピル

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 : 完全一致または次に小さい項目

エクセル Excel XLOOKUP スピル
※検索値の順序をわざと崩しています。

=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 : 完全一致または次に大きい項目

エクセル Excel XLOOKUP スピル

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

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

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

エクセル Excel XLOOKUP スピル

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

ワイルドカード文字
(?)半角の疑問符:任意の 1 文字
(*)半角のアスタリスク:任意の文字列
XLOOKUPでワイルドカードを使うことはあまりないと思いますが、ワイルドカードは他の関数でも使いますので覚えておいた方が良いでしょう。

検索モードの使い方

1 : 先頭から末尾へ検索

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

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

エクセル Excel XLOOKUP スピル

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

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

2 : バイナリ検索(昇順で並べ替え)

【奥義】大量データでの高速VLOOKUP
大量データからのVLOOKUPを大量行に設定すると再計算がなかなか終わらなくなります… そんな経験したことがある人は、少なからずいると思います、そんな場合に、高速にVLOOKUPを実行する方法です。以下の表で説明します。※Sheet1は、A列で昇順に並び変えておきます。
ここで説明しているように、大量のVLOOKUPはとても時間がかかります。
これはXLOOKUPが改善されたと言っても、やはり時間がかかります。
検索範囲のどこに検索値があるか分からないのですから、全て探すしかありません。
最初の方で見つかれば良いですが、一番最後の方にあったとしたら・・・

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

エクセル Excel 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関数をネストして戻り列を可変にする

エクセル Excel 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でも当然できます。

エクセル Excel XLOOKUP スピル

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

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

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

計算範囲として使う

エクセル Excel 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でずらす

エクセル Excel XLOOKUP スピル

このような表が良いかどうかは、この際置いときましょう。
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の戻り値が配列ではなくセル範囲になっていることが関係しているように思われます。

エクセル Excel XLOOKUP スピル

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

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

エクセル Excel XLOOKUP スピル

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

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

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

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

範囲を戻す関数について

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

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

こんなところでしょう、とりあえず他には思い当たるものはありません。
ただし、
INDEX関数が複数値を返す場合はOFFSET出来ません。
XLOOKUPは縦スピルている場合はOFFSET出来ません。
INDIRECT関数は、まさにその為の関数ですので当然ですね。

XLOOKUP関数のVBA使用例

スピルって速いの?スピルの速度について|VBA技術解説
Office365を導入する企業や個人で契約する人もかなり増えてきているようです。自然とスピルや新関数を使う事も増えてくるでしょう。今まで範囲内に同じ数式を入れるときは、絶対参照・相対参照を組み合わせて入れなければならず、これを難しく感じていた人も多いでしょう。

VBAでシート関数使用時の配列要素数制限|VBA技術解説
VBAでワークシート関数が使えるのはとても便利です。WorksheetFunctionのシート関数を使う事は多いですが、配列を引数に指定した場合は要素数に制限があります。この制限があることは、ワークシート関数だという事を考えれば仕方ないのかもしれません。

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

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


ワークシート関数一覧

Excelワークシート関数の一覧と解説です、詳細解説ページへのリンクもあります。Excel2007までの関数です。2010以降追加の関数一覧 ワークシート関数の一覧(2010以降) 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 文字列関数…27 日付と時刻の関数…21 情報関数…17 論理関数…7 検索/行列関数…1…

ワークシート関数の一覧(2010以降)
Excelワークシート関数の一覧と解説です。Excel2010以降に追加された関数一覧になります。2007までのワークシート関数一覧ワークシート関数の一覧 引数、関数構文については Excelシートの複雑な計算式を解析するVBAの関数構文 Excel2010で追加されたワークシート関数 Excel2013で追加されたワークシート関数 関数名 種類と説明 …



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

SWITCH関数(複数値での切替)
XLOOKUP関数とスピル入門
スピルについて
FILTER関数(範囲をフィルター処理)
SORT関数、SORTBY関数(範囲を並べ替え)
UNIQUE関数(一意の値)
RANDARRAY関数(ランダム数値)
SEQUENCE関数(連続数値)
XLOOKUP関数(範囲を検索し一致する対応項目を返す)
XMATCH関数(範囲から値を検索し一致する相対位置)
LET関数(数式で変数を使う)


新着記事NEW ・・・新着記事一覧を見る

VBAのインデントについて|VBA技術解説(7月15日)
「VBA Match関数の限界」についての誤解|エクセル雑感(7月15日)
省略可能なVariant引数の参照不可をラップ関数で利用|VBA技術解説(7月12日)
100桁の正の整数値の足し算|エクセル雑感(7月9日)
LSetとユーザー定義型のコピー(100桁の足し算)|VBA技術解説(7月9日)
Variant仮引数のByRefとByValの挙動違い|エクセル雑感(7月5日)
Variant仮引数にRange.Valueを配列で渡す方法|エクセル雑感(7月5日)
Variantの数値型と文字列型の比較|エクセル雑感(7月1日)
VBAのVariant型について|VBA技術解説(6月30日)
VBAのString型の最大文字数について|エクセル雑感(6月20日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.変数宣言のDimとデータ型|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
6.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
7.繰り返し処理(For Next)|VBA入門
8.セルに文字を入れるとは(Range,Value)|VBA入門
9.とにかく書いてみよう(Sub,End Sub)|VBA入門
10.マクロはどこに書くの(VBEの起動)|VBA入門




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


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



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