VLOOKUPで下から検索（最後の一致を取得）するには、、はい、できません。諦めてください。
これをやるには、XLOOKUP関数の第6引数「検索モード」に「-1」を指定します。VLOOKUP関数自体には下から検索する機能がないため、XLOOKUPまたはLOOKUP関数で代用します。検証環境はWindows 11＋Microsoft 365版Excelです。Excel 2021でも同手順で動作します。
結論：下から検索はXLOOKUPの検索モード「-1」で行う（めちゃ簡単）
VLOOKUP単体では下から検索できません。最後の一致を取得するには、用途に応じて次の数式を使用します。そのままコピーして使えます。
| 用途 | 数式 |
|---|---|
| 最後の一致を取得（365 / 2021） | =XLOOKUP(E2, A2:A100, B2:B100, , , -1) |
| 見つからない時に文字を表示 | =XLOOKUP(E2, A2:A100, B2:B100, “該当なし”, 0, -1) |
| 旧バージョン（2019 / 2016） | =LOOKUP(2, 1/(A2:A100=E2), B2:B100) |
第6引数「-1」が「末尾から先頭へ検索」を意味します。複数の一致がある場合、最も下の行の値が返ります。XLOOKUPはMicrosoft 365とExcel 2021でのみ使用できます。Excel 2019/2016では後述のLOOKUP関数を使用します。
XLOOKUPで下から検索する手順
同じ検索値が複数行ある一覧表から、最後の一致を取得する手順です。
- 結果を表示したいセルを選択します。
- 「 =XLOOKUP( 」と入力します。関数の引数ガイドが表示されます。
- 検索値（例：E2）、検索範囲（例：A2:A100）、戻り範囲（例：B2:B100）を順に指定します。
- 第4引数（見つからない場合）と第5引数（一致モード）は省略するためカンマだけ打ち、第6引数に「-1」を入力します。
- 「Enter」キーを押します。最後に一致した行の値がセルに表示されます。
見つからないときに「該当なし」と表示する実務向けの形は、冒頭のテンプレ表の2行目のとおりです。第4引数に文字を入れると、#N/Aの代わりに任意の文字を表示できます。
検索モードの値と動作
第6引数に指定できる値と動作は次のとおりです。下から検索は「-1」です。
| 検索モード | 動作 |
|---|---|
| 1 | 先頭から検索（既定・最初の一致を返す） |
| -1 | 末尾から検索（最後の一致を返す）＝下から検索 |
| 2 | 昇順データでの二分検索 |
| -2 | 降順データでの二分検索 |
VLOOKUPで下から検索できない理由とLOOKUP関数での代替
VLOOKUP関数は常に先頭（上）から検索し、最初に一致した行を返します。検索方向を逆にする引数は存在しないため、VLOOKUP単体では下から検索できません。Excel 2019/2016など、XLOOKUPが使えない環境ではLOOKUP関数で代用します。
最後の一致を取得するLOOKUP関数の数式は次のとおりです。
=LOOKUP(2, 1/($A$2:$A$100=E2), $B$2:$B$100)
この数式は、3つの段階で「最後の一致」を取得します。各部分の変換結果は次のとおりです。
| 数式の部分 | 変換結果 |
|---|---|
| ($A$2:$A$100=E2) | 一致する行は TRUE、しない行は FALSE |
| 1/(上の配列) | 一致行は 1、不一致行は #DIV/0!（エラー） |
| LOOKUP(2, …) | 「2」は配列内に無いため、最後にある「1」の位置の戻り値を返す |
LOOKUPは「探した値以下で最も近い値」を探す性質を持ちます。配列の最大値は1のため、結果として一番下の1、つまり最後の一致が選ばれます。この数式は配列を内部処理するため、「Ctrl」＋「Shift」＋「Enter」での確定は不要です。Excel 2016/2019/2021およびMicrosoft 365のすべてで動作します。
※分かりにくいですよね。なのでXLOOKUPを使用できる環境への移行を検討、進言しましょう。
バージョン別の使い分け：XLOOKUPとLOOKUPの選択基準
環境ごとの推奨方法は次のとおりです。
| 環境 | 推奨する下から検索の方法 |
|---|---|
| Microsoft 365 / Excel 2021 | XLOOKUP（検索モード -1） |
| Excel 2019 / 2016 / それ以前 | LOOKUP(2, 1/(範囲=検索値), 戻り範囲) |
| バージョン混在で共有する | 全環境で動くLOOKUP式に統一 |
数式の読みやすさとエラー処理のしやすさはXLOOKUPが上です。一方、バージョンが混在する職場で同じファイルを共有する場合は、全環境で動くLOOKUP方式に統一すると表示崩れを防げます。
下から検索がうまくいかない場合の対処
結果がおかしいときは、症状から原因と対処を確認します。
| 症状 | 原因 | 対処 |
|---|---|---|
| 全行で同じ値が返る | 第6引数「-1」の指定漏れ（既定は1＝先頭から） | 数式末尾が「, , -1」になっているか確認する |
| #N/Aが出る | 検索値が範囲内に無い／前後にスペース混入 | TRIMで整形してから照合する |
| LOOKUP式で#DIV/0!が出る | 一致が0件 | IFERRORで包み「該当なし」を表示する |
| 戻り値が1行ずれる | 検索範囲と戻り範囲の行数が不一致 | 開始行・終了行を両範囲でそろえる |
| 半角・全角で一致しない | 表記ゆれ | データ側の表記を統一する |
| 動作が重い | A:A等の列全体を範囲指定している | 実データのある行（例：A2:A100）に限定する |
よくある質問（FAQ）
VLOOKUPで最後の一致を取得する関数はありますか
VLOOKUP単体ではできません。Microsoft 365/Excel 2021ならXLOOKUP（検索モード -1）、Excel 2019/2016ならLOOKUP(2, 1/(範囲=検索値), 戻り範囲)を使用します。
XLOOKUPが使えないバージョンはどれですか
Excel 2019、Excel 2016、およびそれ以前のバージョンです。XLOOKUPはMicrosoft 365とExcel 2021でのみ使用できます。
下から2番目の一致を取得できますか
標準の引数だけでは取得できません。FILTER関数で一致行をすべて抽出し、INDEX関数で下から2行目を参照します。数式は =INDEX(FILTER(B2:B100, A2:A100=E2), ROWS(FILTER(B2:B100, A2:A100=E2))-1) です。FILTERはMicrosoft 365/Excel 2021で使用できます。
最初の一致（上から）に戻すにはどうしますか
XLOOKUPの第6引数を省略するか「1」を指定します。既定が先頭からの検索のため、=XLOOKUP(E2, A2:A100, B2:B100)で最初の一致が返ります。
コメント