ExcelのVLOOKUP関数の代替。INDEX MATCH, XLOOKUP, FILTERの使い方

Excelでデータの検索や結合を行う際に、多くの人がまず思い浮かべるのがVLOOKUP関数ではないでしょうか？非常に便利な関数ですが、「検索値が一番左の列にないと使えない」「複数の条件で検索したい」「該当するデータをすべて表示したい」といった場面では、少し物足りなさを感じることもあります。

この記事では、あなたがアップロードしてくださった画像を参考に、VLOOKUPの弱点をカバーできる、あるいはもっと強力なデータ操作が可能なExcel関数をいくつかご紹介します。

この記事の内容

ExcelのVLOOKUP関数を説明します。
ExcelのVLOOKUP関数に代わる関数「INDEX+MATCH」「XLOOKUP」「FILTER」を説明します。

今回のExcelのデータ検索のお題
Excelの検索関数の解説

今回のExcelのデータ検索のお題

今回は次の社員マスターテーブルから、佐藤さんの部署や社員IDを取得するタスクを考えます。なお、佐藤さんは同性が2名いる前提のテーブルとして、各種関数の違いを見ていきましょう。

Excelの検索関数の解説

おなじみVLOOKUPの基本と弱点

まずは、VLOOKUPの基本的な使い方をおさらいしましょう。

=VLOOKUP(検索値, 範囲, 列番号, 検索方法)

検索値: 探したい値
範囲: 検索する表の範囲
列番号: 範囲の左端から数えて、取得したいデータがある列の番号
検索方法: TRUE（近似一致）またはFALSE（完全一致）

画像のタスクの例では、社員ID 102を検索値として、社員名と部署を取得する例を表します。

しかし、このVLOOKUPには大きな弱点があります。それは、「検索値は必ず指定した範囲の一番左の列になければならない」という制約です。例えば、「社員名から社員IDを検索したい」という場合には、VLOOKUPだけでは対応できません。

この弱点を克服するために登場するのが、これからご紹介する関数たちです。

INDEX+MATCH関数

INDEX関数とMATCH関数を組み合わせることで、VLOOKUPの「左端しばり」から解放されます。

MATCH関数: 指定した範囲内で、検索値が何番目にあるかを返します。
=MATCH(検索値, 検索範囲, 検索方法)
INDEX関数: 指定した範囲の中から、行と列の位置を指定して値を取り出します。
=INDEX(参照範囲, 行番号, [列番号])

この２つを組み合わせると、次のようになります。

=INDEX(取得したいデータの列, MATCH(検索値, 検索したい値がある列, 検索方法))

画像にある例を見てみましょう。

MATCH(B15, B2:B4, 0): 検索値B15（佐藤）がB2:B4（氏名列）の何番目にあるかを探します。結果は2となります（佐藤さんが上から2番目）。
INDEX(C2:C4, MATCH(B15, B2:B4, 0)): C2:C4（部署列）の中から、MATCH関数で求められた2番目の値、つまり「総務部」を取得します。
INDEX(A2:A4, MATCH(B15, B2:B4, 0)): A2:A4（社員ID列）の中から、MATCH関数で求められた2番目の値、つまり「102」を取得します。

このように、MATCHで位置を特定し、INDEXでその位置のデータを取得することで、検索列が左端になくても自由にデータを検索できるようになります。

高機能なXLOOKUP関数（Excel2021以降）

比較的新しいバージョンのExcel（Excel2021以降）をお使いなら、ぜひ活用したいのがXLOOKUP関数です。VLOOKUPとHLOOKUPを統合し、さらに機能を強化したような関数です。

=XLOOKUP(検索値, 検索範囲, 戻り配列, [見つからない場合], [一致モード], [検索モード])

検索値: 探したい値
検索範囲: 検索値が存在する範囲
戻り配列: 検索値が見つかった場合に取得したいデータがある範囲
[見つからない場合]: 検索値が見つからなかった場合に表示する値（省略可）
[一致モード]: 検索方法（0:完全一致、-1:次いで小さい項目、1:次いで大きい項目、2:ワイルドカード）（省略可、既定は0）
[検索モード]: 検索の方向（1:先頭から末尾、-1:末尾から先頭、2:昇順でバイナリ検索、-2:降順でバイナリ検索）（省略可、既定は1）

画像にある例を見てみましょう。検索値「佐藤」から部署と社員IDを検索しています。

部署: =XLOOKUP(B21, B2:B4, C2:C4, "見つかりません")
- 検索値: B21（佐藤）
- 検索範囲: B2:B4（氏名列）
- 戻り配列: C2:C4（部署列）
- 見つからない場合: “見つかりません” 「氏名列の中から佐藤を探し、見つかった行の部署列のデータを返す」という操作が、非常にシンプルに記述できます。検索範囲と戻り配列が分かれているため、VLOOKUPのように「左端にあるか」を気にする必要がありません。
社員ID: =XLOOKUP(B21, B2:B4, A2:A4, "見つかりません")
- 検索値: B21（佐藤）
- 検索範囲: B2:B4（氏名列）
- 戻り配列: A2:A4（社員ID列）「氏名列の中から佐藤を探し、見つかった行の社員ID列のデータを返す」という操作も同様に簡単です。

XLOOKUPは、INDEX+MATCHよりも直感的で分かりやすく、さらに「見つからない場合」の表示を指定できたり、末尾から検索したりといった柔軟な使い方が可能です。

複数の条件や結果を抽出できるFILTER関数（Excel2021以降）

最後に紹介するのは、Excel2021から利用できるFILTER関数です。これはこれまでの関数とは少し異なり、「条件に一致するデータをすべて抽出する」という強力な機能を持っています。

=FILTER(配列, 含む, [空の場合])

配列: 抽出したいデータが含まれる範囲
含む: 抽出条件（TRUEまたはFALSEを返す論理式）
[空の場合]: 抽出結果が空だった場合に表示する値（省略可）

画像にある例を見てみましょう。検索値「佐藤」を含む行をフィルタリングしています。

部署: =FILTER(C2:C4, B2:B4=B26)
- 配列: C2:C4（部署列）
- 含む: B2:B4=B26（氏名列が検索値B26（佐藤）と一致するかどうか）氏名が「佐藤」である行の部署をすべて抽出します。画像では「総務部」「開発部」の2つが表示されています。これは、元のデータに「佐藤」さんが2名いることを想定しているためですね。VLOOKUPやINDEX+MATCH、XLOOKUPが最初に見つかった1件だけを返すのに対し、FILTERは該当するものをすべて返せる点が大きな違いです。
社員ID: =FILTER(A2:A4, B2:B4=B26)
- 配列: A2:A4（社員ID列）
- 含む: B2:B4=B26（氏名列が検索値B26（佐藤）と一致するかどうか）氏名が「佐藤」である行の社員IDをすべて抽出します。「102」「103」が表示されています。

FILTER関数は、単に一つの値を検索するだけでなく、特定の条件を満たすレコード全体を一覧で表示したい場合に非常に役立ちます。

まとめ

関数	特徴	こんな時にオススメ	注意点
VLOOKUP	最も一般的。左端の列を検索して右の列の値を取得。	簡単な検索・取得。Excel初心者でも扱いやすい。	検索値が左端にないと使えない。複数結果は返せない。
INDEX+MATCH	検索値の位置と取得したいデータの位置を組み合わせて柔軟に検索。	VLOOKUPの左端制約を回避したい。古いExcelバージョンでも使える。	VLOOKUPより数式が少し複雑になる。複数結果は返せない。
XLOOKUP	VLOOKUP/HLOOKUPを統合・強化。シンプルで高機能。（Excel2021以降）	簡単かつ柔軟に検索・取得したい。見つからない場合の指定もしたい。	対応バージョンに限りがある。複数結果は返せない。
FILTER	条件に一致するデータをすべて抽出。（Excel2021以降）	複数条件での抽出や、条件に合うデータを一覧表示したい。	対応バージョンに限りがある。