XLOOKUPで最後に一致する行を取り出すには、第6引数の検索モードを-1にします。これは表を下から探す指定です。「最新日付の行」になるのは、日付の古い順に並べてあるなど、下の行ほど新しい表の場合です。
無料Excelサンプルで一緒に試す
B2の発注者を検索し、B3に発注番号を表示する例題です。F3:F9を下から検索し、D3:D9の発注番号を返します。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
目次
まず動かしてみよう｜この記事のサンプルをその場で試せます
練習用ファイルをダウンロードしなくても、記事と同じデータ・同じセル番地のサンプルをここで動かせます。黄色いセルを書き換えると、結果がその場で変わります。各解説の最後にある「▶ この式を試す」を押すと、このツールの該当セルへ移動します。
サンプルの数式
ポイントB2が発注者、F列が発注履歴の発注者、D列が発注番号です。B2を西瓜電機にすると、サンプルの該当例では1007が返ります。最初のシートと別の例題シートでは入力条件が異なるので、検索条件と結果を対にして確認してください。
第5引数と第6引数を取り違えない
| 引数 | 指定 | 意味 |
|---|---|---|
| 第5引数：一致モード | 0 | 完全一致 |
| 第6引数：検索モード | -1 | 末尾から先頭へ |
注意第5引数の-1は「完全一致、なければ次に小さい値」です。逆順検索とは別の設定です。省略用のカンマだけで区切るより、0,-1と明示すると読み間違いを減らせます。
最後の行が最新とは限らない
注意たとえばA商事の4月5日の行が上、4月2日の行が下にあると、逆順検索は4月2日の行を返します。日付の最大値を比較しているわけではありません。図はこの違いを示す説明用の小さな例で、配布ファイルの発注履歴とは別データです。
ポイント入力順や更新順をそのまま採用したいなら逆順検索、日付の新しさを基準にしたいならMAXIFSとXLOOKUPの組み合わせを使います。
複数条件で最後の行を探す
説明用に、A2:A20が商品、B2:B20が拠点、C2:C20が数量、F2・G2が検索条件とします。
ポイント2条件をともに満たす行のうち、末尾側の1件を返します。同じ日時の複数行に優先順位が必要なら、更新時刻や連番などのルールも決めておきます。
行を追加したのに結果が変わらない
注意F3:F9の範囲に含まれない10行目へデータを追加しても検索されません。検索列と戻り列を一緒に広げるか、Excelのテーブル参照を使います。範囲を片方だけ広げると行数が一致しなくなります。
対応版と使い分け
ポイントXLOOKUPはMicrosoft 365・Excel 2024・2021で使えます。Excel 2019・2016には搭載されていません。1件ではなく同じ発注者の履歴を全部表示したい場合はXLOOKUPとFILTERの違いを確認してください。
ポイント見つからない場合の—は、発注番号が空白であることとは別です。結果が0になるときは戻りセルの状態も確認します。
結果がおかしいときの確認表（古い値・「—」・0）
逆順検索は便利ですが、「最後の行」を返しているだけです。思った値と違うときは、次の表で原因を探します。練習ツールの「最後の行≠最新の日付」タブで、入力順と日付の違いを確かめられます。
| 症状 | よくある原因 | 対処 |
|---|---|---|
| 最新の日付の行ではない値が返る | 逆順検索は、入力順でいちばん下の行を返します。日付の新しさは比べていません | 日付の新しさを基準にするなら、MAXIFSなどで最新の日付を求め、その日付の行を探します |
| データはあるのに「—」になる | 発注者の名前の前後に余分なスペースがある、または表記が違う（株式会社の有無など） | スペースを取り除くか、表記をそろえます。空白（スペース）を削除する方法が参考になります |
| 追加した行が検索されない | 追加した行が、F3:F9のような固定の範囲の外にある | 検索範囲と戻り範囲を、いっしょに広げます。範囲を自動で広げる方法も参考になります |
| 結果が0になる | 見つかった行の戻りのセルが空白 | XLOOKUPは、空白のセルを0として返します。空白のまま表示したいときは、IF関数などで切り替えます |
無料Excelサンプルで復習する
B2の発注者を検索し、B3に発注番号を表示する例題です。F3:F9を下から検索し、D3:D9の発注番号を返します。
Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。
次に読むと理解が深まる記事
- Excelで最新日付のデータを抽出｜MAX・MAXIFSとXLOOKUP
- XLOOKUPの範囲を自動拡張｜テーブル参照・列全体指定の使い方
- XLOOKUPで複数該当をすべて抽出できる？FILTERとの違いと2件目の取得
参考：Microsoft公式ドキュメント XLOOKUP
コメント