XLOOKUP関数☆
VLOOKUP関数の性能が良くなった関数として出てきたXLOOKUP関数というのがあります。
指定したものを一覧表から探して、それに対応する一覧表の項目を出すという意味ではVLOOKUP関数と同じです。
でもVLOOKUP関数ではできないこともあって、一覧表の一番左の列に探されるデータ範囲がなきゃいけないというのが一番のできないことなんですけど、XLOOKUP関数はそのできないことを全部できるように生まれた関数です。
XLOOKUP関数はExcel2021以降、またはMicrosoft365で使える関数です。
XLOOOKUP関数とは
下の表で商品名を探して、その番号を調べるとなると、VLOOKUP関数ではできません。
VLOOKUP関数は表の1列目に検索する列になっている必要があるということですが、これは厳密にいえば、ちょっと違っています。この表の例では、一覧表範囲はB列からD列までにあって番号から探せるのは商品と単価です。しかし、一覧表範囲をC列からD列までと指定すると商品名で単価を検索できます。VLOOKUP関数を使う上で、実際にセル上にある一覧表の一部だけを範囲に指定することもできて、検索される範囲や求めるものの列番号を変えることになりますが、この場合は1列目だけとは限りません。
でもこの考え方でも絶対に検索される範囲は探すものの左側になければいけないです。
この表では番号は一番左の列なので、番号の右にある商品や単価で検索して番号を探すということができます。
ごくまれにですが、その逆で、探すものが右側にあって求めるものが左側にある一覧表もありました。
それはVLOOKUP関数が使い物にならないので、ほかの関数を組み合わせてなんとか対応する必要があったり、元の表の列を入れ替える加工する必要がありました。
それをXLOOKUP関数は、列番号ではなく、探す範囲、求める範囲を範囲で指定することでできるようにしました。一覧表の中より探すっていうより、範囲で何番目にあるか探して、求めるもののその番目にあるものを求める、という関数になったのです。
その場合のXLOOKUP関数の使い方は次の通りです。
=XLOOKUP(検索値,検索範囲,戻り範囲)
上の表でみかんを探して、その番号を調べる場合、検索値は文字列のみかん、検索範囲は探すみかんが含まれる範囲のC3からC7のセル範囲、戻り範囲は求める番号の範囲のB3からB7のセル範囲ということになります。
=XLOOKUP(“みかん",C3:C7,B3:B7)
横に大きな表の時、VLOOKUP関数だと何列目か数えるのが大変でしたがその必要もなく、範囲で指定すればいいのでその点、楽になっています。
でも、油断しちゃうとこういう指定もできちゃうんです。
数式バーをよく見ると、検索範囲が3行目から7行目で、戻り範囲が3行目から6行目とセルの数が違うんです。そうすると#VALUE!エラーになっちゃいますので注意してくださいね。
もっとヤバいのは、下のケースでは、検索範囲と戻り範囲で行がずれてるんですが、検索範囲と戻り範囲のセルの数は同じであれば、エラーにはならないで、ずれてる分、ずれた求める値が出ちゃうってことです。これは気づきにくいので、計算式を作った直後には確認するといいと思います。
ちょっと気を付ける必要はありますけど、VLOOKUP関数でできないことができるようになったのは便利になったので良かったと思います！
実は、VLOOKUP関数でできなかったことで、XLOOKUP関数でできるようになったことって、これだけじゃないんです。
見つからないときにエラーを出さないようにもできるようになった
もし検索値が検索範囲にないときには、VLOOKUP関数では「#N/A」になりますが、XLOOKUP関数も同じです。
VLOOKUP関数ではエラーを出さないときにはIFERROR関数を組み合わせる必要がありました。
でもXLOOKUP関数は、上で紹介した入力方法の後に4つ目として、もし見つからなかった場合に表示する値を指定できるようになりました。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合])
上のケースでもしも見つからなかったら「データなし」って表示するには次の計算式になります。
=XLOOKUP(F3,C3:C7,B3:B7,"データなし")
データなしは文字列なので””で囲みます。もし0を出したいときは数字なので0だけでいいですね。
もし、この見つからない場合を指定しないで見つからない場合は#N/Aになります。
一番近い値を探すときでも小さい順に並んでいなくていい
VLOOKUP関数って探す値を一番近いもので探すことができて、それは正確にいうと探している数字が一覧表にある数字を超えなくて一番近いもの、ってことでした。また近似一致の場合は、元にある表が小さい順に並んでいなければいけなかったのです。
いま、XLOOKUP関数の4番目で見つからない場合を指定できるってことだったんですけど、5番目では、完全一致か、完全一致で探して見つからないときに次に近い値を求めるか、次に近い値も範囲の中の数字を超えない次に近いものか、超えて次に近いものかを選べるようになりました。またこの次に近いものは元の表を並べ替えてなくてよくて、どんな順番で書いててもちゃんと見つけます。
これを一致モードって呼びます。一致モードは入れなくてもよくて、入れない場合は完全一致になります。
下の図はXLOOKUP関数を入れている途中で5番目の一致モードを指定しようとしているところです。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード])
5番目の一致モードを指定するけど、4番目の見つからない場合はそのままにしたいっていうときは、見つからない場合のところを空欄にしてカンマが2つ入るような形になりますがそれでもよいです。
下の図で、お買い上げ金額に応じて、どの景品か調べる計算式は次のようになります。景品は9999円のお買い上げなら10000円の下の5000円の景品、10000円のお買い上げだと10000円の景品にします。
=XLOOKUP(F3,D3:D8,C3:C8,,-1)
-1で指定しました。これは、検索範囲の中で検索値を超えないで探すモードです。
この一致モードは4種類で指定します。
そのうちの3つを説明しますね。後の一つはちょっと考え方が独特なので次の項目で説明します。
- 0：完全一致（全く同じもの）
- -1：完全一致または次に小さい項目（全く同じものと範囲の中の数字を超えない一番近いもの）
- 1：完全一致または次に大きい項目（全く同じものと範囲の中の数字を超えて一番近いもの）
だから、一致モードを1にすれば2999円のお買い上げでは2999円を超えて一番近いものになるので3000円の時の景品になるんです。
VLOOKUP関数では、完全一致ではない一番近い値を見つけるモードを「近似一致」といって、昇順で並べてある表じゃないといけませんでした。昇順に並んでないと効率よく検索値以上の値を見つけることができないからです。
XLOOKUP関数は、一番近い値を見つけるモードでも、まずは完全一致で最初から順に探してそこで見つかったら検索終了、最後まで見て一致するものがなければ、モードによって次に大きいか小さい値にするようになってて、近似一致とは呼ばず、「完全一致または次に小さい（大きい）項目」と呼んでいます。
同じ一番近い値を探すってことでも、Excelさんの中の人の動き方がちょっと違うんですよね。
これって大量のデータだったとき、検索値を見つけるスピードはVLOOKUP関数のほうが効率的で早くなることがめっちゃ多くなって、計算が早くなるってことなんですよ。
同じ一番近い値を探す動作でも、名称が違うのはこんなわけがあるのです。
文字の一部で検索する
一致モードを2にすると「ワイルドカード文字との一致」というモードになります。
これは平たく言うと、一部の文字で検索ができるということです。
以下の表の商品でいえば、「ば」なら「ばなな」を、「ご」なら「いちご」を、「ど」なら「ぶどう」を探します。
そのためにはワイルドカードという文字の指定をする必要があります。
ワイルドカードは3種類あって「*」と指定すると0文字以上のなんかの文字列、「?」でなんかの1文字、「~」では「*」「?」の文字を探します。
「*か*」でどこかに「か」がある文字になるんです。例えば「みかん」、あとは「かとりせんこう」とか「奈良のしか」も探します。「*」は0文字以上のなんかの文字なので「なんとか か なんとか」を探すって言えるんですよね。しかもそのなんとかは入ってなくてもいいんです。「*か」で最後が必ず「か」で終わる、「か*」で必ず「か」で始まるってことになります。
「?か?」で「なんかの1文字 か なんかの1文字」を探すので「みかん」をさがします。「やかん」も「ごかい」も「あかく」も、真ん中が「か」の3文字を探します。「?か」で「か」で終わる2文字、「??か」で「か」で終わる3文字を探します。「か?」で「か」で始まる2文字を探します。
これだと「*」と「?」の文字そのものを探せなくなるので、この2文字を探したいときはその文字の前に「~」を指定します。「=??~*??」で2桁同士のかけ算を表します。「=A1*B1」も探しますね。
組み合わせて「?い?*」にすると、3文字の真ん中が「い」で始まる何かを指定できます。
このワイルドカードの検索はVLOOKUP関数ではできていたんです。XLOOKUP関数では完全一致の場合は絶対に同じものとなって、一部を探したいときは検索モードを2にしてワイルドカードにしないといけなくなりました。完全一致では、より厳しく検索するようになったんですね。
ワイルドカードを含んだ文字がセルF3に入っていてそれで検索する場合、次のような計算式になります。
=XLOOKUP(F3,C3:C7,B3:B7,"データなし",2)
ワイルドガード文字をセルに指定するのがめんどいので、こんなふうに検索値にワイルドカード文字を文字列結合して、ワイルドカードを指定しないでも、探したい文字だけ入力すればOKになる計算式にしている人も結構います。頭いいですね！！
=XLOOKUP(”*”&F3&”*”,C3:C7,B3:B7,"データなし",2)
探す方向を指定できる
XLOOKUP関数って超細かく設定できるんですけど。
それが6番目に指定するのの話です。
次の表は受験者ごとのテストの点数を昇順に並べています。
同じ点数の人が何人かいるのがポイントです。
あまり現実的な例ではないんですけど、80点を取った人を求めたいっていうことがあったら、80点の人を本当は3人いるので3人を出さなきゃいけないんですけど、XLOOKUP関数で同じ検索値の人を3人出せないので、そのうちの一人でいいやってなったとき、誰を出しましょうか。
このとき、上から見ていったら80点か最初にでてくるのがかわとうさんです。下から見ていったらくるめさんです。こんな風に上から見るか下から見るかで戻り値がかわるんですよね。
そんな感じで、探す方向を指定するのが最後の6つ目の検索モードです。
次はXLOOKUP関数の6つめを指定しているところです。4種類の数字を入れるんですね。0がない。。。
- 1：先頭から末尾へ検索・（上から下への検索）
- -1：末尾から先頭へ検索・（下から上への検索）
- 2：バイナリ検索（昇順で並べ替え）・（元の表が昇順で並んでいる表）
- ‐2：バイナリ検索（降順に並べ替え）・（元の表が降順で並んでいる表）
ばいなりけんさく？
さとうせんせいに聞いたんですけどよくわからなかったです。多分さとうせんせいもわかってないです。
同じ数字がたくさんあるときに探す方向が違うと違う結果になるのはあるんですけど、そのほかにこの探す方向って探し当てるまでの時間に関係してくるんですよね。たとえば90点の人を探すのに上から探したら90点は下の方にあるのでずっと後になります。そうじゃなくて下から探せば早いですよね。
でも90点の人が実際には探してみないとどこにいるかわからないとき、探す回数を減らしてくテクニックがバイナリ検索です。
バイナリ検索のときは昇順か降順に表が並んでなきゃいけないんですけど、90点って前半にいるのか後半にいるのか半分のところで見てみるんですよ。
前半後半に分けると真ん中のところで80なので後半ってわかります。
そしたらその半分って見ていきます。それを繰り返していって90になるところを見つけます。この表だと2回目で見つかりますね。上から探していくより全然早いです。
でもこの探し方も探したい値が一番上にあることもありますし、そのときだとバイナリ検索より上から探したほうが早くなりますね。
でも、バイナリ検索の動きで考えると、おかしなことも起きてて。昇順で並んだ表の‐2で設定してました。
全部同じ点数にすれば、バイナリ検索の動きだと真ん中のセルの人が出るはずじゃないですか。実際にはそうじゃなくて一番下のセルの人が出たんです。5番目の一致モードを変えても同じで一番下の人が出ました。
全部100点なら昇順で並んでいても降順で並んでいても関係ないなって思って、降順の表の2の設定をすると、今度は一番上のセルの人が出てきました。
バイナリ検索のときで探す値は値がたくさんあるとき、昇順の-2で指定すると同じ値の一番下、降順の2で指定すると同じ値の一番上が見つかった値ってするようです。どっちを出したいのかちゃんとするのであれば2か-2かしっかり決めなきゃですね。
VLOOKUP関数の近似一致で昇順で並んでないといけないのは、効率的に探すためにこのバイナリ検索をしてるからなんですね。XLOOKUP関数では指定しないと上から順に一致するものを探して、一番下まで全部見て見つかんなかったら全部見たうちの完全一致に一番近いものにするって動くので効率的じゃないのです。それを効率的にするために表を昇順か降順で並べ替えてからバイナリ検索モードで指定すれば効率よくできるよってことなんです。
バイナリモードって、よっぽど元の表のデータが長くて、どこにあるのか探すのが大変でXLOOKUP関数の計算で時間がかかってるときに有効で、元の表が数十行くらいならあまり関係ないのかなって思います。
それならせっかく並び順がばらばらでもXLOOKUP関数は検索できるようになってるんだから、表をわざわざ並べ替えしないで、バイナリ検索はお休みしてもらってもいいと思います。
検索モードは、元の表が何百行とかのめっちゃ大きいのじゃなければ、同じ値があったときに、上のものを出したいのか、下のものを出したいのかで決めればいいと思います！
テーブルのデータを元データにしましょう
VLOOKUP関数もそうだったのですが、テーブルのデータを元データにするとよいと思います。範囲で指定する関数なので、テーブルだと列のデータを「テーブル名[項目名]」で設定できるから、とてもわかりやすい計算式にできます。
またXLOOKUP関数の計算式自体もテーブルの中に入ることが多い関数なので、検索値の指定も「[@項目名]」で指定できます。
テーブルでの利用例は、こんな感じになると思います。
左側の青いテーブルが元の表で「商品仕入先」という名前、右のオレンジのテーブルが「仕入」という名前がテーブル名としてついています。
セルH5の仕入先の計算式は次の通りです。
=XLOOKUP([@商品],商品仕入先[商品],商品仕入先[仕入])
検索値と検索範囲の項目名が同じになるので間違えにくい感じがします。
横方向のXLOOKUP関数
VLOOKUP関数は元の表が横に積み重なるとHLOOKUP関数を使うことになります。関数自体が違ってしまうのですね。XLOOKUP関数は横でもXLOOKUP関数です。
下の図の場合のセルD10の仕入先の計算式は次のようになります。
=XLOOKUP([@商品],$C$5:$G$5,$C$3:$G$3)
横方向になると、テーブルにはできないです。セルで参照するので、絶対参照に気を付けましょう。
スピル対応の戻り値範囲
新しい関数なのでFILTER関数などと一緒にスピル関数といわれることもありますが、この関数はスピルに関係する関数というより、従来の行列検索関数のひとつで、VLOOKUP関数と同じくくりのものです。
ただ、戻り値範囲はスピル対応でできていて、1つの計算式で複数の項目のデータを取り出せます。
右の表が元の表です。「取引先」という名前のついているテーブルになっています。左の表の訪問先会社を右の取引先テーブルから探して、住所、担当者、電話番号、メールアドレスを求めています。
これはVLOOKUP関数でもできる表なんですけど、XLOOKUP関数で作ったほうが効率がいいんです。
実はこのときのセルD3に計算式をXLOOKUP関数で作成したら、セルD16まで作った計算式をコピーすればいいんですね。
VLOOKUP関数だと列番号が決まってしまうのでそういうわけにはいきません。
このセルD3に入る計算式は次の通りです。
=XLOOKUP(C3,取引先[会社名],取引先[[住所]:[メールアドレス]])
セルC3は同じ表の訪問先会社で検索値になりますが、これは計算式に対して一つ左のセルなので相対参照で指定します。
検索範囲は取引先テーブルの会社名テーブルです。
次が一番のポイントなんですけど、戻り範囲は取引先テーブルの住所からメールアドレスまで、そのままの並びで戻してもらいます。だから横方向で戻してほしい項目を全部選んでよくて、スピル機能で、複数の項目が1つの計算式だけで求まってくれるのです。
このように、戻ってほしい項目を横方向に全項目分選べば、計算式は下にコピーするだけでいいんです。これはテーブルではなく、セル範囲だったとしても同じです。
まとめ
新しい関数のXLOOKUP関数について説明しました。
VLOOKUP関数との違いも説明しましたが、結果的に同じことをしているのですが、VLOOKUP関数は表全体で考えて戻り値を列で指定するのに対し、XLOOKUP関数は範囲で考えているという違いが一番大きいのかなって思います。
すごく機能がてんこもりの関数なので、なんか複雑な感じもするので、VLOOKUP関数でできるときでXLOOKUP関数でするメリットがない場合は、できるだけ単純な方のVLOOKUP関数で使っていくのがエコでいいんじゃないかなって思います。
関連関数
VLOOKUP関数：ほかの一覧表で一致したものに対する指定した列のデータを求めます
HLOOKUP関数：ほかの横方向の一覧表で一致したものに対する指定した列のデータを求めます