【Excel】VLOOKUP関数が最後のデータしか返さない!Excelの検索方向と重複時の挙動と対処法

ExcelでVLOOKUP関数を使ってデータを検索する際、意図した結果が得られないことがあります。特に、検索対象の列に同じ値が複数存在する場合、一番最初に見つかったデータではなく、一番最後のデータが返される現象に遭遇することがあります。この問題は、VLOOKUP関数の基本的な仕様と、Excelのデータ処理の特性に起因しています。この記事では、VLOOKUP関数が最後のデータしか返さない原因を解説し、それを解決するための具体的な方法を提示します。

VLOOKUP関数は、指定した範囲の左端の列で特定の値を検索し、見つかった行の指定した列の値を返します。しかし、検索値が複数存在する場合の挙動を理解していないと、期待通りの結果を得られません。この記事を読めば、VLOOKUP関数の検索方向と重複時の挙動を理解し、正確なデータを取得できるようになります。

VLOOKUP関数が最後のデータしか返さない根本原因

VLOOKUP関数が最後のデータしか返さない主な原因は、ExcelのVLOOKUP関数の仕様にあります。VLOOKUP関数は、検索範囲の左端の列を上から順に検索していきます。そして、最初に検索値と一致したセルが見つかった時点で、その行の指定された列の値を返して検索を終了します。したがって、検索値が複数存在する場合、一番最初に見つかったデータが返されるはずです。

しかし、実際には「最後のデータしか返さない」という現象が発生することがあります。これは、VLOOKUP関数の仕様そのものではなく、データの並び順や、検索範囲の指定方法、あるいは別の関数との組み合わせによって引き起こされる場合があります。例えば、データが降順に並んでいる場合や、検索範囲が意図せずデータの末尾から始まっているように解釈されている場合に、このような状況が発生する可能性があります。

VLOOKUP関数で最初に見つかったデータを取得する手順

VLOOKUP関数で検索値が重複している場合に、一番最初に見つかったデータを確実に取得するには、いくつかの方法があります。ここでは、最も一般的で簡単な方法を解説します。

1. 検索対象のデータを昇順に並べ替える
   VLOOKUP関数は、検索範囲の左端の列を上から順に検索します。検索値が重複している場合、一番最初に見つかったものが返されます。この性質を利用して、検索対象の列を昇順(小さい順)に並べ替えることで、意図した最初のデータを取得できます。データ量が多い場合は、並べ替えに時間がかかることがあります。
2. MATCH関数とINDEX関数を組み合わせる
   MATCH関数とINDEX関数を組み合わせることで、VLOOKUP関数のように検索範囲の左端に依存せず、柔軟な検索が可能です。MATCH関数で検索値が最初に出現する位置(行番号)を特定し、INDEX関数でその位置に対応する値を返します。この方法は、データの並べ替えが不要なため、元のデータを保持したまま検索したい場合に有効です。
3. FILTER関数を使用する(Microsoft 365またはExcel 2021以降)
   Microsoft 365またはExcel 2021以降のバージョンでは、FILTER関数を使用できます。FILTER関数は、指定した条件に一致するすべてのデータを配列として返します。この配列の最初の要素を取得することで、最初に見つかったデータを取得できます。

MATCH関数とINDEX関数を組み合わせる具体的な手順

MATCH関数とINDEX関数を組み合わせる方法は、VLOOKUP関数が最後のデータしか返さない問題を解決するための強力な手段です。この組み合わせは、データの並べ替えを必要とせず、より柔軟な検索を実現します。

MATCH関数とINDEX関数の基本構造

MATCH関数は、指定した範囲内で特定の値を検索し、その値が範囲内の何番目にあるかを示す数値を返します。INDEX関数は、指定した範囲の中から、指定した行番号と列番号に対応する値を返します。

MATCH関数とINDEX関数を使った検索手順

1. MATCH関数で検索値の位置を特定する
   MATCH関数を使って、検索したい値が検索範囲の何番目にあるかを見つけます。例えば、A1セルに検索値があり、B列(B1:B100)で検索し、完全一致で探す場合は、`MATCH(A1, B1:B100, 0)` と入力します。ここで、0は完全一致検索を指定します。
2. INDEX関数で対応する値を抽出する
   MATCH関数で得られた行番号を、INDEX関数に渡して目的の値を抽出します。例えば、C列(C1:C100)から対応する値を返したい場合は、`INDEX(C1:C100, MATCH(A1, B1:B100, 0))` となります。これにより、B列でA1の値が最初に見つかった行の、C列の値を返します。

INDEX関数とMATCH関数を組み合わせるメリット

この組み合わせの大きなメリットは、検索対象の列が検索値の左側にある場合でも検索できる点です。VLOOKUP関数は検索対象の列が必ず一番左にある必要がありますが、INDEX関数とMATCH関数は列の順序に依存しません。また、データの並べ替えが不要なため、元のデータをそのまま利用できます。この柔軟性から、複雑なデータ分析において非常に有用です。

FILTER関数を使った検索手順(Microsoft 365/Excel 2021以降)

Microsoft 365またはExcel 2021以降をご利用の場合、FILTER関数を使うことで、より直感的に重複する値の中から最初に見つかったものを取得できます。FILTER関数は、条件に一致するすべてのデータを配列で返します。

FILTER関数で重複データを抽出する

例えば、A1セルに検索値があり、B列(B1:B100)で検索し、C列(C1:C100)から対応する値を返したい場合を考えます。FILTER関数は以下のように記述します。

`=FILTER(C1:C100, B1:B100=A1, “見つかりません”)`

この数式は、B列(B1:B100)がA1セルの値と一致するすべての行について、C列(C1:C100)の値を配列として返します。もし一致するデータが1つだけなら、その値が返されます。複数ある場合は、それらが配列として返されます。

配列から最初の値を取得する

FILTER関数で返された配列から、最初に見つかった値だけを取得するには、さらにINDEX関数を組み合わせます。FILTER関数が返す配列の最初の要素を取得したい場合は、以下のように記述します。

`=INDEX(FILTER(C1:C100, B1:B100=A1, “見つかりません”), 1)`

この数式は、FILTER関数が返した配列の1番目の要素、すなわち最初に見つかったデータを返します。もし一致するデータが一つもない場合は、FILTER関数の第3引数で指定した「見つかりません」という文字列が返されます。

FILTER関数使用時の注意点

FILTER関数は動的な配列を返すため、数式を入力したセルとその下に、結果が自動的に展開されます。この展開範囲に他のデータが存在すると、#SPILL!エラーが発生する可能性があります。また、FILTER関数はMicrosoft 365およびExcel 2021以降のバージョンで利用可能であり、それ以前のバージョンでは使用できません。互換性に注意が必要です。

VLOOKUP関数で最後のデータしか返さない場合のよくある誤解と対処法

VLOOKUP関数が最後のデータしか返さないという現象は、しばしば誤解を生みます。ここでは、よくある誤解とその対処法について解説します。

誤解1:VLOOKUP関数は自動的に最後のデータを返す

これは誤解です。前述の通り、VLOOKUP関数は最初に一致したデータを返します。もし最後のデータが返されているように見える場合、それはデータの並び順や検索範囲の設定が原因である可能性が高いです。対処法としては、データを昇順に並べ替えるか、INDEX/MATCH関数やFILTER関数を使用します。

誤解2:検索値が重複していると必ずエラーになる

これも誤解です。検索値が重複していても、VLOOKUP関数はエラーを返さず、最初に一致したデータを返します。エラー(#N/Aなど)が発生するのは、検索値が検索範囲内に全く見つからなかった場合です。

誤解3:検索範囲の指定方法が複雑で間違えやすい

VLOOKUP関数の検索範囲の指定は、絶対参照($)を使うことが推奨されます。相対参照のままコピーすると、検索範囲がずれてしまい、意図しない結果を招くことがあります。例えば、`VLOOKUP(A1, Sheet1!$B$2:$D$100, 3, FALSE)` のように、$マークを付けて固定します。これにより、数式をコピーしても検索範囲が固定され、参照エラーを防ぐことができます。

対処法:検索方向の確認とFALSE指定の徹底

VLOOKUP関数は、第4引数(検索方法)に`FALSE`または`0`を指定することで、完全一致検索を行います。これを指定しない場合、Excelは近似一致検索となり、検索値以下の最も近い値を返します。この近似一致検索が、意図しない結果、特に最後のデータに近い値が返されてしまう原因となることがあります。必ず`FALSE`または`0`を指定し、完全一致検索を徹底してください。また、検索範囲の左端列が、本当に検索したい値を含んでいるか、データが昇順に並んでいるかを確認することも重要です。

VLOOKUP関数とINDEX/MATCH関数/FILTER関数の比較

VLOOKUP関数、INDEX関数とMATCH関数の組み合わせ、そしてFILTER関数は、いずれもデータを検索・抽出する機能ですが、それぞれに特徴があります。どの関数を使うべきかは、目的やExcelのバージョンによって異なります。

項目	VLOOKUP関数	INDEX関数 + MATCH関数	FILTER関数 (Microsoft 365/Excel 2021以降)
検索方向の制約	検索対象列は検索値列の右側にある必要がある	制約なし(左側でも右側でも可)	制約なし
重複時の挙動	最初に見つかったデータを返す	最初に見つかったデータを返す	条件に一致するすべてのデータを配列で返す
データの並べ替え	近似一致の場合、昇順並べ替えが必要	不要	不要
列の挿入/削除への耐性	列番号を指定するため、列の挿入/削除で数式が壊れやすい	列参照のため、列の挿入/削除に比較的強い	列参照のため、列の挿入/削除に強い
返せる値	指定した1つの値	指定した1つの値	条件に一致する複数の値(配列)
Excelバージョン	全バージョン	全バージョン	Microsoft 365, Excel 2021以降

まとめ

VLOOKUP関数が最後のデータしか返さないように見える問題は、関数の仕様そのものではなく、データの並び順や検索方法の指定に起因することがほとんどです。この記事では、VLOOKUP関数が最初に一致したデータを返すことを確認し、重複データから最初に見つかったデータを取得するために、データの昇順並べ替え、INDEX関数とMATCH関数の組み合わせ、そしてFILTER関数(Microsoft 365/Excel 2021以降)の使用方法を解説しました。これらの方法を理解し、状況に応じて使い分けることで、Excelでのデータ検索の精度と効率を大幅に向上させることができます。今後は、VLOOKUP関数だけでなく、INDEX/MATCH関数やFILTER関数も活用し、より高度なデータ分析に挑戦してみてください。

📊

Excelトラブル完全解決データベースこの記事以外にも、様々なエラー解決策をまとめています。困った時の逆引きに活用してください。

🏆

超解決 Excel検定 あなたのExcel実務能力を3分で測定！【1級・2級・3級】