【Excel】部分一致で「最後の一致セル」を取得する4つの方法【全バージョン対応】
「VLOOKUPで検索したら、最初にヒットしたものしか取れなかった…」
そんな経験はありませんか？複数の一致候補がある中で一番最後（最新）のデータを取り出したいのに、Excelの標準的な関数では最初の一致しか返せず、途方に暮れてしまうケースは意外と多いです。
この記事を読み終えると、部分一致で最後の一致セルを取得する4つの方法を習得できます。お使いのExcelのバージョンに関係なく使える方法を揃えていますので、自分の環境に合った数式をそのままコピーして使ってみてください。
この記事で使うサンプルデータ
以下のA列データを前提に解説します。
| 行 | A列（商品名） |
|---|---|
| 2 | りんごジュース |
| 3 | みかんゼリー |
| 4 | りんごタルト |
| 5 | ぶどうジュース |
| 6 | りんごパイ |
目標：「りんご」を含む最後の商品名 → りんごパイ を取得する
どの方法を選べばいい？ まず確認しよう
方法を詳しく見る前に、自分に合う方法を選ぶための簡単な判断基準を示します。
Excel 2021 / Microsoft 365 を使っている └→ 方法①（XLOOKUP版）← 最も直感的でおすすめ Excel 2010〜2019 を使っている ├→ 方法②（LOOKUP版）← 最もシンプル・おすすめ └→ 方法③（AGGREGATE版）← エラー制御を細かくしたい場合 Excel 2007 など古いバージョン / 配列数式が使える環境 └→ 方法④（INDEX+MATCH配列版）
迷ったら方法②（LOOKUP版）を選んでください。最も短い数式で、Excel 2007以降のすべてのバージョンで動作します。
方法①：XLOOKUP + SEARCH（Excel 2021 / M365向け）
数式
=XLOOKUP(1,
(ISNUMBER(SEARCH("りんご", A2:A6))) *
(ROW(A2:A6) = MAX(ISNUMBER(SEARCH("りんご", A2:A6)) * ROW(A2:A6))),
A2:A6)
仕組みの解説
SEARCH("りんご", A2:A6)→ 各セルで「りんご」が何文字目にあるかを返す（なければエラー）ISNUMBER(...)→ エラーをFALSE、一致をTRUE（数値として1）に変換MAX(ISNUMBER(...) * ROW(...))→ 部分一致した行の中で最も大きい行番号を取得ROW(A2:A6) = MAX(...)→ その最大行番号と一致する行だけをTRUEにするXLOOKUP(1, ..., A2:A6)→ ①と④をかけ合わせ、1になる（＝最後の一致）の行の値を返す
一致しない場合のエラー対策（推奨）：
=IFERROR(XLOOKUP(1,
(ISNUMBER(SEARCH("りんご", A2:A6))) *
(ROW(A2:A6) = MAX(ISNUMBER(SEARCH("りんご", A2:A6)) * ROW(A2:A6))),
A2:A6), "一致なし")
方法②：LOOKUP + SEARCH（全バージョン対応・最もシンプル）
数式
=LOOKUP(2, 1/(ISNUMBER(SEARCH("りんご", A2:A6))), A2:A6)
仕組みの解説
一見わかりにくいですが、LOOKUPの「仕様を逆手に取った」古典的テクニックです。
SEARCH(...)→ 一致位置またはエラーの配列を生成ISNUMBER(...)→TRUE（1）またはFALSE（0）の配列に変換1/ISNUMBER(...)→ TRUEの行は1、FALSEの行は#DIV/0!エラーになるLOOKUP(2, ...)→ 配列中に2は存在しないため、LOOKUPは「2以下の最大値」を探す仕様により、エラーをスキップしながら最後の1の位置の値を返す
この「存在しない値を検索させて最後の有効値を取る」のがこの数式のポイントです。
エラー対策付き：
=IFERROR(LOOKUP(2, 1/(ISNUMBER(SEARCH("りんご", A2:A6))), A2:A6), "一致なし")
✅ Excel 2007以降で動作。配列数式（Ctrl+Shift+Enter）不要で最もコンパクト。
方法③：INDEX + AGGREGATE + SEARCH（Excel 2010以降）
AGGREGATE はあまり知られていませんが、「エラーを自動で無視しながら集計できる」非常に強力な関数です。配列数式を使わずにエラーを除外した計算ができる点が魅力です。
数式
=INDEX(A2:A6,
AGGREGATE(14, 6,
(ROW(A2:A6) - ROW(A2) + 1) / ISNUMBER(SEARCH("りんご", A2:A6)),
1))
仕組みの解説
ROW(A2:A6) - ROW(A2) + 1→ 相対行番号（1〜5）を生成ISNUMBER(SEARCH(...))→ 一致しない行はFALSE（0）になる相対行番号 / ISNUMBER(...)→ 不一致行はゼロ除算エラー（#DIV/0!）になるAGGREGATE(14, 6, ..., 1)→ 第1引数14はLARGE相当、第2引数6はエラー無視。エラーを除いた中で最大の行番号（＝最後の一致行）を取得INDEX(A2:A6, ...)→ その行番号の値を返す
エラー対策付き：
=IFERROR(INDEX(A2:A6,
AGGREGATE(14, 6,
(ROW(A2:A6) - ROW(A2) + 1) / ISNUMBER(SEARCH("りんご", A2:A6)),
1)), "一致なし")
✅ Excel 2010以降で動作。Ctrl+Shift+Enter不要。
方法④：INDEX + MATCH の配列数式（Excel 2007以降）
数式（Ctrl+Shift+Enter で確定）
=INDEX(A2:A6,
MATCH(1,
ISNUMBER(SEARCH("りんご", A2:A6)) *
(ROW(A2:A6) = MAX(ISNUMBER(SEARCH("りんご", A2:A6)) * ROW(A2:A6))),
0))
ロジックは方法①と同じですが、XLOOKUPの代わりにINDEX+MATCHを使います。数式入力後に必ずCtrl+Shift+Enterで確定してください。数式バーに {=...} と波括弧が表示されれば成功です。
方法の比較まとめ
| 方法 | 使用関数 | 対応バージョン | 配列数式 | 簡潔さ | 備考 |
|---|---|---|---|---|---|
| ① XLOOKUP版 | XLOOKUP + SEARCH | 2021 / M365 | 不要 | ★★★☆☆ | 読みやすく直感的 |
| ② LOOKUP版 | LOOKUP + SEARCH | 2007以降 | 不要 | ★★★★★ | 最短・最も汎用的 |
| ③ AGGREGATE版 | INDEX + AGGREGATE + SEARCH | 2010以降 | 不要 | ★★★☆☆ | エラー制御が柔軟 |
| ④ 配列数式版 | INDEX + MATCH + SEARCH | 2007以降 | 必要 | ★★☆☆☆ | 旧環境の最終手段 |
シンプルさの基準： 数式の文字数と、初見で理解できる難易度で評価しています。②は全体でも最短の数式であり、仕組みを覚えてしまえば応用も効くため最高評価です。
よくある疑問・エラー対処
Q. 一致するものが1つもない場合は？
#N/A や #VALUE! エラーが出ます。すべての数式を IFERROR(数式, "一致なし") で囲むことで、エラーの代わりに任意のテキストを表示できます。
Q. 大文字・小文字を区別して検索したい
SEARCH の代わりに FIND を使ってください。FIND は大文字小文字を区別します。
Q. 空白セルが含まれていても動作する？
方法②〜③はエラー（空白由来のエラーを含む）を無視する構造なので基本的に問題ありません。方法④の場合は IFERROR を内側に追加するとより安全です。
Q. #VALUE! エラーが出る
検索範囲（例：A2:A6）と返却範囲のサイズが一致しているか確認してください。また、方法④でCtrl+Shift+Enterを忘れていないかも確認しましょう。
まとめ
| 状況 | おすすめ方法 |
|---|---|
| Excel 2021 / M365 で直感的に書きたい | ① XLOOKUP版 |
| バージョンを問わず最短で書きたい | ② LOOKUP版（最推奨） |
| 10以降でエラー制御を柔軟にしたい | ③ AGGREGATE版 |
| 古いバージョンで配列数式が使える | ④ INDEX+MATCH版 |
どの方法を使う場合も、IFERROR を組み合わせてエラー処理をしておくことが実務での安心につながります。まずは方法②のLOOKUP版を試してみて、自分のExcel環境や用途に合わせて他の方法も活用してみてください。
💡 この記事が役に立ったら、ブックマーク・シェアをしていただけると励みになります。VLOOKUP・XLOOKUPの応用テクニックをまとめた関連記事もあわせてご覧ください！
