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

スポンサードリンク

作成:2020/2/3,更新:2023/3/13

XLOOKUP関数の構文	検索値と一致する行の値をXLOOKUP関数で返す
#スピル! エラーについて
一致モードの使い方	検索モードの使い方
検索条件が2列(複数)ある場合	XLOOKUP関数で縦横に検索する
必要な列だけをXLOOKUP関数で取り出す
テーブルでXLOOKUP関数を使う	テーブルから2つのデータを取り出す
VLOOKUP関数も機能アップしていました

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

XLOOKUP関数はLOOKUP関数系の新しい関数で、VLOOKUP関数やHLOOKUP関数の後継となる関数です。
スピルが使用できる関数となっています。
XLOOKUP関数の使い方を詳細に説明しています。

Excel for Microsoft 365 (サブスクリプション版) でXLOOKUP関数が使えるようになりました。
Office365は2020/4/22からは Microsoft365となりました。
なお、2021/10/5から市販されている Excel2021(永続ライセンス版:Office2021)でも使用できます。

Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。

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

表や範囲から行ごとに情報を検索する　　　 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セルの単価が表示されるように数式を入力しなさい。

	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の解答】
F4セルに　=XLOOKUP(F4,B4:B7,C4:D7,"該当なし")　と入力しています。
XLOOKUP関数の各引数は
検索値：コード(F4)
検索範囲：商品一覧表のコード(B4:B7)
戻り範囲：商品一覧表の商品名(C4:D7) ←複数列を指定しています。
見つからない場合："該当なし"
と設定しています。
戻り範囲に複数列を指定しているので、Spill(スピル)の機能でG4:H4セルに値が返されます(複数のセルに値が返されています)。
XLOOKUP関数の使い方01

検索値が見つからない場合は "該当なし"　が返されます。
VLOOKUP関数では #N/A エラーが返されていましたが、XLOOKUP関数ではエラー処理まで可能になっています。

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

VLOOKUP関数では処理できなかった使い方です。
検索する範囲が返す値の列より右側にある場合です。(検索値が左端列ではない場合)

【問題2】
商品名が既に入力されています。コードが表示されるように数式を入力しなさい。

	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関数では指定できなかった例で、検索値(商品名)が検索する表で戻り値(コード)の右側にある例です。
C4セルには　=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関数の方がかなり簡略な数式になるのがわかります。

#SPIL! エラーについて　　 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))　としました。

XLOOKUP関数で必要な列だけを取り出す　　　 topへ

下図の成績表から必要な列だけを取り出します。

XLOOKUP関数とCHOOSECOLS関数で必要な列のデータを取り出す。

氏名を入力すると国語と社会の得点を取り出します。
D15セルに　=CHOOSECOLS(XLOOKUP(C15,C3:C11,D3:H11),XMATCH(D14:E14,D2:H2))　と入力します。
XLOOKUP関数で氏名にっ該当するデータを取り出し、CHOOSECOLS関数で必要な列のデータを取り出します。
必要な列はXMATCH関数でその位置を検索しています。

CHOOSECOLS関数はExcel for Microsoft365で使用できる関数です。(2022/9/2に使用できるのを確認しました)
指定された行・列の配列を返すCHOOSEROWS関数・CHOOSECOLS関数の使い方

FILTER関数とCHOOSECOLS関数で必要な列のデータを取り出す。

FILTER関数を使うと下の数式のようになります。
=CHOOSECOLS(FILTER(D3:H11,C3:C11=C15),XMATCH(D14:E14,D2:H2))

FILTER関数とCOUNTIF関数で必要な列のデータを取り出す。

FILTER関数とCOUNTIF関数でも同じことが可能です。
=FILTER(FILTER(D3:H11,C3:C11=C15),COUNTIF(D14:E14,D2:H2))

XLOOKUP関数でテーブルを使う例　　　 topへ

B2:H11をテーブルに変換しています。テーブル名は「成績表」としています。
氏名を検索して国語の得点を返す数式は　=XLOOKUP($C15,成績表[氏名],成績表[国語])　となります。

氏名から国語～合計の得点を表示する例です。
数式は　=XLOOKUP(C15,成績表[氏名],成績表[[国語]:[合計]])　としました。
戻り範囲に　成績表[[国語]:[合計]]　と複数列を指定するのがポイントです。

テーブルから2つのデータを取り出したい　　　 topへ

すみません・・・ここでは XLOOKUP関数は使いません。

VLOOKUP関数の時にも2つの条件でデータを取り出したいといった要望がありました。
XLOOKUP関数でも同様な要望があると想像されますが、スピルが使えるようになり、FILTER関数でこれが実現されます。

フィルター関数で2つの条件でデータを取り出した例です。
氏名が「近藤」または「植田」のデータを取り出す例です。
ここで使ったテーブルのテーブル名は「成績表」です。
数式は　=FILTER(成績表,(成績表[氏名]=”近藤")+(成績表[氏名]="植田"))　としました。

FILTER関数は　FILTER関数の使い方をご覧ください。

「スピル」という機能のおかげで、VLOOKUP関数も機能アップしていました。(2020/02/22 記)

従来のVLOOKUP関数で複数の検索値を検索することができてしまいました。(SPILLのおかげです)
D15セル数式は　=VLOOKUP($C$15:$C$18,$C$3:$H$11,COLUMN(B2),FALSE)　としました。
下図では、D15セルをオートフィルで、H15セルまで数式をコピーしています。