XLOOKUPで最後に一致する値を取得｜逆順検索と最新日付の違い

XLOOKUPで最後に一致する行を取り出すには、第6引数の検索モードを-1にします。これは表を下から探す指定です。「最新日付の行」になるのは、日付の古い順に並べてあるなど、下の行ほど新しい表の場合です。

無料Excelサンプルで一緒に試す

B2の発注者を検索し、B3に発注番号を表示する例題です。F3:F9を下から検索し、D3:D9の発注番号を返します。

無料サンプルファイルをダウンロード（.xlsx）

Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。

サンプルの数式

=XLOOKUP(B2,F3:F9,D3:D9,"---",0,-1)

B2が発注者、F列が発注履歴の発注者、D列が発注番号です。B2を西瓜電機にすると、サンプルの該当例では1007が返ります。最初のシートと別の例題シートでは入力条件が異なるので、検索条件と結果を対にして確認してください。

第5引数と第6引数を取り違えない

第5引数の-1は「完全一致、なければ次に小さい値」です。逆順検索とは別の設定です。省略用のカンマだけで区切るより、0,-1と明示すると読み間違いを減らせます。

最後の行が最新とは限らない

たとえばA商事の4月5日の行が上、4月2日の行が下にあると、逆順検索は4月2日の行を返します。日付の最大値を比較しているわけではありません。図はこの違いを示す説明用の小さな例で、配布ファイルの発注履歴とは別データです。

入力順や更新順をそのまま採用したいなら逆順検索、日付の新しさを基準にしたいならMAXIFSとXLOOKUPの組み合わせを使います。

複数条件で最後の行を探す

説明用に、A2:A20が商品、B2:B20が拠点、C2:C20が数量、F2・G2が検索条件とします。

=XLOOKUP(1,(A2:A20=F2)*(B2:B20=G2),C2:C20,"該当なし",0,-1)

2条件をともに満たす行のうち、末尾側の1件を返します。同じ日時の複数行に優先順位が必要なら、更新時刻や連番などのルールも決めておきます。

行を追加したのに結果が変わらない

F3:F9の範囲に含まれない10行目へデータを追加しても検索されません。検索列と戻り列を一緒に広げるか、Excelのテーブル参照を使います。範囲を片方だけ広げると行数が一致しなくなります。

対応版と使い分け

XLOOKUPはMicrosoft 365・Excel 2024・2021で使えます。Excel 2019・2016には搭載されていません。1件ではなく同じ発注者の履歴を全部表示したい場合はXLOOKUPとFILTERの違いを確認してください。

見つからない場合の—は、発注番号が空白であることとは別です。結果が0になるときは戻りセルの状態も確認します。

無料Excelサンプルで復習する

B2の発注者を検索し、B3に発注番号を表示する例題です。F3:F9を下から検索し、D3:D9の発注番号を返します。

無料サンプルファイルをダウンロード（.xlsx）

Microsoft 365向け。XLOOKUP・FILTER・UNIQUEの例はExcel 2024・2021でも利用できます。本文の対応版もご確認ください。

次に読むと理解が深まる記事

• Excelで最新日付のデータを抽出｜MAX・MAXIFSとXLOOKUP
• XLOOKUPの範囲を自動拡張｜テーブル参照・列全体指定の使い方
• XLOOKUPで複数該当をすべて抽出できる？FILTERとの違いと2件目の取得

参考：Microsoft公式ドキュメント XLOOKUP