【エクセル】INDEX関数とMATCH関数で対応値を検索する方法

2023 1/22

2023-01-22

ExcelのVLOOKUP関数と同じような検索機能をINDEX関数とMATCH関数を組み合わせて実現することが可能です。INDEX関数とMATCH関数を組み合わせた方法はその汎用性の高さからVLOOKUPの代わりに使われることが多くあります。得にVLOOKUP関数では不可能な検索値に対して左側の値も取得できるのがこの方法の大きな特徴です。ここではINDEX関数とMATCH関数を合わせてVLOOKUP関数と同じように検索する方法について解説します。また後半では様々な応用テクニックについても合わせて解説します。

INDEX関数とMATCH関数を組み合わせてVLOOKUPのように検索する数式を作成している様子

関連リンク

【エクセル】VLOOKUP関数の使い方・テクニック完全ガイド

【エクセル】XLOOKUP関数の使い方(VLOOKUPの後継関数)

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

INDEX関数とMATCH関数を組み合わせてVLOOKUP関数のように検索することのメリット下記の通り。

VLOOKUP関数では不可能である検索値より左側の値を取得可能
汎用性が高く様々なテクニックへの応用が効く

基本構文

下記がINDEX関数とMATCH関数を組み合わせてVLOOKUPのように検索する構文となります。対応範囲と検索範囲は同じ高さ（同じ行数）である必要があります。

＝INDEX（対応範囲,MATCH(検索値,検索範囲,0））

対応範囲：検索値に対して返される対応値の範囲を指定します。
検索値：検索する値を指定します。
検索範囲：検索する値が含まれる範囲を指定します。

手順

実際にINDEX関数とMATCH関数を使って、下記のような商品のリストから、IDに対応した商品名を返す数式の作成手順を解説します。

ここで入力する数式

=INDEX(C3:C12,MATCH(F3,B3:B12,0))

１：「=INDEX(」を入力

空白セルに「=INDEX(」と入力します。
入力途中で予測関数が表示されるので、INDEX関数の箇所でTABキーを押下すると自動入力されます。

２：対応範囲を指定

対応範囲を指定します。対応範囲とは検索値に対して対応する値がある範囲となります。この例ではIDを検索値として商品名を返したいので、商品名の列を指定します。

３：「MATCH(」と入力

「MATCH(」と入力します。INDEX関数の２つ目の引数である行番号にあたる部分をMATCH関数をそのまま入れて、検索値が上から何番目にあるかを数値で返します。

４：検索値を指定

MATCH関数の１つ目の引数として検索値を指定します。この例ではIDを検索値として指定します。

５：検索範囲を指定

検索値が含まれている範囲を指定します。この例でいうとIDの列のデータ全てとなります。

完成

「))」と入力し関数を閉じてENTERを押下すると完成です。VLOOKUP関数のように検索値に対応する値を返すことができました。この例では検索値が左側にありますが、検索値を右側において検索も可能ですのでVLOOKUP関数に比べて柔軟に対応できます。

INDEX関数とMATCH関数で複数条件での検索をする方法

INDEX関数とMATCH関数を組み合わせて、複数条件で検索する方法です。注意点としては数式を入力し、確定する際に「CTRL + SHIFT + ENTER」を押下し、数式を「｛｝」で囲む必要があります。「{}」は直接入力するエラーになるので、必ずCTRL + SHIFT + ENTERで対応してください。

書式

{=INDEX(データ範囲,MATCH(検索値A&検索値B,検索範囲A&検索範囲B,0),列番号)}

使用例

{=INDEX(B6:D17,MATCH(B3&C3,B6:B17&C6:C17,0),3)}

詳しい手順やその他の応用テクニックは下記リンクを参照してください。

VLOOKUP関数を複数条件で検索する方法まとめ

INDEX関数とMATCH関数での検索をVBAで行う方法

INDEX関数とMATCH関数をあわせて検索している様子。検索値がない場合は該当なしと表示する仕様

INDEX関数とMATCH関数を組み合わせて行う検索はVBAでも実現できます。下記はINDEX関数とMATCH関数を組み合わせて検索しているサンプルコードです。検索値が見つからない場合は「該当なし」と表示される機能をつけています。これによって、検索値が見つからなくても処理が止まらずに済みます。

Sub indexMatch()

    Dim sId, srchArea, resArea, result

    Set sId = Worksheets("入力").Range("A2")  '検索値を指定
    Set srchArea = Worksheets("マスタデータ").Range("A2:A51")  '検索範囲を指定
    Set resArea = Worksheets("マスタデータ").Range("B2:B51")  '対応範囲を指定
    Set result = Worksheets("入力").Range("B2")  '結果を出力するセルを定義
    
    On Error Resume Next '以降はエラー回避

    'INDEX + MATCH実行
    result.Value = _
    WorksheetFunction.Index(resArea, WorksheetFunction.Match(sId, srchArea, 0))

    If Err.Number <> 0 Then  'エラーナンバーが０以外の場合
        result.Value = "該当なし"  '結果のセルに「該当なし」と表示
    End If

    On Error GoTo 0  'エラー回避を解除

End Sub

INDEX関数とMATCH関数での検索がうまくいかない場合の対処方法

INDEX関数とMATCH関数での検索がうまく行かない場合の対処方法について返ってくるエラー別に解説します。

「#N/A」エラーが出る場合

INDEX関数とMATCH関数を組み合わせた検索方法でも検索値が見つからない場合はVLOOKUP関数と同じように#N/Aエラーを返します。検索値が本当にない場合は正しいエラーなのですが、検索値があるはずなのにエラーが出ている場合は下記のような要因が考えられます。

検索値に全角、半角が正しく入力されていない
余計な空白が入っている
検索値の書式設定が文字列と数値で異なる
セル参照が正しくない
検索の型が「TRUE」もしくは「1」が設定されている

「#NAME?」エラーが出る場合

セルに「#NAME?」と表示されたら多くの場合、関数名に入力ミスがあります。正しいスペルかどうか確認しましょう。このようなスペルミスを防ぐためには普段から関数の自動入力機能を使用することをおすすめします。関数名を何文字か入力すると関数名のサジェスト一覧が表示されるので、目的の関数を選んでTABキーを押下することで入力できます。

正しくない値が返ってくる場合

上記のように正しくない値が返ってくる原因の多くはMATCH関数の第3引数の「照合の種類」を指定していない場合に起きます、必ずMATCH関数の第3引数は「0」の完全一致に設定しましょう。

NGの数式

=INDEX(C3:C12,MATCH(E3,B3:B12))

正しい数式

=INDEX(C3:C12,MATCH(E3,B3:B12,0))

INDEX関数とMATCH関数で別シートのデータを検索する方法

INDEX関数とMATCH関数で別シートのデータを検索する数式を入力している様子

マスタデータなどの別シートを数式内で参照したい場合は数式入力中にシートを切り替えてセルを選択するだけ設定可能です。手打ちで設定したい場合は参照文字列を「Sheet1!A1」のように「シート名＋ ! ＋セル番号」の形にすることで参照できます。

書式

=INDEX(シート名!対応範囲,MATCH(シート名!検索値,シート名!検索範囲,0))

使用例

=INDEX(マスタデータ!B2:B51,MATCH(入力!A2,マスタデータ!A2:A51,0))

INDEX関数とMATCH関数の代わりにXLOOKUP関数を使用する方法

Microsoftの「Office 365」もしくは「Office 2021」以降のバージョンではVLOOKUP関数の後継関数である「XLOOKUP」が使えます。この関数はINDEX関数とMATCH関数の組み合わせることで出来る汎用性も兼ね備えている非常に便利な関数です。XLOOKUP関数のポイントは下記の通り

XLOOKUP関数のポイント

検索値を含む列を左端にしなくても良い
一致モードのデフォルトが完全一致
戻り値を一気に表示可能
垂直配列と水平配列の両方で機能
逆検索を実行可能

XLOOKUP関数の書式

XLOOKUP関数には設定項目が6つありますがそのうち3つは省略可能で「検索値」、「検索範囲」、「戻り範囲」の3つ設定すれば機能します。

＝XLOOKUP（検索値,検索範囲,戻り範囲,見つからない場合］,［一致モード］,検索モード］）

検索値：検索値には検索する値を指定します。例えば、下記の例ではF3に指定しているIDを検索値として指定します。
検索範囲：検索範囲を指定します。
戻り範囲：結果として返す戻り値が入っている範囲を指定します。
見つからない場合］：検索値のデータが見つからない場合に返す値を指定します。
［一致モード］：検索する方法を指定します。デフォルトは「完全一致」になり、検索値が検索範囲内の文字列に完全一致した場合のみ値を返します。
検索モード］：検索モードを指定。デフォルトでは、先頭から末尾へ検索されます。

XLOOKUP関数についての詳しい解説は下記リンクを参照してください。

XLOOKUP関数の使い方(VLOOKUPの後継関数)

ショートカットキーの練習できます

当サイトはショートカットキーの練習を実際の動きを確認しながら練習できる機能がついています。繰り返し指を動かして練習ができるので、ゲーム感覚で遊んでいくうちに自然とショートカットキーが使えるようになれます。ショートカットキーは暗記するよりも実際に手を動かして練習していったほうが習得が早くなるので、是非当サイトをブックマークに登録し定期的に練習してみてください。下記のSTARTキーを押下すると練習を始められます。

下のスタートボタンを押して練習モードに入ります。

STARTSTOP

※入力モード切り替え ctrl + shift + alt + space

PRE

現在の問題をリストから除外

除外したリストをリセット

現在の除外状況のURL取得

コピー

ガイドモード

リピートモード

※推奨ブラウザ Google Chrome

もっとショートカットキーの練習をする

PUSH ENTER

よかったらシェアしてね！

URLをコピーしました！

URLをコピーしました！