Excelにおける検索機能の一つであるXLOOKUP関数は、初心者から上級者まで幅広いユーザーにとって便利な機能です。
本記事では、XLOOKUP関数の基本的な使い方から高度な応用方法まで徹底解説します。また、似た機能を持つVLOOKUP関数との違いや、どちらの関数を使うべきかについても詳しく解説します。初心者の方でもわかりやすく、かつ上級者の方にも新たな発見があるような内容ですので、ぜひ最後までご一読ください。
チャット一括管理アプリ「OneChat」が今なら初月無料!!
OneChatのダウンロードはこちらから
XLOOKUP関数の基本的な使い方
XLOOKUP関数の基本的な使い方についてみていきましょう。
XLOOKUP関数の構文
XLOOKUP関数の基本的な構文は次の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
基本的な検索の実行方法
XLOOKUP関数の基本的な検索は、構文の各引数に要素を入力することにより実行することができます。
XLOOKUP関数のそれぞれの引数について詳しくみていきましょう。
| 引数 | 詳細 |
| 検索値 | 検索を実行する対象となる値を指定する |
| 検索範囲 | 検索を実行する配列、または範囲を指定する |
| 戻り配列 | 返す値の配列、または範囲を指定する |
| 見つからない場合 | 検索値が見つからなかった場合に返す値を指定する省略した場合は「#N/A」が返される |
| 一致モード | 検索方法を指定する完全一致・近似一致・部分一致が選択できる |
| 検索モード | 検索の方法について指定する先頭から検索・末尾から検索・バイナリ検索が選択できる |
参考：XLOOKUP 関数 – Microsoft サポート
実例：簡単な検索の実行
具体的な例を用いて、XLOOKUP関数の簡単な検索の実行手順についてみていきましょう。
例①：商品コードを用いて商品名の検索を行う
| 行\列 | A | B |
| 1 | 商品コード | 商品名 |
| 2 | 101 | A |
| 3 | 102 | B |
| 4 | 103 | C |
| 5 | 104 | D |
| 6 | 105 | E |
| 7 | 106 | F |
| 8 | 107 | G |
| 9 | 108 | H |
| 10 | 109 | I |
| 行\列 | D | E |
| 1 | 商品コード | 商品名 |
| 2 | 103 |
上記商品データから、指定した商品コード（例：「D2」セルに入力されている「103」）に該当する商品名をXLOOKUP関数を用いて検索します。この場合の数式は次の通りです。
=XLOOKUP(D2, A2:A10, B2:B10)
この数式では、「D2」セルに入力されている商品コードを「A2」セルから「A10」セルの範囲で検索を行います。そこで一致する商品名を「B2」セルから「B10」セルの範囲から返します。
検索結果
| 行\列 | D | E |
| 1 | 商品コード | 商品名 |
| 2 | 103 | C |
例②：社員IDを用いて社員名の検索を行う
| 行\列 | A | B |
| 1 | 社員ID | 社員名 |
| 2 | 1101 | 高橋 彩 |
| 3 | 1102 | 渡辺 美奈 |
| 4 | 1103 | 佐藤 健 |
| 5 | 1104 | 小林 裕 |
| 6 | 1105 | 田中 美咲 |
| 7 | 1106 | 鈴木 翔太 |
| 8 | 1107 | 中村 愛 |
| 9 | 1108 | 山本 健太 |
| 10 | 1109 | 伊藤 亮 |
| 行\列 | D | E |
| 1 | 社員ID | 社員名 |
| 2 | 1104 |
上記社員データから、指定した社員ID（例：「D2」セルに入力されている「1104」）に該当する社員名をXLOOKUP関数を用いて検索します。この場合の数式は次の通りです。
=XLOOKUP(D2, A2:A10, B2:B10)
この数式では、「D2」セルに入力されている社員IDを「A2」セルから「A10」セルの範囲で検索を行います。そこで一致する社員名を「B2」セルから「B10」セルの範囲から返します。
検索結果
| 行\列 | D | E |
| 1 | 社員ID | 社員名 |
| 2 | 1104 | 小林 裕 |
XLOOKUP関数の高度な使い方
XLOOKUP関数の高度な使い方についてみていきましょう。
複数条件での検索
XLOOKUP関数を用いた複数条件での検索について紹介します。
XLOOKUP関数自体は複数条件での検索を直接行うことはできません。ただし、工夫することで複数条件での検索を実現することが可能です。具体例を用いて手順について紹介します。
例：部署と役職の2要素を用いて社員名の検索を行う
| 行\列 | A | B | C |
| 1 | 社員名 | 部署 | 役職 |
| 2 | 高橋 彩 | 営業部 | 課長 |
| 3 | 渡辺 美奈 | 総務部 | 一般職 |
| 4 | 佐藤 健 | IT部 | 主任 |
| 5 | 小林 裕 | 人事部 | 部長 |
| 6 | 田中 美咲 | マーケティング部 | 一般職 |
| 7 | 鈴木 翔太 | 開発部 | 主任 |
| 8 | 中村 愛 | 総務部 | 課長 |
| 9 | 山本 健太 | 営業部 | 一般職 |
| 10 | 伊藤 亮 | 法務部 | 部長 |
上記データから、「部署」と「役職」をもとに社員名を検索します。指定した「部署」と「役職」（例：「E1」セルに入力されている「人事部部長」）に該当する社員名を検索するための手順は次の通りです。
①データにキー列を追加する
上記データの任意の場所（例：D列）に検索の条件となる「部署」と「役職」を結合した要素を入力するための列を追加します。この要素は、「&」を用いて行うと簡単です。
以下の数式を「D2」セルに入力しましょう。
=B2&C2
入力した数式を「D3」セル以下にオートフィルを用いてコピーします。
| 行\列 | A | B | C | D |
| 1 | 社員名 | 部署 | 役職 | 部署＋役職 |
| 2 | 高橋 彩 | 営業部 | 課長 | 営業部課長 |
| 3 | 渡辺 美奈 | 総務部 | 一般職 | 総務部一般職 |
| 4 | 佐藤 健 | IT部 | 主任 | IT部主任 |
| 5 | 小林 裕 | 人事部 | 部長 | 人事部部長 |
| 6 | 田中 美咲 | マーケティング部 | 一般職 | マーケティング部一般職 |
| 7 | 鈴木 翔太 | 開発部 | 主任 | 開発部主任 |
| 8 | 中村 愛 | 総務部 | 課長 | 総務部課長 |
| 9 | 山本 健太 | 営業部 | 一般職 | 営業部一般職 |
| 10 | 伊藤 亮 | 法務部 | 部長 | 法務部部長 |
②XLOOKUP関数を入力する
以下のXLOOKUP関数を検索結果を表示したいセル（例：F2）に入力します。
=XLOOKUP(E2, D2:D10, A2:A10)
検索結果
| 行\列 | E | F |
| 1 | 部署・役職 | 社員名 |
| 2 | 人事部部長 | 小林 裕 |
部分一致検索
XLOOKUP関数では、ワイルドカード（「*」「?」）を用いることで部分一致検索を実行することが可能です。ワイルドカードを文字列に適用することで、適用した文字列の一部に一致するデータを検索することができます。
ワイルドカードの種類と意味は次の通りです。
| ワイルドカード | 詳細 |
| * | 任意の数の文字に一致する |
| ? | 任意の1文字に一致する |
ワイルドカードの使用したXLOOKUP関数での部分一致検索の方法について具体例を用いて解説します。
| 行\列 | A | B |
| 1 | 商品名 | 価格 |
| 2 | Dragonfruit | 120 |
| 3 | Banana | 100 |
| 4 | Cherry | 80 |
| 5 | Pineapple | 180 |
| 6 | Grape | 160 |
①任意の文字列を検索する
「Apple」が文字列に含まれる商品名の価格を検索する場合に任意のセル（例：D1）に入力する数式は次の通りです。
=XLOOKUP(“*Apple*”, A2:A6, B2:B6, “見つかりません”, 2)
検索結果
| 行\列 | D |
| 1 | 180 |
②任意の文字を検索する
「Grap」から始まり、5文字目が任意の文字である商品名の価格を検索する場合に任意のセル（例：D1）に入力する数式は次の通りです。
=XLOOKUP(“Grap?”, A2:A6, B2:B6, “見つかりません”, 2)
検索結果
| 行\列 | D |
| 1 | 160 |
範囲外のデータを扱う方法
XLOOKUP関数を用いて、範囲外（検索値が見つからなかった場合）のデータを扱う方法について解説します。
XLOOKUP関数では、データが範囲外である（指定した検索値が見つからなかった）場合に返す値を引数であらかじめ指定することができます。データが範囲外である場合に返す値の指定場所は、第3引数の「戻り配列」です。データが範囲外の場合は、ここで指定した値が返されます。
以下では、具体例を交えて範囲外のデータを扱う方法について紹介します。
例①：商品コードを用いて商品名の検索を行う
| 行\列 | A | B |
| 1 | 商品コード | 商品名 |
| 2 | 101 | A |
| 3 | 102 | B |
| 4 | 103 | C |
| 5 | 104 | D |
| 6 | 105 | E |
| 7 | 106 | F |
| 8 | 107 | G |
| 9 | 108 | H |
| 10 | 109 | I |
| 行\列 | D | E |
| 1 | 商品コード | 商品名 |
| 2 | 110 |
上記商品データから、指定した商品コード（例：「D2」セルに入力されている「110」）に該当する商品名をXLOOKUP関数を用いて検索します。この場合の数式は次の通りです。
=XLOOKUP(D2, A2:A10, B2:B10, “見つかりません”)
この数式では、「D2」セルに入力されている商品コードを「A2」セルから「A10」セルの範囲で検索を行います。そこで一致する商品名を「B2」セルから「B10」セルの範囲から返します。該当する商品コードが指定した範囲内に存在しない場合は「見つかりません」と返されます。
検索結果
| 行\列 | D | E |
| 1 | 商品コード | 商品名 |
| 2 | 110 | 見つかりません |
例②：社員IDを用いて社員名の検索を行う
| 行\列 | A | B |
| 1 | 社員ID | 社員名 |
| 2 | 1101 | 高橋 彩 |
| 3 | 1102 | 渡辺 美奈 |
| 4 | 1103 | 佐藤 健 |
| 5 | 1104 | 小林 裕 |
| 6 | 1105 | 田中 美咲 |
| 7 | 1106 | 鈴木 翔太 |
| 8 | 1107 | 中村 愛 |
| 9 | 1108 | 山本 健太 |
| 10 | 1109 | 伊藤 亮 |
| 行\列 | D | E |
| 1 | 社員ID | 社員名 |
| 2 | 1110 |
上記社員データから、指定した社員ID（例：「D2」セルに入力されている「1110」）に該当する社員名をXLOOKUP関数を用いて検索します。この場合の数式は次の通りです。
=XLOOKUP(D2, A2:A10, B2:B10, “社員が見つかりません”)
この数式では、「D2」セルに入力されている社員IDを「A2」セルから「A10」セルの範囲で検索を行います。そこで一致する社員名を「B2」セルから「B10」セルの範囲から返します。該当する社員IDが指定した範囲内に存在しない場合は「社員が見つかりません」と返されます。
検索結果
| 行\列 | D | E |
| 1 | 社員ID | 社員名 |
| 2 | 1110 | 社員が見つかりません |
XLOOKUP関数の応用例
XLOOKUP関数の応用例について紹介します。
別シートからのデータ検索
XLOOKUP関数を用いて、異なるシートに入力されているデータを検索する方法について、例を用いて紹介します。
Sheet1
| 行\列 | A |
| 1 | 商品コード |
| 2 | 101 |
| 3 | 102 |
| 4 | 103 |
| 5 | 104 |
| 6 | 105 |
| 7 | 106 |
| 8 | 107 |
| 9 | 108 |
| 10 | 109 |
Sheet2
| 行\列 | A | B |
| 1 | 商品コード | 商品名 |
| 2 | 101 | A |
| 3 | 102 | B |
| 4 | 103 | C |
| 5 | 104 | D |
| 6 | 105 | E |
| 7 | 106 | F |
| 8 | 107 | G |
| 9 | 108 | H |
| 10 | 109 | I |
上記データがSheet1とSheet2にそれぞれ入力されている場合に、Sheet1の商品コードをもとにSheet2の該当する商品名の検索を行います。この場合の数式は次の通りです。
=XLOOKUP(A2, Sheet2!A2:A10, Sheet2!B2:B10, “見つかりません”)
この数式では、Sheet1の「A2」セルに入力されている値（商品コード）を、Sheet2の「A2」セルから「A10」セルの範囲で検索を行います。そこで一致する商品名を「B2」セルから「B10」セルの範囲から返します。該当する商品コードが存在しない場合は、「見つかりません」と返されます。
動的なデータ範囲を使用した検索
Excelで動的なデータ範囲を用いることにより、データの追加や削除が行われた際にも柔軟に対応することができます。下記データを用いて簡単に解説します。
例：商品コードを用いて商品名の検索を行う
Sheet1
| 行\列 | A |
| 1 | 商品コード |
| 2 | 101 |
| 3 | 102 |
| 4 | 103 |
| 5 | 104 |
| 6 | 105 |
| 7 | 106 |
| 8 | 107 |
| 9 | 108 |
| 10 | 109 |
Sheet2
| 行\列 | A | B |
| 1 | 商品コード | 商品名 |
| 2 | 101 | A |
| 3 | 102 | B |
| 4 | 103 | C |
| 5 | 104 | D |
| 6 | 105 | E |
| 7 | 106 | F |
| 8 | 107 | G |
| 9 | 108 | H |
| 10 | 109 | I |
①Excelのテーブルを用いる
Excelのテーブルを用いることで、データに変更が加えられるたびに自動的に更新が行われます。
今回の例の場合、Sheet2に入力済みのデータに対するテーブルを作成することで、Sheet2のデータに変更が加えられても自動的に更新が行われるように設定します。
- Sheet2に入力されているテーブルを作成するデータ範囲を選ぶ
- 「挿入」タブより「テーブル」を選択する
- 作成した「テーブル」に名前をつける（例：商品情報）
Sheet1の商品コード（例：A2）をもとに作成したSheet2のテーブル内の該当する商品名の検索を行います。この場合の数式は次の通りです。
=XLOOKUP(A2, 商品情報[商品コード], 商品情報[商品名], “見つかりません”)
テーブルを作成し、作成したテーブルを引数として入力することにより、Sheet2のデータに変更が加えられても自動的に更新することができます。
②名前付き範囲を用いる
名前付き範囲を用いることで動的範囲を設定することが可能です。この場合、INDEX関数とCOUNTA関数を組み合わせることにより動的範囲を提起することができます。
この例では、Sheet2の商品コード列・商品名列に名前付き範囲を設定することでSheet2のデータに変更が加えられても自動的に更新が行われるように設定します。
名前付き範囲の設定手順は次の通りです。
- 「数式」タブをクリック
- 「名前の管理」を選択
- 「新規作成」をクリック
- 表示されたダイアログボックス内のそれぞれの項目を入力する
今回の例では以下の内容を入力しましょう
Sheet2 商品コード
- 名前：商品コード範囲
- 範囲：Sheet2
- 参照範囲：=Sheet2!$A$2:INDEX(Sheet2!$A:$A, COUNTA(Sheet2!$A:$A))
Sheet2 商品名
- 名前：商品名範囲
- 範囲：Sheet2
- 参照範囲：=Sheet2!$B$2:INDEX(Sheet2!$B:$B, COUNTA(Sheet2!$B:$B))
名前付き範囲が設定できたら、Sheet1の任意のセルに次の数式を入力します。（例：Sheet1の「A2」セルの値を検索する）
=XLOOKUP(A2, 商品コード範囲, 商品名範囲, “見つかりません”)
- A2：Sheet1の検索を行う商品コードが入力されているセル
- 商品コード範囲：動的に定義した商品コードの範囲の名称
- 商品名範囲：動的に定義した商品名の範囲の名称
- “見つかりません”：該当する商品コードがない場合に表示するメッセージ
関連記事：仕事に必携！VLOOKUP関数で劇的に効率アップする方法
XLOOKUP関数と他の関数の組み合わせ
XLOOKUP関数は単体利用でも便利な検索関数ですが、他の関数を組み合わせることにより、さらに高度な分析や操作を実現することが可能となります。
IF関数との組み合わせ
検索結果に基づき条件の評価を行い、異なる結果を返すことができます。
例：指定した社員IDから該当する評価点を検索し、その値に基づいて指定したメッセージを表示する
| 行\列 | A | B |
| 1 | 社員ID | 評価点 |
| 2 | 1101 | 55 |
| 3 | 1102 | 50 |
| 4 | 1103 | 85 |
| 5 | 1104 | 95 |
| 6 | 1105 | 90 |
| 7 | 1106 | 80 |
| 8 | 1107 | 45 |
| 9 | 1108 | 60 |
| 10 | 1109 | 65 |
| 行\列 | C | D |
| 1 | 社員ID | 評価 |
| 2 | 1103 |
上記データをもとに、「C2」セルに入力された社員IDから対応する評価点の検索を行い、それをもとに「D2」セルに評価点が75点以上の場合は「合格」、それ以外の場合は「不合格」のメッセージを表示します。この場合に「D2」セルに入力される数式は次の通りです。
=IF(XLOOKUP(C2, A2:A10, B2:B10) >= 75, “合格”, “不合格”)
上記数式では、「XLOOKUP(C1, A2:A10, B2:B10)」にて「C1」セルに入力された社員IDを「A2」セルから「A10」セルの範囲で検索し、対応する評価点を「B2」セルから「B10」セルの範囲で返します。IF関数を用いて、検索した評価点の値が75以上であるかを評価し、真の場合は「合格」、偽の場合は「不合格」を返します。
結果
| 行\列 | C | D |
| 1 | 社員ID | 評価 |
| 2 | 1103 | 合格 |
XLOOKUP関数とVLOOKUP関数の比較
Excelには、XLOOKUP関数意外にも検索関数が用意されています。検索関数の中でも比較的頻繁に用いられることの多いVLOOKUP関数について、XLOOKUP関数との違いを見ていきましょう。
機能とパフォーマンスの違い
機能とパフォーマンス面から比較してみましょう。
| XLOOKUP | VLOOKUP | |
| 検索方向 | 垂直・水平方向のどちらも検索可能で、検索値の入力場所も問いません。 | 垂直方向のみ検索可能で、検索値が最初の列（一番左端）に存在している必要があります。 |
| 返される列の指定 | 返される列を引数で指定可能です。そのため、列の追加や削除といった変更に柔軟に対応できます。 | 検索範囲内の左端から数えた列番号で指定します。そのため、列の追加や削除といった変更が加えられると結果が変わることがあります。 |
| エラー処理 | 第4引数でエラー発生時の値を指定することができます。 | エラー処理を行う際には、IFERROR関数などを組み合わせる必要があります。 |
| 検索の型 | 完全一致・近似一致・部分一致検索が可能です。 | 完全一致・近似一致検索が可能です。 |
| 複数条件での検索 | 複数条件での検索が可能です。 | 単一条件のみの検索が可能です。 |
| 計算速度 | 効率的なアルゴリズムを使用しており、大規模なデータセットで特にパフォーマンスが向上している。 | 大規模なデータセットで用いた場合は、パフォーマンスが低下する場合がある。 |
どちらの関数を使うべきか？
XLOOKUP関数とVLOOKUP関数、どちらの関数を使うべきでしょうか。
柔軟性やパフォーマンスの高さといった観点から見ると、VLOOKUP関数よりXLOOKUP関数の方が優れていると言えるでしょう。しかし、XLOOKUP関数は使用できるExcelのバージョンが限定されており、他のスプレッドシートソフトウェアとの互換性もない場合が多いです。
XLOOKUP関数の使用がおすすめ
- XLOOKUP関数がサポートされているバージョンを使用している場合
- 検索範囲が垂直方向のみではなく水平方向に広がる場合
- 検索値が表の左端以外に存在する場合
- 複数条件・部分一致検索を行いたい場合
VLOOKUP関数の使用がおすすめ
- 複数人で作成したExcelファイルを共有する場合
- 垂直方向の検索を行いたい場合
- 検索値が検索範囲の左端に常にある場合
よくある質問
XLOOKUP関数に関連するよくある質問について紹介します。
XLOOKUPはエクセルのどのバージョンで使えますか？
XLOOKUP関数が使用できるExcelのバージョンについて紹介します。
| バージョン | サポート状況 |
| Microsoft 365 | ⚪️ |
| Excel 2021 | ⚪️ |
| Excel 2019 | △（※） |
| Excel 2016 | △（※） |
| Excel 2013 | × |
| Excel 2010 | × |
| Excel 2007 | × |
| Excel 2003 | × |
※Excel 2019、およびExcel2016ではXLOOKUP関数を使用することはできませんが、新しいバージョンのExcelを用いて他の人が作成したXLOOKUP関数を含むExcel 2019、およびExcel2016のブックを使用する状況になる場合はあります。
参考：XLOOKUP 関数 – Microsoft サポート
エクセルのXLOOKUPとVLOOKUPの違いは何ですか？
ExcelのXLOOKUP関数とVLOOKUP関数の違いについてみていきましょう。
| XLOOKUP | VLOOKUP | |
| 検索方向 | 垂直・水平方向のどちらも検索可能で、検索値の入力場所も問いません。 | 垂直方向のみ検索可能で、検索値が最初の列（一番左端）に存在している必要があります。 |
| 書式 | =XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード]) | =VLOOKUP(検索値, 範囲, 列番号, [検索の型]) |
| 返される列の指定 | 返される列を引数で指定可能です。そのため、列の追加や削除といった変更に柔軟に対応できます。 | 検索範囲内の左端から数えた列番号で指定します。そのため、列の追加や削除といった変更が加えられると結果が変わることがあります。 |
| エラー処理 | 第4引数でエラー発生時の値を指定することができます。 | エラー処理を行う際には、IFERROR関数などを組み合わせる必要があります。 |
| 検索の型 | 完全一致・近似一致・部分一致検索が可能です。 | 完全一致・近似一致検索が可能です。 |
| 複数条件での検索 | 複数条件での検索が可能です。 | 単一条件のみの検索が可能です。 |
| 計算速度 | 効率的なアルゴリズムを使用しており、大規模なデータセットで特にパフォーマンスが向上している。 | 大規模なデータセットで用いた場合は、パフォーマンスが低下する場合がある。 |
参考：VLOOKUP 関数 – Microsoft サポート
XLOOKUPとは何ですか？
XLOOKUPとは、Microsoft 365、およびExcel 2021以降のバージョンで追加されたExcelの検索関数の一つです。指定した値をデータの範囲内から 検索し、その値に関連するデータを返す際に用いられます。従来のExcelの検索関数であるVLOOKUP関数やHLOOKUP関数に存在した制約を克服し、多数の機能や柔軟性を備えています。
XLOOKUP関数の構文は次の通りです。
=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])
関連記事：効率的なデータ検索が可能！ExcelのVLOOKUP・HLOOKUP・XLOOKUPマスター術
まとめ
今回は、XLOOKUP関数に関して基本的な使い方から高度な応用方法まで解説しました。
XLOOKUP関数は従来のVLOOKUP関数やHLOOKUP関数に存在した制約がなくなり、様々な機能や柔軟性が備わっている便利な関数です。
Excel Lookup関数に関する重要用語
| 用語 | 説明 |
| ワイルドカード | 部分一致検索を実行する際に用いられる特殊文字で、XLOOKUP関数ではワイルドカードを用いた検索が行える。 |
| 動的範囲 | データの追加や削除といった変更に応じて自動的に調整が行われる範囲のことを指す。動的範囲を用いることにより、データに変動が生じても正確な検索結果を返すことができる。 |
