Excelで「同じ値が複数あるデータ」を確実に抽出したい、、Excelを触っていると必ずは遭遇するこの場面、VLOOKUP関数やXLOOKUPを使える人こそハマってしまう壁でもあります。
この記事で解決できること（最初に結論）
Excelで、こんな経験はありませんか？
- VLOOKUPで検索すると最初の1件しか出ない
- XLOOKUPに変えても全部は取れない
- 「2件目・3件目」も必要なのに方法が分からない
- 社内PCが古くてFILTER関数が使えない
👉 結論：重複データを取り出す最適解は、実は3つだけです
| Excel環境 | 最適な方法 | なぜこれか |
|---|---|---|
| Excel 2021 / Microsoft 365 | FILTER関数 | 最短・最強・ミスらない |
| Excel 2019以前 | INDEX + LARGE | 全バージョン対応 |
| 最新 or 最後の1件だけ | XLOOKUP | 検索モードで対応可 |
※ VLOOKUP単体では不可能です（仕様上の制限）。
なぜVLOOKUP・XLOOKUPでは「全部」取れないのか？
VLOOKUPの限界（これは不具合ではない）
VLOOKUPは、検索値が複数見つかっても
👉 「最初に見つかった1件」しか返さない関数です。
- 2件目
- 3件目
- 全件
を指定する引数は存在しません。
これはエラーではなく設計上の仕様です。
XLOOKUPでも「全件抽出」はできない
XLOOKUPはVLOOKUPの後継ですが、基本的な考え方は同じです。
=XLOOKUP(検索値, 検索範囲, 戻り範囲, , , 検索モード)検索モードの違い
- 1（省略時）：最初の一致
- -1：最後の一致
👉 つまり
- 取れるのは最初 or 最後の1件だけ
- 全件を一気に返す機能はありません
【最優先】FILTER関数で重複データを一括抽出（最強）
対応環境
- Excel 2021
- Microsoft 365
基本構文
=FILTER(抽出範囲, 条件範囲=条件, "該当なし")例：正社員（○）をすべて抽出
=FILTER(B5:D14, A5:A14="○", "該当なし")ポイント
- 数式は1セルだけ
- 条件に合うデータがすべて自動表示（スピル機能）
- 元データ更新 → 自動反映
👉 この時点で、VLOOKUPに戻る理由はありません。
重複を除外したい場合（UNIQUE）
=UNIQUE(FILTER(B5:B14, A5:A14="○"))横並びにしたい場合
=TRANSPOSE(UNIQUE(FILTER(B5:B14, A5:A14="○")))複数条件も一発で対応
AND条件
=FILTER(B5:D14, (A5:A14="○")*(C5:C14="男性"))OR条件
=FILTER(B5:D14, (A5:A14="○")+(A5:A14="△"))
【FILTERが使えない人向け】INDEX + LARGE（全Excel対応）
こんな人向け
- Excel 2016 / 2019
- 社内PCでアップデート不可
基本形（コピペ可）
=IFERROR(
INDEX(B:B,
SMALL(
IF($A$5:$A$14="○",ROW($A$5:$A$14)),
ROW(A1)
)
),
"")※ 配列数式として入力（Ctrl+Shift+Enter）が必要な場合があります
| 要素 | 意味 |
|---|---|
| B:B | 抽出したい列 |
| A5:A14 | 条件範囲 |
| “○” | 条件 |
| ROW(A1) | 何番目か（下にコピーすると自動で2,3…と増える） |
📌 数式を下に伸ばすだけで、2件目・3件目が表示されます
複数条件（AND）
=IFERROR(
INDEX(B:B,
SMALL(
IF((条件1)*(条件2),ROW(範囲)),
ROW(A1)
)
),
"")
【限定用途】XLOOKUPで「最後の1件」だけ取る
最新データだけ欲しい場合
=XLOOKUP(A2, A5:A14, B5:B14, "該当なし", 0, -1)使いどころ
- 更新日が一番新しいデータ
- 履歴の最新レコードだけ必要
👉 全件はいらない場合のみ有効です。
VLOOKUPで重複を扱う方法（正直おすすめしない）
方法
- 作業列を追加
=COUNTIF($A$5:A5, A5)- → りんご-1 / りんご-2 のように分解
- 結合キーで検索
=VLOOKUP(検索値&"-"&番号, 範囲, 列番号, FALSE)
デメリット
- 列追加が必須
- データ構造が壊れやすい
- メンテナンス性が最悪
👉 今から覚える価値は低いです。
実務で即使える応用例
重複しているデータだけ抽出
=FILTER(A5:A14, COUNTIF(A5:A14, A5:A14)>1)条件付き売上集計（柔軟）
=SUM(FILTER(売上列, カテゴリー列=H2))👉 SUMIFSより直感的で拡張しやすい
どの方法を選ぶべきか（迷ったらこれ）
| 状況 | 結論 |
|---|---|
| Excelが新しい | FILTER一択 |
| Excelが古い | INDEX + SMALL |
| 最新1件だけ | XLOOKUP |
| VLOOKUP信者 | 卒業しよう |
よくある質問（FAQ）
- Q. VLOOKUPで2番目は取れませんか？
- A. 取れません。仕様です。
- Q. FILTERが使えない場合は？
- A. INDEX + SMALLを使ってください。
- Q. エラーを消したい
- A. IFERRORまたはFILTERの第3引数を使いましょう。
まとめ｜重複データ抽出の最適解
- VLOOKUP / XLOOKUPは万能ではない
- 全件抽出＝FILTER
- 古いExcel＝INDEX + SMALL
Excel作業で
「なんか無理やりやってる感」があるなら、
それは関数選択が間違っているだけです。
FILTERを使える環境なら、今日からVLOOKUP卒業でOK。
コメント