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

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

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


エクセル Excel XLOOKUP スピル

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


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

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

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

全体目次

XLOOKUP関数の書式

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

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

検索範囲
必須です。
検索する範囲または配列を指定します。
指定範囲または配列は、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)

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関数は、まさにその為の関数ですので当然ですね。

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

関数名 説明
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で追加されたワークシート関数 関数名 種類と説明 …



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

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


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

VBAでWMIの使い方について|VBA技術解説(4月6日)
列幅・行高をDPI取得しピクセルで指定する|VBA技術解説(4月6日)
行・列の表示・非表示(Hidden)|VBA入門(4月3日)
ユーザーに絶対に停止させたくない場合のVBA設定|VBA技術解説(4月1日)
CharactersプロパティとCharactersオブジェクト|VBA技術解説(3月31日)
指数近似/対数近似/累乗近似(掲載順位とCTR)|エクセル関数超技(3月31日)
練習問題32(連続数値部分を取り出し記号で連結)|VBA練習問題(3月24日)
連続数値部分を取り出し記号で連結|エクセル関数超技(3月24日)
数式バーの高さを数式の行数で自動設定|VBAサンプル集(3月21日)
LET関数(数式で変数を使う)|エクセル入門(3月21日)


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

1.最終行の取得(End,Rows.Count)|VBA入門
2.RangeとCellsの使い方|VBA入門
3.セルのコピー&値の貼り付け(PasteSpecial)|VBA入門
4.マクロって何?VBAって何?|VBA入門
5.Range以外の指定方法(Cells,Rows,Columns)|VBA入門
6.変数宣言のDimとデータ型|VBA入門
7.ひらがな⇔カタカナの変換|エクセル基本操作
8.繰り返し処理(For Next)|VBA入門
9.徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)|エクセル関数超技
10.セルに文字を入れるとは(Range,Value)|VBA入門




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


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



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