XLOOKUPは縦の一覧だけでなく、横に並んだ年・月の見出しも検索できます。検索範囲を1行、戻り範囲を対応する1行に指定します。列の位置がそろっていれば、離れた行や見出しより上の行も戻り範囲にできます。
無料Excelサンプルで一緒に試す
B2:F2が年の見出し、B7:F7が年間合計です。B10を「2018年」にすると、B11に12,893（千円）が返る例を確認できます。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
横方向検索の基本式
ポイントB10の年をB2:F2から探し、見つかった位置と同じ列のB7:F7の値を返します。配布サンプルでは2018年はE列にあり、E7の12,893が返ります。単位は表に記載された千円です。
数値の年と「年」付き文字列を区別する
注意見出しが文字列の2018年なら、検索値も2018年にそろえます。見出しが数値2018に表示形式で「年」を付けたものなら、検索値は数値2018にします。見た目が同じでも内部の値が異なると一致しません。
▶ 数値の2018と文字列「2018年」の違いを、上の練習ツールで試す
横方向に固定してコピーする
別の検索欄へ式をコピーする場合、見出しと戻り行を固定します。
ポイントB10はコピー先に応じて動かし、検索範囲と戻り範囲の列をそろえたままにします。年度が増えてG列へ追加されたら、両方の範囲をG列まで広げます。
複数の行をまとめて返す
ポイント戻り範囲をB3:F7にすると、見つかった年の四半期別売上と合計が縦5行へ広がります。式はB11などの空き領域に入れ、下のセルを空けておきます。検索範囲と戻り範囲は列数を合わせます。
HLOOKUPで同じ結果を求める
注意範囲の先頭行が2行目なので、7行目の合計は範囲内の6行目です。行番号はシート全体の7ではありません。HLOOKUPも離れた行を返せますが、検索する行を指定範囲の先頭に置く必要があります。XLOOKUPでは検索行と戻り行を別々に指定します。
横と縦の2条件で検索したい場合
ポイント年だけでなく、商品名や支店名との交点を返す場合はXLOOKUPを入れ子にするかINDEX＋MATCHを組み合わせます。XLOOKUPの入れ子で行と列を検索に具体例があります。
XLOOKUPはMicrosoft 365・Excel 2024・2021向けです。Excel 2019・2016との共有ではHLOOKUPなどを使います。
無料Excelサンプルで復習する
B2:F2が年の見出し、B7:F7が年間合計です。B10を「2018年」にすると、B11に12,893（千円）が返る例を確認できます。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
コメント