VLOOKUP関数の進化版！
XLOOKUP関数を使いこなそう
公開日
Excelを駆使して日々データ管理やチェック業務をされている皆さん、XLOOKUP関数はもうお使いでしょうか？
XLOOKUP関数は、一言でいうと「より分かりやすく、より使いやすくなったVLOOKUP関数」です。
VLOOKUP関数はデータ検索に大変便利な関数ですが、「式の書き方が覚えづらい」「引数の意味が分かりづらい」と感じる方も少なくないと思います。
今回は、そんなVLOOKUP関数の使いづらさが改良されたXLOOKUP関数について、式の仕組みと書き方をご紹介します。
※XLOOKUP関数は2020年から新たに追加された関数で、 Microsoft 365や、買い切りであればExcel 2019をお使いの方が使用可能です。
それより前のバージョンのExcelでは使用できませんのでご注意ください。
XLOOKUP関数の概要
XLOOKUP関数は、「ある値」を手掛かりに「関連した別の値」を「指定した表」から検索できる関数です。
式の書き方は以下の通りです。
=XLOOKUP(
①手がかりになる値,
②手がかりになる値が存在する列範囲,
③取得したい値が存在する列範囲,
④手がかりが見つからない場合に表示する値,
⑤検索方法,
⑥検索順序
)
括弧の中に設定する値（引数と言います）は6つありますが、4つ目以降は省略可能です。
その中でも5つ目と6つ目は省略して問題ない場合が多いので、基本の使い方としては4つ目までを覚えると良いでしょう。
以下は、XLOOKUP関数の式を使ったサンプルデータです。
式はB列に用意していて、A列の社員IDを手掛かりに、右表から社員の事業所を割り出して表示しています。
B列に用意している実際の式は、以下の通りです。
5つ目と6つ目の引数は省略しています。
=XLOOKUP(A5,$H$5:$H$10,$D$5:$D$10, "×")
引数の順番に仕組みを説明すると、
①A列の社員IDを手掛かりに、
②右表の社員ID列から手掛かりと一致する社員IDを探し出し※、
③一致した社員IDと同じ行の事業所列から事業所の情報を取得してB列に表示※、
④手がかりが右表から見つからなかった場合に×を表示させています。
※今回は、B5セルに用意したXLOOKUP関数の式を、B6セル以降にもコピーします。
そこで、2つ目と3つ目の引数で列の範囲を指定する際に、列番号・行番号の前に＄をつけています。（H5:H10,D5:D10➡$H$5:$H$10,$D$5:$D$10）
これは絶対参照といって、式を別のセルにコピーした際にも、指定した範囲がずれることなく常に同じ範囲を指定できます。
それでは、引数を１つずつ確認して、仕組みを紐解いていきましょう。
記事の最後に練習用のサンプル表を用意していますので、是非チャレンジしてみてください。
豆知識！表のテーブル化でより分かりやすい式に
XLOOKUP関数で使いたい表を事前にテーブル化しておくと、2つ目と3つ目の引数をテーブルの列名で指定できるようになり、式が大変分かりやすくなります。
テーブル化していない場合の式と、表を「従業員リスト」という名前でテーブル化しておいた場合の式を比べてみましょう。
◆テーブル化していない場合
=XLOOKUP(A5,$H$5:$H$10,$D$5:$D$10, "×")
◆テーブル化した場合
=XLOOKUP(A5,従業員リスト[社員ID],従業員リスト[事業所],"×")
$H$5:$H$10,$D$5:$D$10,のように行番号と列番号で範囲指定するよりも、どの列を指定しているか一目見て分かりますね。
別のセルにコピーした時も列範囲がずれないので、絶対参照を気にする必要もなくなります。
テーブル化の仕方については、▼こちらの小技でご紹介しています。
XLOOKUP関数の式に限らず、表をテーブル化することのメリットは沢山あるので是非試してみてください。
1つ目の引数「手がかりになる値」
取得したい値の「手がかりになる値」を選びます。
サンプルでは、A列の「社員ID」を手掛かりに、右表から社員の事業所を取得したいので、まずはA列の「社員IDセル]➡A5を設定します。
2つ目の引数「手がかりになる値が存在する列範囲」
1つ目の引数で指定した「手がかりになる値」が存在する列の範囲を、表の中から選びます。
サンプルでは右表の「社員ID列の範囲」➡ $H$5:$H$10を設定します。
3つ目の引数「取得したい値が存在する列範囲」
表の中から「取得したい値が存在する列範囲」を選びます。
サンプルでは右表の「事業所列の範囲」➡$D$5:$D$10を設定します。
注意！行の高さは揃える
2つ目と3つ目の引数で指定した列範囲は、高さ(先頭行～最終行)が同じである必要があります。
下図のように指定した列範囲の高さが違うとエラーになるのでご注意ください。
ここが便利！VLOOKUPとの違い①
VLOOKUP関数では２つ目の引数で、「手がかりになる値が存在する列」から「取得したい値が存在する列」の範囲をまとめて設定します。
=VLOOKUP(
①手がかりになる値,
②手がかりになる値が存在する列～取得したい値が存在する列範囲,
③取得したい値が存在する列番号,
④検索方法
)
このとき、「手がかりになる値が存在する列」は選んだ範囲の左端にないといけない、というルールがあります。左端にない場合はエラーになります。
そのため、サンプルデータのように「手がかりになる値が存在する列」が右端にあると、VLOOKUP関数は使えません。あらかじめ列の並びを変えておく必要があります。
XLOOKUP関数では、「手がかりになる値が存在する列」と「取得したい値が存在する列」を別々の引数で選びます。このおかげで列の並び順を気にする必要がなくなりました。
また、VLOOKUP関数の３つ目の引数では、「取得したい値が存在する列」が、左端に用意した「手がかりになる値が存在する列」から数えて何列目かを設定する必要がありましたが、XLOOKUP関数ではこの設定も不要になりました。
このおかげで検索範囲内の列が一部非表示になっていて列番号を数え間違える、といった問題も起きなくなりました。
4つ目の引数「手がかりが見つからない場合に表示する値」
1つ目の引数である「手がかりになる値」が、2つ目の引数である「手がかりになる値が存在する列範囲」の中に存在しなかった場合に、セルに表示させたい値を設定します。
サンプルでは、該当がなかった場合にはB列に「×」と表示させるため➡"×"を設定します。
見つからなかった場合にセルを空欄にしたいときは、空のダブルクォーテーション➡""を設定しましょう。
ここが便利！VLOOKUPとの違い②
VLOOKUP関数では「手がかりが見つからない場合に表示する値」の引数がありません。見つからなかった場合には必ずエラー「＃N/A」がセルに表示されてしまい、これを回避するためにはIFERROR関数を組み合わせる必要がありました。
◆VLOOKUP関数でエラー対応したい場合
（VLOOKUP関数に、IFERROR関数を組み合わせる必要がある）
=IFERROR(VLOOKUP(引数①, 引数②, 引数③, 引数④),手がかりが見つからない場合に返す値)
XLOOKUP関数では4つ目の引数でエラー対応ができるようになったため、よりシンプルな式でエラー対応ができるようになりました。
◆XLOOKUP関数でエラー対応したい場合
（4つ目の引数で設定するだけでOK！）
=XLOOKUP(引数①,引数②,引数③,手がかりが見つからない場合に返す値)
5つ目の引数「検索方法」
1つ目の引数である「手がかりとなる値」を、２つ目の引数である「手がかりになる値が存在する列範囲」の中から検索するときに、どのような一致条件で検索するかを設定します。
省略した場合は完全一致検索になります。サンプルでは省略しています。
多くの場合は省略したままで問題ありませんが、設定したい場合は下記をご参照ください。
- 0 指定：完全一致検索。
見つからない場合は、#N/A が返されます。 省略した場合はこの設定になります。 - －1 指定：近似値検索。
見つからない場合は、検索した値より小さくて一番近い値が返されます。
(例：1、5、10の中から3を検索すると1を取得する) - 1 指定： 近似値検索。
見つからない場合は、検索した値より大きくて一番近い値が返されます。
(例：1、5、10の中から3を検索すると5を取得する) - 2 指定： 曖昧検索。
「手がかりになる値」にワイルドカード(＊)を使用して、曖昧検索ができます。
ここが便利！VLOOKUPとの違い③
VLOOKUP関数では、「検索方法」は4つ目の引数で、省略するかTRUEと指定した場合➡近似値検索、FALSEと指定した場合➡完全一致検索になります。
近似値検索とは「該当がなかった場合に一番近い値をとる」検索方法です。近い値も検索対象としたい場合には使えますが、数値ではない単語(氏名など）に対しては意図しない結果が返ってくる可能性があり、扱いに注意が必要です。たいていの場合は、完全一致検索が適していることが多いでしょう。
VLOOKUP関数ではこの引数を省略すると自動的に近似値検索になってしまうので、完全一致検索にするためにはFALSEの指定が必須となります。
一方で、XLOOKUP関数ではこの引数を省略すると自動的に完全一致検索になるので、多くの場合で省略できます。「完全一致検索＝FALSE」といったイメージしづらい仕様に惑わされることもなくなりました。
6つ目の引数「検索順序」
1つ目の引数である「手がかりとなる値」を、２つ目の引数である「手がかりになる値が存在する列範囲」の中から検索するときに、どの方向から検索していくかを設定します。
省略した場合は列の先頭から下方向に検索します。サンプルでは省略しています。
多くの場合は省略したままで問題ありませんが、設定したい場合は下記をご参照ください。
- 1 指定： 先頭の項目から下方向に検索を実行します。
省略した場合はこの設定になります。 - －1 指定： 末尾の項目から上方向に検索を実行します。
- 2 指定：昇順で並んでいる検索範囲に対してバイナリ検索を実行します。
検索範囲を昇順で並べ替えておく必要があります。 - －2 指定：降順で並んでいる検索範囲に対してバイナリ検索を実行します。
検索範囲を降順で並べ替えておく必要があります。
豆知識！検索順序で何が変わる？
どの方向から検索するかによって、検索のスピードが変わります。しかしスピードに差が出てくるのは、検索範囲が何十万、何百万と膨大な場合です。
バイナリ検索というのも、規則的に並んだ表を効率よく検索する手段の一つですが、一般的な事務業務で扱うボリュームであれば、どの方向から検索してもスピードに差はありません。よって、基本的には省略して問題ないでしょう。
XLOOKUP関数の式の仕組みと作り方、お分かりいただけたでしょうか。
最初のセルに式が用意できたら、2行目以降にコピーして表の完成です。
以下に練習用のサンプル表を用意しました。コピペしてエクセルファイルに貼り付けて、左表の事業所の列にXLOOKUP関数の式を追加してみてください。
| 社員ID | 事業所 |
|---|---|
| FMOCHIDA | |
| KNAMIKI | |
| RTAKAHASHI | |
| IISAWA |
| 事業所 | 氏名(漢字) | 氏名(カナ) | 氏名(英) | 社員ID |
|---|---|---|---|---|
| 山梨支店 | 大平 忍 | オオヒラ シノブ | SHINOBU OHIRA | SOHIRA |
| 大阪支店 | 伊沢 依央菜 | イサワ イオナ | IONA ISAWA | IISAWA |
| 大阪支店 | 持田 藤雄 | モチダ フジオ | FUJIO MOCHIDA | FMOCHIDA |
| 愛知支店 | 谷村 静男 | タニムラ シズオ | SHIZUO TANIMURA | STANIMURA |
| 愛知支店 | 野村 弓月 | ノムラ ユヅキ | YUZUKI NOMURA | YNOMURA |
| 愛知支店 | 高橋 理子 | タカハシ リコ | RIKO TAKAHASHI | RTAKAHASHI |
サンプル通りに出来たら、以下のパターンも試してみましょう！
- 手掛かりが見つからない場合に表示する内容を、×ではなく空欄にする
- 右表をテーブル化して、テーブルの列名を使って式を作成する
普段の業務でVLOOKUP関数を多用されている人ほど、XLOOKUP関数の使いやすさを実感できると思います。
まだ使ったことがない方、知っているけれど習慣でVLOOKUP関数を使っている方は、是非XLOOKUP関数を試してみてください。
