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

スポンサードリンク

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

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

XLOOKUP関数はLOOKUP関数系の新しい関数で、VLOOKUP関数やHLOOKUP関数の後継となるものと思われます。
スピルが使用できる関数となっています。
XLOOKUP関数を取り急ぎ紹介していますが、今後このページで詳しい説明を追加していこうと考えています。
Excel for Office365 (Excel2016:サブスクリプション版) でXLOOKUP関数が使えるようになりました。
Office365は2020/4/22からは Microsoft365となりました。
ちなみに、Excel2016(永続ライセンス版),Excel2019(永続ライセンス版)では使用できませんので、ご注意ください。
なお、Office Insiderに参加すると、最新の機能が使用できる環境が得られます。Office Insider になる　をご覧ください。
このページの画像は Microsoft 365 の画像を使用しています。

XLOOKUP関数の構文　　　 topへ

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

引数の [　] はオプションとなり、指定しなくてもOK(省略可)です。

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

XLOOKUP関数で1つの列の値を返す例　　　 topへ

VLOOKUP関数と同じような使い方ですが、検索値が左端列に無くてもOKです。
XLOOKUP関数の[一致モード]は既定で 0 (完全一致)となっています。
XLOOKUP関数は見つからない場合は、#N/A が返されますが、引数の[見つからない場合]を設定すると、エラー回避ができます。次の例では"該当なし"としています。

検索値が左端列の場合
C4セルに　=XLOOKUP(B4:B5,H4:H6,I4:I6,"該当なし")　と入力しています。
検索値：品番(B4:B5)
検索範囲：右側の商品一覧表の品番(H4:H6)　スピル(Spill)の機能が使えるので、セル範囲は相対参照でOKです。VLOOKUP関数では絶対参照にするのとは異なります。
戻り範囲：右側の商品一覧表の商品名(I4:I6)　スピル(Spill)の機能が使えるので、セル範囲は相対参照でOKです。VLOOKUP関数では絶対参照にするのとは異なります。
見つからない場合："該当なし"
と設定しています。
Spill(スピル)の機能でC4とC5セルに値が返されます(複数のセルに値が返されています)。

↓
D4セルには　=XLOOKUP(B4:B5,H4:H6,J4:J6,"")　と入力しました。
- 数式をコピーして利用する場合は、D4セルに　=XLOOKUP($B$4:$B$5,$H$4:$H$6,I4:I6,"該当なし")　と入力します。
  検索値：B4:B5　と　検索範囲：H4:H6　を絶対参照にします。
  
  ↓
  D4セルを選択して、[Ctrl]+[R] でコピーします。または、C4セルをオートフィルでD4セルへコピーします。
  D4セルの数式は　=XLOOKUP($B$4:$B$5,$H$4:$H$6,J4:J6,"該当なし")　となります。
  (戻り範囲：I4:I6　が　J4:J6　となります。)
検索値が左端列ではない場合
VLOOKUP関数では指定できなかった例で、検索値(商品名)が検索する表で戻り値(品番)の右側にある例です。
C4セルには　=XLOOKUP(C4:C5,I4:I6,H4:H6)　と入力されています。
検索値：C4:C5
検索範囲：I4:I6
戻り範囲：H4:H6
と設定しています。
- 従来なら、INDEX関数とMATCH関数で　=INDEX($H$4:$H$6,MATCH(C4,$I$4:$I$6,0))　としていたと思われます。
  XLOOKUP関数の方がかなり簡略な数式になるのがわかります。
- 商品一覧表の検索範囲が昇順に並んでいたらLOOKUP関数でも求めることができるのです。
  ただ・・・「検索範囲が昇順に並んでいたら」がなかなか使用しづらい・・・という欠点があります。

XLOOKUP関数で複数列の値を返す例　　　 topへ

VLOOKUP関数では1つの列の値を返すことができますが、XLOOKUP関数では複数の列の値を返すことができます。
C4セルに　=XLOOKUP(B4,$H$4:$H$6,$I$4:$J$6,"該当なし")　と入力します。
すると、C4:D4セルに値が返されます。
注目は戻り範囲に　I4:J6　と複数列を指定しています。
下のセルへ数式をコピーするために、検索範囲と戻り範囲を絶対参照にしています。

↓
C5セルを選択して、[Ctrl]+[D]で上のセルをコピーします。または、C4セルをオートフィルでC5セルへコピーします。
C5セルの数式は　=XLOOKUP(B5,$H$4:$H$6,$I$4:$J$6,"該当なし")　となります。

#スピル! エラーについて　　 topへ

このような使い方をするときは注意が必要です。
XLOOKUP関数で商品名と単価を返そうとしているのですが、D6セルには既に値「14」が入力されていました。
C6セルに数式　=XLOOKUP(B6,$H$4:$H$6,$I$4:$J$6,"")　を入力すると、#SPILL!(#スピル!)　とエラーが表示されました。

↓
#スピル! とは?　エラーインジケータ(緑色の三角マーク)をクリックすると、説明が表示されます。
「スピル範囲が空白ではありません。」、ヘルプには「このエラーは、こぼれた配列数式のスピル範囲が空白ではない場合に発生します。」と書いてあります。
データを返すところが空白でないといけないというエラーのようです。

↓
D6セルを[Delete]キーでクリアすると、XLOOKUP関数の返り値が表示されました。
数式バーには C6セルの数式が薄く表示されており(ゴーストと呼ぶらしいです)、このセルの値はXLOOKUP関数での結果というのがわかります。

XLOOKUP関数を関数の挿入から使用する例　　　 topへ

XLOOKUP関数を関数の挿入から使う方法です。

関数(数式)を入力するセルを選択して、[関数の挿入]ボタンをクリックします。
関数の挿入ダイアログボックスが表示されます。
関数の分類で「すべて表示」を選択します。
関数名で「XLOOKUP」を選択して、[OK]ボタンをクリックします。
関数の引数ダイアログボックスが表示されます。
検索値に　B4
検索範囲に　$H$4:$H$6　(下のセルにコピーするので絶対参照とします)
戻り範囲に　$I$4:$J$6　(下のセルにコピーするので絶対参照とします)
見つからない場合に　"該当なし"
一致モードは、XLOOKUP関数の既定は完全一致なので省略します。
と入力します。
[OK]ボタンをクリックします。
C4セルに数式　=XLOOKUP(B4,$H$4:$H$6,$I$4:$J$6,"該当なし")　が入力されました。
C5セルを選択して、[Ctrl]+[D]でC4セルの数式をコピーしました。(C4セルを選択して、オートフィルでもOKです)

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

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

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

一致モードが 0 の時の例

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

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

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

一致モードが 1 の時の例

C4セルの数式は評価Bの表を使っています。
数式は　=XLOOKUP(B4:B12,G10:G13,I10:I13,"なし",E3)　としています。
完全に一致する値がないときは、上の値(大きい値)が選ばれます。
つまり、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 と設定しています。
よって、末尾から検索して最初に見つかった「山田」が表示されています。

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セルまで数式をコピーしています。
- XLOOKUP関数では、複数列の値を返すことができるので、
  D15セルに　=XLOOKUP(C15,$C$3:$C$11,$D$3:$H$11)　とします。
  下図では、D15セルをオートフィルで、D18セルまで数式をコピーしています。

検索条件が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へ

下図の成績表から必要な列だけを取り出します。
取り出す列見出しを C14:E14セルに入力します。
C15セルに　=XLOOKUP(C14,$B$2:$H$2,$B$3:$H$11)　と入力します。
氏名の列のデータが表示されました。
C15セルの数式をE15セルまでコピーします。
列見出しを入力した列だけを取り出すことができました。