この記事ではXLOOKUPについて解説しています。
- 表やリストからアイテムを検索してアイテム名や金額を表示したい人
- Excelの時短術を身に着けたい人
様々な場面で使用する事が出来る為、絶対に覚えておきたい関数の一つです。
しかし、XLOOKUPの登場は他の関数と比べるとつい最近の事で2019年8月に発表されたばかり。
つまり、この関数を知っていても会社のパソコンで使えない事もありますのでご注意下さい。
サブスクリプション型のOffice365または、Excel2021以降のバージョンで使用する事ができます。
XLOOKUP（えっくす るっくあっぷ）関数とは？
XLOOKUP関数では表や範囲から条件に一致した行の情報を取得する事が出来ます。
商品番号で検索して商品名や値段を表示したり、社員番号を検索して社員の情報を表示することが可能です。
残念ながら使えないという方は、VLOOKUP関数でも似たような事が出来ますのでこちらの記事を参考にしてみて下さい。
XLOOKUP関数は見つからなかった時の値も指定する事が出来ますが、VLOOKUP関数でも他の関数と組み合わせる事で指定する事が出来ます。
VLOOKUP関数とXLOOKUP関数の違い
機能を見るとほとんど同じように見えますが、実は細かい部分が少し違います
- 検索範囲と一致した時に表示する範囲を別々に指定するようになった。
- 見つからない時に表示する値を指定出来る。
- 一致モードでVLOOKUP関数よりも検索について細かく設定出来るようになった。
- 検索の順番をいじれるようになった。
- スピルを使えるようになった。
こんな所でしょうか。特にスピルを使えるようになったのは良いですね！
VLOOKUP関数のように列番号をセル毎に手作業で変えたり、COLUMN関数を使って指定したり、、、といった事をしなくても良いのは非常に楽です。
XLOOKUP関数の使い方
まずはXLOOKUP関数の式をチェックしましょう！！
=XLOOKUP( 検索値 , 検索範囲 , 戻り配列 , [見つからない場合] , [一致モード] , [検索モード] )
やたらと引数が多いように感じますが、必須なのは3つだけです。
「一致モード」と「検索モード」は使う場面が限られてくると思いますので、引数の数だけで言えばVLOOKUPと同じような感じだと思って大丈夫でしょう。
実際に使う時はこんな感じで入力します。
=XLOOKUP(K2,B2:B9,C2:C9,”見つかりません”,0,1) ※記述する必要の無い引数も含めています。
=XLOOKUP(K2,B2:B9,C2:C9,”見つかりません”) ※こちらは省いたもの
=XLOOKUP(K2,B2:B9,C2:C9) ※省略出来る引数を全て省略した形
検索値 ※必須
「検索値」には検索したい値を指定します。
値は文字列で関数に直接”ましゅかぶろぐ”のように記述することも出来ますし、
別のセルに検索する値が入力されているようなケースではセルを指定することも出来ます。
検索範囲 ※必須
「検索範囲」には「検索値」を検索する配列か範囲を入力します。
この引数で指定した部分でのみ検索をする為、範囲外の値には反応しません。
指定した範囲が実際の表よりも少し狭くなっており、表示しているデータが間違っていたなんて事が発生しますのでご注意下さい。
戻り配列 ※必須
ここまでの引数だけで「検索範囲」から「検索値」を検索する事ができますね。
ここからは一致した後、どうするかを指定していきます。
「戻り配列」には検索値が一致した行の、どの列に入力されている項目を表示するかを指定します。
商品名を表示したければ商品名の列を選びますし、値段を表示したければ値段の列を選びましょう。
ちなみにここで複数列を指定しているとスピルしてくれます！
見つからない場合 ※省略可
この引数には指定された範囲に検索対象が無い場合に表示する文字を決める事が出来ます。
見つからない場合は”見つかりません”のような文字を表示させる事が可能になる訳ですね。
ただし商品番号を検索して値段を表示しようとしているようなケースで、検索対象は表にあるが値段が未入力だったという事もありますよね。
この場合は検索対象が見つかっているので、この引数に設定した文字は表示されず「0」が表示されます。
一致モード ※省略可
「一致モード」では検索する時のオプションを設定する事が出来ます。
普通に検索するだけなら0を指定して問題ありません。
| モード | 内容 |
|---|---|
| 0 | 完全一致するものを検索。見つからない場合は#N/Aを表示。引数を省略した場合は自動的にこのモード |
| -1 | 完全一致するものを検索。見つからない場合は次の小さなアイテムを表示。 |
| 1 | 完全一致するものを検索。見つからない場合は次の大きなアイテムを表示。 |
| 2 | (*) (?) (~)等のワイルドカードで検索。 |
検索モード ※省略可
「検索モード」では「検索値」で指定した引数を検索する時に「検索範囲」をどの順番で検索するか指定する事が出来ます。
| モード | 内容 |
|---|---|
| 1 | 先頭の項目から検索を実行。引数を省略した場合は自動的にこのモード |
| -1 | 末尾の項目から逆方向に検索を実行。 |
| 2 | 昇順で並べ替えられた検索範囲を使用してバイナリ検索を実行。 |
| -2 | 降順で並べ替えられた検索範囲を使用してバイナリ検索を実行。 |
まとめ
少し難しい関数ですが、XLOOPUPを使える環境であれば絶対に覚えておきたい関数です。
VLOOKUPを理解している方でも最初は少し混乱するかもしれません。
常に最新のExcelを使用している企業は多くありません。
もしこの関数を使える環境ではない人は急いで覚えないでも良いでしょう。
へー！こんなのあるんだ！くらいの認識で、実際に使えるようになったら改めて使い方を確認するくらいで問題ありません。
関数一覧
下記どちらの関数一覧からでも各関数の解説記事へアクセス出来ます。
エラー値についてのまとめ記事
コメント