XLOOKUPを入れ子にすると、「券種」と「年齢区分」のような縦横2つの見出しから、交点の金額を取り出せます。内側のXLOOKUPで1行を選び、外側のXLOOKUPでその行から1列を選ぶ、という順番で読むと理解しやすくなります。
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
XLOOKUPの入れ子（ネスト）で縦横検索する基本式
サンプルの結果セルB4には次の式を入力します。
| 場所 | 役割 |
|---|---|
| B2 | 縦の行見出しから探す条件 |
| B3 | 横の列見出しから探す条件 |
| A8:A10 | 縦に並ぶ行見出し |
| B7:D7 | 横に並ぶ列見出し |
| B8:D10 | 見出しを含まない料金の範囲 |
① 内側：券種「2日」の行を選ぶ → ② 外側：「小学生」の列を選ぶ
| 券種 | 大人 | 中学生 | 小学生 |
|---|---|---|---|
| 1日 | 8,500 | 7,000 | 4,700 |
| 2日 | 13,600 | 10,900 | 7,500 |
| 3日 | 17,800 | 14,300 | 9,800 |
内側のXLOOKUPは「1行」を返す
この部分は、B2の条件を縦のA8:A10から探します。戻り範囲がB8:D10の3列なので、見つけた行の3つの料金が横並びで返ります。確認するときは元の表から離れた空きセルへ入力し、右隣2セルを空けてください。
ポイント検索範囲が縦1列だからといって、内側の結果が縦1列になるわけではありません。結果の形は戻り範囲によって決まります。
外側のXLOOKUPで、その行の列を選ぶ
外側はB3をB7:D7の列見出しから検索します。その位置にある料金を、内側で取り出した1行から返します。サンプルではB2が「2日」、B3が「小学生」なので、結果は7,500です。
注意内側の出力が3列なら、外側の検索範囲も3列にそろえます。片方だけ4列に広げたり、見出しを1列ずらしたりすると、エラーや誤った値の原因になります。
#N/Aになる場合の表示を決める
行・列のどちらかの見出しが見つからない場合を「該当なし」と表示するには、全体をIFNAで囲めます。
注意IFNAは#N/Aを置き換えます。元の料金セル自体に#N/Aが入っている場合も同じ表示になる点には注意してください。入力ミスを調べるときは一度IFNAを外し、内側の式から確認すると原因を切り分けられます。
注意内側の戻り値を「該当なし」という1セルの文字にすると、外側の検索範囲と大きさが合わなくなる場合があります。入れ子では「どちらの検索が失敗したか」も意識します。
INDEX＋MATCHでも同じ縦横検索ができる
対応バージョンExcel 2019・2016など、XLOOKUPが使えない環境では次の式を使えます。
ポイント2つのMATCHが行番号と列番号を求め、INDEXが交点を返します。MATCHの最後の0は完全一致です。利用するExcelや、管理する人が読みやすい書き方で選びましょう。
▶ INDEX＋MATCHの式（B5）を、上の練習ツールで試す
XLOOKUPの入れ子で縦横検索ができない・エラーになるときの確認表
| 症状 | 主な原因 | 確認と対処 |
|---|---|---|
| #N/A | 行（B2）か列（B3）の見出しが、表の見出しと一致していない（余分な空白・表記の違いなど） | 見出しを表からコピーして試す。内側の式だけを別のセルに入れて、どちらが原因か切り分ける |
| #SPILL! | 内側の式だけを入力したとき、右隣の2セルが空いていない | 右隣の2セルを空ける。元の表から離れた空きセルで確認する |
| 違う列の値が返る | 外側の検索範囲（B7:D7）と、内側の戻り範囲（B8:D10）の列の数や順番がずれている | 見出しの範囲と料金の範囲を、同じ列の幅・同じ順番にそろえる |
| #NAME? | XLOOKUPが使えないExcel（2019・2016など）で開いている | INDEX＋MATCHの式を使う |
XLOOKUPの複数条件・クロス抽出・全件抽出との違い
この例は「行見出し1つ＋列見出し1つ」で1セルを探します。商品名とサイズの両方で同じ一覧の行を探す方法はXLOOKUPとIF・複数条件の使い分け、商品・単価など複数の行条件を使うクロス検索はクロス抽出の実例で説明しています。
ポイント行見出しや列見出しが重複している場合、通常は最初の一致を使います。重複分の合計が必要なら、1セルを返す検索式ではなくSUMIFSなどの集計方法を検討してください。
次のステップ：商品と倉庫のクロス抽出・複数条件へ進む
仕様の確認：Microsoft公式・XLOOKUP関数
コメント