XLOOKUP 応用編：複数列一括返却・高速化・後ろから検索
📚 XLOOKUPシリーズ
【1】複数列を1本の数式で一気に返す（スピル展開）
通常の検索関数は「1つの値を返す」のが基本です。しかしXLOOKUPは、 第3引数（戻り配列）に複数列の範囲を指定するだけで、 ヒットした行の複数列を横並びに一気に展開（スピル）してくれます。
| F | G | H | I | |
| 1 | 社員ID | 氏名 | 部署 | 役職 |
| 2 | 1001 | 田中 太郎 | 営業部 | 主任 |
| 3 | 1002 | 鈴木 花子 | 総務部 | 係長 |
| 4 | 1003 | 佐藤 健 | 開発部 | リーダー |
| A | B | C | D | |
| 1 | 入力（社員ID） | 氏名 | 部署 | 役職 |
| 2 | 1001 | =XLOOKUP(A2, F:F, G:I, "未登録") | （空） | （空） |
| A | B | C | D | |
| 入力セル（A2） | 結果① 氏名 | 結果② 部署 | 結果③ 役職 | |
| 1 | 入力（社員ID） | 氏名 | 部署 | 役職 |
| 2 | 1001 | 田中 太郎 | 営業部 | 主任 |
数式 B2（青枠）が起点。 C2・D2 は スピル（点線枠）で自動展開。
検索IDを変えると 3列すべてが連動して更新される。
| A | B | C | D | |
| 1 | 入力（社員ID） | 氏名 | 部署 | 役職 |
| 2 | 1001 | =INDEX(G:G,MATCH(A2,F:F,0)) | =INDEX(H:H,MATCH(A2,F:F,0)) | =INDEX(I:I,MATCH(A2,F:F,0)) |
MATCH(A2, F:F, 0) の部分が 3セルすべてに重複して登場する。検索列（F列）を変更する際は 3か所すべてを修正しなければならない。
| 比較項目 | XLOOKUP | INDEX+MATCH |
|---|---|---|
| 必要な数式の本数 | ✅ 1本 | ⚠️ 3本（列数分） |
| 修正が必要な箇所 | ✅ 1か所 | ⚠️ 3か所（列数分） |
| MATCH部分の重複 | ✅ なし | ⚠️ 3回重複 |
| 列追加時の対応 | ✅ 戻り配列の範囲を広げるだけ | ⚠️ 数式をもう1本追加が必要 |
| ⚡ 処理速度（大量データ時） | ✅ 高速 検索は1回のみ。複数列の返却は展開処理で完結。 |
⚠️ 遅くなりやすい MATCHが列数分だけ独立して実行される。 |
XLOOKUPの戻り配列は連続した範囲である必要があります。「氏名（G列）と役職（I列）だけ」のように 列が飛んでいるケースでは、
CHOOSECOLS 関数と組み合わせることで対応できます。→ CHOOSECOLSの詳細はこちら
【2】第5引数・第6引数：一致モードと検索モード
XLOOKUPには省略可能な第5・第6引数があり、検索の動作を細かく制御できます。
通常は省略（完全一致・先頭から線形検索）で問題ありませんが、
データの特性に応じて使い分けると大幅な高速化も可能です。
第5引数：一致モード（match_mode）
| 値 | 意味 |
|---|---|
| 0 | 完全一致（既定） ― 通常はこれを使う。一致しなければ #N/A または「見つからない場合」の値を返す。 |
| -1 | 完全一致、なければ次に小さい値 ― 価格帯テーブルなどの近似検索に |
| 1 | 完全一致、なければ次に大きい値 ― 同上（上方向の近似） |
| 2 | ワイルドカード一致 ― *（任意の文字列）?（任意の1文字）~（エスケープ）が使える |
第6引数：検索モード（search_mode）
| 値 | 意味 |
|---|---|
| 1 | 先頭から末尾へ線形検索（既定） ― データ量が多いほど時間がかかる |
| -1 | 末尾から先頭へ線形検索 ― 最新の履歴など「最後のヒット」を取得したいときに |
| 2 |
⚡ バイナリ検索（昇順ソート済みデータ向け） データが昇順に並んでいる場合に使用。大量データで劇的に高速化できる。 |
| -2 |
⚡ バイナリ検索（降順ソート済みデータ向け） データが降順に並んでいる場合に使用。同様に高速。 |
VLOOKUPの完全一致（第4引数
FALSE）は「線形検索」のみ。高速なバイナリ検索（TRUE）は
「近似一致」になってしまい、見つからなかった場合に勝手に最近傍の値を返すという致命的な仕様があります。一方、XLOOKUPは「完全一致」と「バイナリ検索」を同時に両立できます。
数万件のソート済みマスタに対して：
第5引数 0（完全一致）、第6引数 2（バイナリ検索・昇順）。D列が昇順ソート済みであることが前提。
→ 詳細な速度計測は 速度カテゴリ で解説。
バイナリ検索の最大のリスクは「手動でソートするのを忘れた・誰かがデータを追加してソート順が崩れた」というヒューマンエラーです。
Excel 2021 / 2024 / Microsoft 365 などでは SORT関数・SORTBY関数 を使うことで、「常に昇順に並べた状態のデータ」を関数が動的に作り出し、 そこにXLOOKUPのバイナリ検索をかけるという組み合わせが可能になります。
【パターン①】SORT でキー列を昇順に並べてからXLOOKUP
元データ（A:B 列）がランダム順でも、SORT で並べ直した配列に対してXLOOKUPを適用する。
SORT(A2:A1000)：A列を昇順に並べた配列（検索配列）
SORTBY(B2:B1000, A2:A1000)：B列をA列の並び順に合わせた配列（戻り配列）
→ 元データのソート順に関わらず、常に正しくバイナリ検索できる。
SORTBY(A2:C1000, A2:A1000, 1)：A〜C列全体をA列の昇順で並べた配列を生成
CHOOSECOLS(sorted, 1)：並べ替え済み配列の1列目（検索キー）
CHOOSECOLS(sorted, 2, 3)：並べ替え済み配列の2・3列目（戻り配列）
LET で配列を一度だけ計算して再利用 → 計算コストを最小化
- 元データのソート状態に関わらず常に正しい結果が得られる（ヒューマンエラー排除）
- バイナリ検索の劇的な高速化をそのまま享受できる
- データ追加・更新が発生しても数式が自動で対応する
LETと組み合わせることでソート計算の重複を防げる
SORT/SORTBY を数式内に直接ネストすると、ブックが再計算されるたびに配列全体を並べ直します。 通常のデータ量では体感できませんが、50万行規模になって初めて速度差が感じられるようになるイメージです。
✅ 実践的な解決策：作業シートにSORT済みデータを「1回だけ」出力しておく
別シート（例：
作業シート）の空きセル（例：A1）に、一度だけSORTBYの結果を出力しておきます。
/* 作業シートのA1セルに入力（1回だけ）*/
作業!A1#（スピル範囲）にソート済みのデータが展開されます。あとは各シートのXLOOKUPからそのスピル範囲を参照するだけです：
XLOOKUPのバイナリ検索は、すでに整列済みのスピル範囲を読むだけなので高速かつ正確。
元データに追記・変更があれば作業シートが自動更新されるため、手動でソートし直す手間もありません。
→ SORT / SORTBY の詳細はこちら
【3】末尾から逆順に検索する（最新の履歴を取得）
同じ値が複数行に存在する場合、最後に登録されたデータ（最新の履歴）を取得したいケースがあります。
第6引数に -1 を指定すると、末尾から先頭に向かって検索します。
第5引数 0（完全一致）、第6引数 -1（末尾から先頭へ）。
D列に「1001」が複数行あった場合、一番下（最後）の行のE列を返す。
【4】INDEX+MATCHが最新Excelでも現役な理由：行オフセット技
XLOOKUPが登場しても、INDEX+MATCHが完全に不要になったわけではありません。
MATCHは「検索値が何行目にあるか」という行番号（数値）を返します。
この数値に +1 や -1 を足すことで、
ヒットした行の1行下・1行上のデータを自在に取得できます。
例：D列で「商品A」を見つけ、その1行下のC列の値を取る
例：D列で「商品A」を見つけ、その1行上のC列の値を取る
XLOOKUPは「ヒットした行そのもの」しか返せず、隣接行へのずらし取得はできません。 「見つけた位置を起点に相対的にずらして取得したい」ケースでは、 最新のMicrosoft 365やExcel 2024環境でもINDEX+MATCHの組み合わせが最適解です。