「最新の売上を確認しようとしたら、空白セルのせいで古い数値を拾っていた……」そんな経験はありませんか？日々更新される売上記録や進捗表では、空白を無視して最後に値が入ったセルを自動取得するテクニックが欠かせません。
この記事では、LOOKUP関数とXLOOKUP関数を使ったコピペで即使える2つの式を、仕組みから丁寧に解説します。
結論：まずは式だけ見たい方へ
細かい解説の前に、結論をお伝えします。
- LOOKUP式（Excel全般・互換性重視）：
excel =LOOKUP(2, 1/(A1:A100<>""), A1:A100) - XLOOKUP式（Excel 365 / 2021以降・可読性重視）：
excel =XLOOKUP(TRUE, A1:A100<>"", A1:A100, "データなし", 0, -1)
どちらの式を選ぶかは、使用しているExcelのバージョンと、ファイルの共有相手によって決まります。詳しい使い分け基準は記事末尾の比較表を参照してください。
なぜ「最後の空でないセル」の取得が必要なのか
次のような売上記録表を想像してください。
| 行 | 日付 | 売上 |
|---|---|---|
| 2 | 4/1 | 12,000 |
| 3 | 4/2 | 8,500 |
| 4 | 4/3 | （未入力） |
| 5 | 4/4 | 15,000 |
| 6 | 4/5 | （未入力） |
「最新の売上」を別セルに自動表示したい場合、MAXや単純なINDEXでは空白セルが含まれると意図通りに動きません。空白をスキップして、最後に値が入っているB5セルの「15,000」を返す式が必要になります。以降で紹介する2つの方法は、どちらもこのケースで正しく15,000を返します。
方法①：LOOKUP関数を使った古典的テクニック
基本の式
=LOOKUP(2, 1/(B2:B100<>""), B2:B100)
仕組みを3ステップで理解する
一見すると謎めいた式ですが、分解すると構造がよくわかります。
ステップ1：空かどうかを判定する
B2:B100<>""
各セルが空でなければTRUE（＝1）、空ならFALSE（＝0）の配列を生成します。
ステップ2：1で割ってエラーを作る
1/(B2:B100<>"")
- 値があるセル →
1 ÷ 1 = 1 - 空のセル →
1 ÷ 0 = #DIV/0!（エラー）
これにより、「値あり → 1」「空 → エラー」という配列ができます。
ステップ3：LOOKUPの「超えない最大値」ルールを利用する
=LOOKUP(2, {1,1,エラー,1,エラー}, B2:B100)
LOOKUPは検索値（ここでは2）が見つからないとき、エラーを無視しながら検索値を超えない最大値の位置を返す仕様です。配列の中に2はないので、最後に登場する1の位置、つまりB5の15,000が返ります。
数値にも文字列にも使える
ステータス列など文字列が入る列にもそのまま使えます。
=LOOKUP(2, 1/(C2:C100<>""), C2:C100)
「最後に更新されたステータス」を自動取得できます。
方法②：XLOOKUP関数を使ったモダンな書き方
XLOOKUPはExcel 365・Excel 2021以降で使用できます。Google スプレッドシートでは現時点で非対応または動作が不安定なケースがあるため、スプレッドシートユーザーは方法①のLOOKUP式を使用してください。
基本の式
=XLOOKUP(TRUE, B2:B100<>"", B2:B100, "データなし", 0, -1)
各引数の意味
| 引数 | 設定値 | 意味 |
|---|---|---|
| 検索値 | TRUE |
「空でない」という条件に一致するものを探す |
| 検索範囲 | B2:B100<>"" |
各セルが空でないかを判定した配列 |
| 戻り範囲 | B2:B100 |
実際に返したい値の範囲 |
| 見つからない場合 | "データなし" |
全セルが空のときに表示するテキスト |
| 一致モード | 0 |
完全一致 |
| 検索モード | -1 |
末尾から検索（これが最後の値を取得するカギ） |
XLOOKUPを使う利点
- 検索モードを
-1にするだけで末尾から検索できるため、式の意図が一目でわかる - 「見つからない場合」を第4引数で明示できるので、別途
IFERRORを書く必要がない - 後からメンテナンスする際に式の読み解きコストが低い
LOOKUPとXLOOKUP、どちらを使うべき？
| 比較項目 | LOOKUP | XLOOKUP |
|---|---|---|
| 対応バージョン | Excel全般 | Excel 365・2021以降 |
| Google スプレッドシート | ✅ 使用可 | ⚠️ 非対応・不安定な場合あり |
| 可読性 | やや低い（慣れが必要） | 高い |
| エラー処理 | IFERRORが別途必要 |
第4引数で完結 |
| 共有ファイルでの安全性 | ◎ | △（バージョン依存） |
判断の目安： - 共有相手のExcelバージョンが不明、またはGoogle スプレッドシートを使う → LOOKUP式 - 自分専用のExcel 365環境で保守性を重視する → XLOOKUP式
よくあるエラーと対処法
#N/Aエラーが出る（LOOKUP式の場合）
範囲内に値が1件もないときに発生します。IFERRORで囲んで対処します。
=IFERROR(LOOKUP(2, 1/(B2:B100<>""), B2:B100), "データなし")
数値と文字列が混在していて書式が崩れる
日付などの数値が文字列として表示されてしまう場合は、TEXT関数で書式を指定します。
=TEXT(LOOKUP(2, 1/(A2:A100<>""), A2:A100), "yyyy/m/d")
日付列に使うときは特に有効です。自分のデータ型に合わせて書式文字列を変えてください（例：数値なら"#,##0"）。
検索範囲が広すぎて動作が重い
A1:A1000000のような列全体指定は処理が遅くなります。実際のデータ件数に合わせた範囲指定か、A2:INDEX(A:A, COUNTA(A:A)+1)のような動的範囲を使うと改善できます。
まとめ
最後の空でないセルを取得する2つのテクニックを紹介しました。
- LOOKUP式：
=LOOKUP(2, 1/(範囲<>""), 範囲)— バージョン問わず使える互換性重視の定番式 - XLOOKUP式：
=XLOOKUP(TRUE, 範囲<>"", 範囲, "なし", 0, -1)— Excel 365以降で使えるメンテナンスしやすい式
どちらも一度覚えてしまえば、売上記録・進捗管理・在庫更新など幅広い場面で即戦力になります。まずはサンプルデータで動作を確認し、業務のシートに組み込んでみてください。
次回のLookupシリーズでは、複数条件での最後の値取得を解説予定です。お楽しみに！
