XLOOKUP関数で範囲や配列を検索する:Excel関数

スポンサードリンク

更新:2024/4/9;作成:2020/2/3

XLOOKUP関数の構文	検索値と一致する行の値をXLOOKUP関数で返す
#スピル! エラーについて
一致モードの使い方	検索モードの使い方
検索条件が2列(複数)ある場合	XLOOKUP関数で縦横に検索する

XLOOKUP関数が利用できるExcelの種類(バージョンについて)

XLOOKUP関数はLOOKUP関数系の新しい関数で、VLOOKUP関数やHLOOKUP関数の後継となる関数です。
スピルが使用できる関数となっています。
Excel for Microsoft 365 (サブスクリプション版) ,Excel2021(永続ライセンス版)でXLOOKUP関数が使えるようになりました。
Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。

	Spill(スピル)	XLOOKUP関数
Excel2016	×	×
Excel2019	×	×
Excel2021	○	○
Excel for Microsoft365	○	○

関連するページ
XLOOKUP関数で必要な列だけを取り出す:Excel関数
 XLOOKUP関数をテーブルで使う:Excel関数

表や範囲から行ごとに情報を検索する　　　 topへ
　エックスルックアップ
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
Excel for Microsoft365 ,Excel2021で使用できます。

XLOOKUP関数の引数の説明　　　 topへ

=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
引数の [　] はオプションとなり、指定しなくてもOK(省略可)です。

検索値	参照する値、セル
検索範囲	検索する配列または範囲
戻り範囲	返す配列または範囲
[見つからない場合]	一致するものが見つからない場合は、[見つからない場合] に指定したテキストを返します。
[一致モード]	一致の種類を指定します
	0	完全一致。(これが既定の設定です) 見つからない場合は、#N/A が返されます。
	-1	完全一致。見つからない場合は、次の小さなアイテムが返されます。
	1	完全一致。見つからない場合は、次の大きなアイテムが返されます。
	2	＊、？、および～が特別な意味を持つワイルドカードになります。ワイルドカードを使った検索が可能になります。
[検索モード]	使用する検索モードを指定します。
	1	(これが既定の設定です)先頭の項目から検索を実行します。
	-1	末尾の項目から逆方向に検索を実行します。
	2	昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。
	-2	降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

検索値と一致する行の値をXLOOKUP関数で返す　　　 topへ

VLOOKUP関数と同じような使い方ですが、検索値が左端列に無くてもOKです。
XLOOKUP関数の[一致モード]は省略すると、既定値の 0とみなされ、完全一致で検索されます。
ちなみに、VLOOKUP関数の引数の検索の型を省略すると、既定値のTRUEとみなされて検索値を超えない最大値が返されます。
完全一致で検索するにはFALSEまたは 0 を指定する必要がありました。
XLOOKUP関数は見つからない場合は、#N/A が返されますが、引数の[見つからない場合]を設定すると、エラーの回避ができます。次の例では"該当なし"としています。
VLOOKUP関数などではIFERROR関数などと組み合わせてエラー処理をする必要がありました。

検索値が左端列の場合(VLOOKUP関数と同じような使い方)

VLOOKUP関数と同じような使い方です。
検索範囲の指定方法が異なります、また戻り範囲に複数列を指定できます。

【問題1】F4セルにコードを入力したら、商品一覧表から検索して、G4セルに商品名、H4セルに単価が表示されるように,
G4セルに数式を入力しなさい。

	B	C	D	E	F	G	H
2	商品一覧
3	コード	商品名	価格		コード	商品名	単価
4	K-01	りんご	250		K-03
5	K-02	みかん	210
6	K-03	バナナ	300
7	K-04	柿	320

【問題1の解答】
G4セルに　=XLOOKUP(F4,B4:B7,C4:D7,"該当なし")　と入力します。
XLOOKUP関数の各引数は
検索値：コード(F4)
検索範囲：商品一覧表のコード(B4:B7)
戻り範囲：商品一覧表の商品名(C4:D7) ←複数列を指定しています。
見つからない場合："該当なし"
と設定しています。
戻り範囲に複数列を指定しているので、Spill(スピル)の機能でG4:H4セルに値が返されます(複数のセルに値が返されています)。
検索値が見つからない場合は "該当なし"　が返されます。
VLOOKUP関数では #N/A エラーが返されていましたが、XLOOKUP関数ではエラー処理まで可能になっています。

検索値が左端列ではない場合(VLOOKUP関数では処理できなかった使い方)

VLOOKUP関数では処理できなかった使い方です。
検索する範囲が返す値の列より右側にある場合です。(検索値が左端列ではない場合)
【問題2】
商品名が既に入力されています。コードが表示されるようにG4セルに数式を入力しなさい。

B C D E F G

2 商品一覧

3 コード商品名価格商品名コード

4 K-01 りんご 250 バナナ

5 K-02 みかん 210

6 K-03 バナナ 300

7 K-04 柿 320

【問題2の解答】
VLOOKUP関数では指定できなかった例で、検索値(商品名)が検索する表で戻り値(コード)の右側にある例です。
G4セルには　=XLOOKUP(F4,C4:C7,B4:B7,"該当なし")　と入力します。
検索値：F4
検索範囲：C4:C7
戻り範囲：B4:B7
見つからない場合："該当なし"
と設定しています。
実は、商品名は昇順に並んでいませんが、XLOOKUP関数は既定で完全一致で検索するので引数の[一致モード]を指定する必要はありません。
以前は、INDEX関数とMATCH関数で　=INDEX(B4:B7,MATCH(F4,C4:C7,0))　としていたと思われます。
商品名が昇順に並んでいないので、MATCH(F4,C4:C7,0)　と完全一致で検索する必要があります。
XLOOKUP関数の方がかなり簡略な数式になるのがわかります。

#SPILL! エラーについて　　 topへ

このような使い方をするときは注意が必要です。
XLOOKUP関数で商品名と単価を返そうとしているのですが、H4セルには既に値「500」が入力されていました。
G4セルに数式　=XLOOKUP(F4,B4:B7,C4:D7,"該当なし")　を入力すると、#SPILL!(#スピル!)　とエラーが表示されました。

↓
#SPILL! とは?　エラーインジケータ(緑色の三角マーク)が表示されているセルを選択し、セルの横の [!]マークをクリックすると、説明のリストが表示されます。
「スピル範囲が空白ではありません。」、ヘルプには「このエラーは、こぼれた配列数式のスピル範囲が空白ではない場合に発生します。」と書いてあります。
データを返すところが空白でないといけないというエラーのようです。
↓
H4セルを[Delete]キーでクリアすると、XLOOKUP関数の返り値が表示されました。
数式バーには H4セルの数式が薄く表示されており(ゴーストと呼ぶらしいです)、このセルの値はXLOOKUP関数での結果というのがわかります。

XLOOKUP関数の一致モードの使い方　　　 topへ

XLOOKUP関数の[一致モード]：一致の種類を指定します

XLOOKUP関数は完全一致で検索されるのが特徴です。

0	完全一致。(これが既定の設定です) 見つからない場合は、#N/A が返されます。
-1	完全一致。見つからない場合は、次の小さなアイテムが返されます。
1	完全一致。見つからない場合は、次の大きなアイテムが返されます。
2	＊、？、および～が特別な意味を持つワイルドカードになります。

一致モードが 0 の時の例

C4セルの数式は　=XLOOKUP(B4:B12,G4:G6,I4:I6,"なし",0)　とします。
検索値と完全一致しないと[見つからない場合]の「なし」が表示されます。
検索範囲に完全一致するものがあるときだけ、値が返されます。
なお、0 は既定値なので省略することができます。
数式は　=XLOOKUP(B4:B12,G4:G6,I4:I6,"なし")　とすることができます。

一致モードが -1 の時の例

見つからない場合は、次の小さなアイテムが返されます。
C4セルの数式は　=XLOOKUP(B4:B12,G4:G6,I4:I6,"なし",-1)　とします。
完全に一致する値がないときは、下の値が選ばれます。
つまり、D7セルの　50　の場合、一致するのがG4:G6に無いので、下の値 40 を選び、その同じ行のI列の値　△　が返されます。
なお、VLOOKUP関数の場合は、=VLOOKUP(B4:B12,G4:I6,3,TRUE)　とします。
なお、VLOOKUP関数は既定値がTRUEナノで省略して、=VLOOKUP(B4:B12,G4:I6,3)　とすることができます。
（この数式は引数をB4:B12として、スピルの機能を使っています。）
XLOOKUP関数の[一致モード]が -1 と同じ結果が得られます。
(VLOOKUP関数では表引きのキー(下図では右の表のG列の値 G4:G6 が昇順になっている必要があります)

一致モードが 1 の時の例

見つからない場合は、次の大きなアイテムが返されます。
C4セルの数式は評価Bの表を使っています。
数式は　=XLOOKUP(B4:B12,G10:G13,I10:I13,"なし",1)　とします。
完全に一致する値がないときは、上の値(大きい値)が選ばれます。
つまり、E7セルの　50　の場合、一致するのがG10:G13に無いので、上の値(大きい値) 70 を選び、その同じ行のI列の値　「良」　が返されます。

表引きのキーが降順の場合

表引きのキーが降順の場合(評価一覧の得点が降順になっています)
結果は上記と同じでした。XLOOKUP関数は完全一致で検索するので検索範囲は昇順でも降順でも関係ないということです。

一致モードが 2 の時の例です

[一致モード]が 2 の場合は、ワイルドカード文字を使った検索が可能になります。
ここでは、該当するデータが複数ある場合に、データの先頭から、末尾からといった[検索モード]を使います。
ワイルドカード文字の使い方は　Excel 2016で関数の検索条件にワイルドカード文字を使う　をご覧ください。

検索モードの使い方

先頭からと、末尾からの検索の方向が設定できます。
さらに、検索時間がかかるような場合(VLOOKUPを多量に使っていたなど)、検索範囲が昇順または降順に並んでいればバイナリ検索で高速検索が可能になります。
従来の順番に検索したものを2分検索で高速化したもののようです。

1	(これが既定の設定です)先頭の項目から検索を実行します。
-1	末尾の項目から逆方向に検索を実行します。
2	昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。
-2	降順で並べ替えられた検索範囲を使用してバイナリ検索を実行します。並べ替えられていない場合、無効な結果が返されます。

検索モードが 1 の時(先頭から)の例です

E4セルには　=XLOOKUP("*田",B4:B9,B4:C9,"なし",2,1)　と入力されています。
末尾が田　というのをワイルドカードを使って　*田　として検索しています。
一致モードが2、検索モードが1 と設定しています。
よって、先頭から検索して最初に見つかった「今田」が表示されています。

検索モードが -1 の時(末尾から)の例です

E4セルには　=XLOOKUP("*田",B4:B9,B4:C9,"なし",2,-1)　と入力されています。
一致モードが2、検索モードが-1 と設定しています。
よって、末尾から検索して最初に見つかった「山田」が表示されています。

検索条件が2列(複数)ある場合　　　 topへ

XLOOKUP関数を使うと作業列は不要になりました。
H4セルには　=XLOOKUP(H2&"_"&H3,C3:C8&"_"&D3:D8,E3:E8)　と入力されています。
「&"_"&」は万が一を考えて、わざわざ付け加えています。無くてもかまわない場合が多いとは思いますが・・・。
VLOOKUP関数の時には、検索条件に2列の値を使うときは作業列を作成する必要がありました。
H4セルの数式は　=VLOOKUP(H2&"_"&H3,B3:E8,4,FALSE)　とします。

XLOOKUP関数で縦横に検索する　　　 topへ

下図のC12セルには、=XLOOKUP(C10,B4:B7,XLOOKUP(C9,C3:E3,C4:E7))　と入力されています。

XLOOKUP(C9,C3:E3,C4:E7)　で　D4:D7　が返されます。
=XLOOKUP(C10,B4:B7,D4:D7)　→　35000　となります。
XLOOUP関数だけで計算できるようになりましたが、従来のように INDEX,MATCH関数で求めることもできます。
数式は　=INDEX(C4:E7,MATCH(C10,B4:B7,0),MATCH(C9,C3:E3,0))　としました。