XLOOKUP関数は、指定した範囲を下方向に、または横方向に検索して値を返す関数です。
検索関数といえば「VLOOKUP関数」や「HLOOKUP関数」を思い浮かべますが、今回取り上げる「XLOOKUP関数」は、これらの関数の良い部分を受け継ぎ、さには機能を強化した関数になります。
XLOOKUP関数が使えるのは「Excel365」からで、2020/1からマイクロソフトオフィスユーザー向けに提供されています。Excel365以前のOfficeを使用している場合は、今まで通り「VLOOKUP関数」や「HLOOKUP関数」を使うことになります。
XLOOKUP関数の基本的な使い方
対応バージョン：Excel365 2019 2016 2013 2010
XLOOKUP関数の書式です。
=XLOOKUP(検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード])
※カッコ内の「検索値,検索範囲,戻り範囲,[見つからない場合],[一致モード],[検索モード]」を引数といいます。
引数の説明
- 〔検索値〕：検索する値を指定。大文字と小文字は区別しません。全角文字と半角文字は区別します。
- 〔検索範囲〕：検索するセル範囲や配列を指定。
- 〔戻り範囲〕：どこの範囲の値を取り出すか指定。
- 〔[見つからない場合]〕：検索値が見つからなかった場合に表示する「文字列」を指定。省略すると、検索値が見つからなかった場合に「#N/A」が表示されます。
- 〔[一致モード]〕：完全一致か近似一致かを指定。省略すると完全一致が指定されたと見なす。（※1）
- 〔[検索モード]〕：どこから検索するか指定。省略すると先頭の項目から検索する。（※2）
一致モードと検索モードで指定できる値の種類
| 一致モード（※1） | 検索モード（※2） | 値 | 意味 | 値 | 意味 |
|---|---|---|---|
| 0 | 完全一致 | 1 | 先頭から検索 |
| -1 | 完全一致（見つからない場合は、次の小さな値が返される） | -1 | 末尾から逆方向に検索 |
| 1 | 完全一致（見つからない場合は、次の大きな値が返される） | 2 | 昇順の検索範囲をバイナリ検索。予め昇順に並び変える必要がある。 |
| 2 | ワイルドカードを使用して検索 | -2 | 降順の検索範囲をバイナリ検索。予め降順に並び変える必要がある。 |
なんとなく引数の数が多くて難しそうなのですが、XLOOKUP関数で指定する引数の6種類のうち、引数の4番目から最後の引数までは省略しても良いので、それほど面倒な関数ではありません。
ただ、検索する値が見つからなかった場合のことを考慮して、4番目の引数〔見つからなかった場合〕には、何かのメッセージを指定しておく方が良いかも知れません。
XLOOKUP関数の使用例
検索範囲を横方向に検索する
下記のサンプルは、XLOOKUP関数の基本的な使用例です。
B5セル~F9セルまでの在庫表から、商品コードで横方向に検索し「在庫数量」と「在庫金額」を求めています。XLOOKUP関数が使えない環境の場合は「HLOOKUP関数」で同様のことができます。
- B5セル~F9セルまでが在庫表：引数〔検索範囲〕
- B1が引数〔検索値〕
- 戻り範囲（戻り値）として要求する在庫数量は範囲の2行目
- 戻り範囲（戻り値）として要求する在庫金額は範囲の3行目
在庫数量を求める
=XLOOKUP($B$1,$B$5:$F$5,B6:F6)
引数〔検索値〕にはB1セルを指定し、〔検索範囲〕にはB5セル~F9セルまでを指定。
ここでは在庫数量を求めたいので、引数〔戻り範囲〕にはB6~F6セルまでを指定。残りの引数は省略します。
これで、指定した商品コードと一致する在庫数量が検索できます。
在庫金額を検索する場合は、引数〔戻り範囲〕B7~F7セルまでに変更するだけで求めることができます。
在庫金額を求める
=XLOOKUP($B$1,$B$5:$F$5,B7:F7)
検索範囲を縦方向に検索する
A4~F17セルまでの「在庫表」から、XLOOKUP関数を使用して商品コードの一致する商品の在庫数量や在庫金額を検索して表示しています。
なお、XLOOKUP関数が使えない環境の場合は「VLOOKUP関数」を使います。
XLOOKUP関数は検索値が左端でなくてもよい
XLOOKUP関数は検索値が左端である必要はなく、表の中であればどこでも良いのに対して、VLOOKUP関数は検索値（ここでは商品コード）が表の左端になければ検索できない制限がありますので、この表のままではエラーとなってしまいます。
そのため、VLOOKUP関数を使う場合は、検索値が表の左端となるような工夫が必要です。
商品名を求める
=XLOOKUP($B$2,$B$5:$B$17,C5:C17,”該当なし”)
引数〔検索値〕にはB2セルを指定し、〔検索範囲〕にはB5セル~F17セルまでの範囲を指定。
ここでは商品名を求めたいので、引数〔戻り範囲〕にはC5~C17セルまでと、引数〔検索範囲〕と同じ行数で指定。引数〔検索範囲〕と行数が異なるとエラーになりますので注意しましょう。
指定した商品コードをみつけられなかった場合にメッセージを表示したいので、引数〔見つからない場合〕に”該当なし”を指定しています。これで検索値が見つからないときは「該当なし」と表示されます。
次の単価についは、引数〔戻り範囲〕を単価列D5~D17に変更するだけです。
単価を求める
=XLOOKUP($B$2,$B$5:$B$17,D5:D17,”該当なし”)
在庫数量、在庫金額についても引数〔戻り範囲〕を、それぞれの列に変更すれば求めることができます。
XLOOKUP関数で串刺し検索をする
通常、業務では在庫表などは別のシートに保管している場合は殆どでしょう。
ここでは、別シートに保管されている在庫表（シート名は「在庫表」）から、XLOOKUP関数を使い商品コードで検索し商品名や在庫数量などを表示します。
引数の指定方法は、在庫表が別シートのためセル範囲の指定にシート名での修飾が必要なだけで、その他の引数の書き方は、上記の「検索範囲を縦方向に検索する」場合と同じです。
下記のフォームに商品コードを入力すると、別シートに保管されている「在庫表」から、商品名や在庫数量、在庫金額を取り出して表示します。
=XLOOKUP($A$2,在庫表!$B$2:$B$14,在庫表!C2:C14,“該当なし”)
別シートにある表を参照する場合は、そのシートの名前（ここでは「在庫表」）でセル範囲を修飾指定します。書き方は以下の通りです。
在庫表!$B$2:$B$14,在庫表!C2:C14。
スピルに対応している
XLOOKUP関数はスピルに対応した関数です。
スピルとは、数式の計算結果が複数ある場合、数式を入力したセルの隣接したセルにも表示されることをいいます。今までの数式では、数式を入力したセルにしか計算結果が表示されましたが、Excel365からは隣接するセルにまで結果が表示できるスピルが利用できます。
上記のサンプルが「スピル」を使ったものです。
D2~F2セルには数式を入力していません。
商品コードで検索範囲を探し、該当する商品の商品名から在庫数量までを取得しています。
通常であれば（Excel365以前）、商品名を取得する数式には商品名が保存されているセル範囲を指定し、単価を取得するなら単価のセル範囲というように、個別に数式を入力していきます。
しかし、スピルが使えれば商品名を取得すセルに数式を1回入力するだけで、隣接するセルにまで結果を表示することができます。
=XLOOKUP($B$2,$B$5:$B$17,C5:F17,”該当なし”)
商品名を取得する数式の引数〔戻り範囲〕を、商品名、単価、在庫数量、在庫金額までが含まれるようセル範囲「C5~F17」を指定することで、商品名から在庫金額までが一気に表示されます。
まとめ
XLOOKUP関数は、VLOOKUP関数を機能アップした関数で、VLOOKUP関数にはなかった機能が多く盛り込まれています。
XLOOKUP関数とVLOOKUP関数の違い
XLOOKUP関数とVLOOKUP関数の大きく異なるのは以下の点です。
- XLOOKUP関数は検索値が左端でなくてもよい
- XLOOKUP関数はスピルに対応している
- データとなる表の追加・削除などの変更が影響しない。VLOOKUP関数は影響する。（VLOOKUP関数は列番号で、XLOOKUP関数はセル範囲で指定するため）
- XLOOKUP関数は検索値が見つからなかった場合のメッセージを指定できる
以前からのVLOOKUP関数よりも便利な使い勝手になったXLOOKUP関数なので、Excel365が使える環境であればVLOOKUP関数ではなくXLOOKUP関数を使ってみてください。元データの自由度も広がりますし、かつ作業時間の短縮にもなります。
XLOOKUP関数と同じ分類の関数
XLOOKUP関数と同じ系列の関数は以下のページで探せます。
