Excel の INDEX 関数と MATCH 関数：基本的な検索と高度な検索

Excel では、特定のデータを正確に取得することが頻繁に求められます。INDEX 関数と MATCH 関数はそれぞれ独自の強みを持っていますが、これらを組み合わせることで、データ検索のための非常に強力なツールセットが実現します。両関数を併用すれば、基本的な横方向・縦方向の検索はもちろん、双方向検索や大文字小文字を区別する検索、さらには複数条件による検索といった高度な機能まで幅広く活用できます。VLOOKUP よりも拡張性に優れたこの「INDEX＋MATCH」の組み合わせにより、より柔軟で多様なデータ検索が可能になります。本チュートリアルでは、この2 つの関数が一体となって実現できる可能性を、さらに深く掘り下げていきます。

• INDEX 関数
• MATCH 関数
• INDEX 関数と MATCH 関数を組み合わせる

• 双方向検索
• 左方向検索
• 大文字小文字を区別する検索
• 最も近い値による検索
• 複数条件による検索
• 複数列にわたる検索
• 最初の非空白セルの値を検索
• 最初の数値を検索
• MAX または MIN に関連する値を検索
• ヒント：独自の#N/A エラーメッセージをカスタマイズできます

Excel で INDEX 関数と MATCH 関数を使用する方法

INDEX 関数と MATCH 関数を使う前に、まずはこれらの関数がどのようにして値の検索をサポートしてくれるのかを確認しておきましょう。

Excel で INDEX 関数を使用する方法

Excel のINDEX 関数は、指定された範囲内の特定の位置にある値を返す関数です。構文は次のとおりです。

• array（必須）は、値を取得する範囲を指定します。
• row_num（必須。ただし、)column_numが指定されている場合は除く）は、配列内の行番号を指定します。
• column_num（省略可。ただし、)row_numを省略する場合は必須）は、配列内の列番号を指定します。

たとえば、リストの6 番目の学生である Jeff の得点 6を調べるには、次のように INDEX 関数を使えます。

=INDEX(C2:C11,6)

√ 注：範囲 C2:C11は得点が記載されているセル範囲であり、数値 6は6 番目の学生の試験得点を示しています。

ここで簡単なテストをしてみましょう。数式 =INDEX(A1:C1,2)はどのような値を返すでしょうか？――はい、これは指定された行の2 番目の値、つまり生年月日を返します。

これで、INDEX 関数が横方向または縦方向の範囲で完璧に動作することがわかりました。しかし、複数の行と列を持つ広い範囲から値を取得する必要がある場合はどうすればよいでしょうか？そのような場合には、行番号と列番号の両方を指定する必要があります。たとえば、単一の列ではなくテーブル全体の範囲内でJeff の得点を調べるには、行番号6と列番号3を指定し、A2 から C11 までのセル範囲から次のように得点を特定できます。

=INDEX(A2:C11,6,3)

ただし、行と列が多数存在する大規模なデータベースでは、正確な行番号と列番号を直接指定して数式を作成するのは明らかに不便です。こうした場面こそ、MATCH 関数を組み合わせるべきタイミングです。

Excel で MATCH 関数を使用する方法

Excel の MATCH 関数は、指定された範囲内で特定の項目がどの位置にあるかを示す数値を返します。MATCH 関数の構文は次のとおりです。

• lookup_value（必須）は、lookup_array 内で照合する値を指定します。
• lookup_array（必須）は、MATCH 関数が検索対象とするセル範囲を指定します。
• match_type（省略可）：1、0、または-1。
  • 1（デフォルト）の場合、MATCH 関数はlookup_value 以下の最大値を検索します。lookup_array 内の値は昇順で並べる必要があります。
  • 0の場合、MATCH 関数はlookup_valueと完全に一致する最初の値を検索します。lookup_array 内の値は任意の順序で配置可能です。（一致タイプが0 に設定されている場合は、ワイルドカード文字も使用できます。）
  • -1の場合、MATCH 関数はlookup_value 以上の最小値を検索します。lookup_array 内の値は降順で並べる必要があります。

たとえば、名前リスト内のVera の位置を知るには、「数式を区別する」機能を次のように使えます。

=MATCH("Vera",A2:A11,0)

※注：結果の「4」は、「Vera」という名前がリストの4 番目の位置にあることを示しています。

これで、Excel における INDEX 関数と MATCH 関数の基本的な使い方がわかりました。次は、両関数を組み合わせて実際に活用してみましょう！

Excel で INDEX 関数と MATCH 関数を組み合わせる方法

下記の例を参考に、INDEX 関数と MATCH 関数をどのように組み合わせられるかご確認ください。

Evelyn の得点を検索する際、試験得点が3 列目にあることが分かっていれば、MATCH 関数で行位置を自動的に特定し、手動で数える手間を省けます。その後、特定された行と3 列目の交差点にある値を取得するために INDEX 関数を使いましょう。

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),3)

この数式は少しだけ複雑に見えるかもしれませんが、各部分を順を追って確認していきましょう。

このINDEX 関数には3 つの引数があります。

• row_num：MATCH(「Evelyn」,A2:A11,0)は、範囲 A2:A11 内で"Evelyn"の位置を検索し、その行番号 5を返して、INDEX 関数に渡します。
• column_num：3は、配列内でスコアを検索する際に INDEX 関数に指定する3 列目を示します。
• array：A2:C11は、INDEX 関数に、指定された行と列の交差点にある一致する値をA2 から C11の範囲内で返すよう指示します。その結果、90が得られます。

上記の数式では、「Evelyn」というハードコードされた値を使用しています。しかし実際の業務では、別の学生の得点など異なるデータを検索するたびに数式を修正しなければならず、ハードコードは非現実的です。このようなケースでは、セル参照を使って動的な数式を作成できます。たとえば、ここでは「Evelyn」を F2 に変更します。

=INDEX(A2:C11,MATCH(F2,A2:A11,0),3)

INDEX および数式を区別するの使用例

ここでは、さまざまな要件に対応するための INDEX 関数と MATCH 関数の活用シナリオをご紹介します。

双方向検索に INDEX と MATCH を適用する

前の例では列番号がわかっていたため、「数式を区別する」機能を使って行番号を特定しました。しかし、列番号もわからない場合は、どのように対応すればよいでしょうか？

このような場合は、MATCH 関数を2 つ使って双方向検索（マトリックス検索とも呼ばれます）が可能です。1 つは行番号を、もう1 つは列番号を特定します。たとえば、Evelyn の得点を調べるには、次の数式を使いましょう。

=INDEX(A2:C11,MATCH("Evelyn",A2:A11,0),MATCH("Score",A1:C1,0))

左方向検索に INDEX と MATCH を適用する

次に、Evelyn のクラスを特定する必要があるシナリオを考えてみましょう。すでにお気づきかもしれませんが、クラス列は名前列の左側にあります。このような場合、他の強力なExcel 検索関数である VLOOKUP では対応できません。

実際、左方向の検索が可能である点は、INDEX と MATCH の組み合わせが VLOOKUP を上回る大きな特長の一つです。

Evelyn のクラスを検索するには、次の数式を使って、B2:B11 から Evelyn を検索し、対応する値を A2:A11 から取得します。

=INDEX(A2:A11,MATCH("Evelyn",B2:B11,0))

大文字小文字を区別する検索に INDEX と MATCH を適用する

MATCH 関数は本来、大文字と小文字を区別しません。しかし、数式で大文字と小文字を厳密に区別する必要がある場合は、EXACT 関数を組み込むことでその機能を強化できます。INDEX 数式内で MATCH 関数と EXACT 関数を組み合わせれば、以下のように大文字と小文字を区別した検索を効果的に実行できます。

• arrayは、値を返す範囲を指定します。
• lookup_valueは、大文字と小文字を区別して照合する値をlookup_array 内で指定します。
• lookup_arrayには、lookup_valueと比較するセル範囲を指定します。

たとえば、JIMMY の試験得点を調べるには、次の数式を使います。

=INDEX(C2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0))

√ 注：これは配列数式です。Ctrl+Shift+Enter で入力する必要があり、Excel 365 や Excel 2021 以降のバージョンを除き、この操作が必須です。

最も近い値を検索するのに INDEX と MATCH を適用する

Excel では、データセット内の特定の値に最も近い値を見つける必要がある状況に遭遇することがあります。このようなケースでは、INDEX 関数と MATCH 関数に加えて ABS 関数および MIN 関数を組み合わせることで、非常に効果的に対応できます。

• arrayは、値を返す範囲を指定します。
• lookup_arrayは、lookup_valueに最も近い値を検索するための値の範囲を指定します。
• lookup_valueは、最も近い値を探すもとになる値を指定します。

たとえば、誰の得点が85 に最も近いかを調べるには、次の数式を使って、C2:C11 の範囲から85 に最も近い得点を検索し、その対応する名前を A2:A11 から取得します。

=INDEX(A2:A11,MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0))

√ 注：これは配列数式です。Ctrl+Shift+Enter で入力する必要があります（Excel 365 および Excel 2021 以降のバージョンを除く）。

複数条件による検索に INDEX と MATCH を適用する

複数の条件を満たす値を検索する必要があり、2 つ以上の列を横断して検索しなければならない場合は、次の数式を使用してください。この数式により、異なる列にわたって複数の条件を指定し、すべての条件を満たす目的の値を検索できます。

√ 注：これは配列数式です。Ctrl＋Shift＋Enterで入力すると、数式バーに中括弧のペアが表示されます。

• arrayは、値を返す範囲を指定します。
• (lookup_value=lookup_array)は単一の条件を表します。この条件では、特定のlookup_valueがlookup_array 内の値と一致するかどうかをチェックします。

たとえば、クラス A のココさんで誕生日が7/2/2008の方のスコアを検索するには、次のような数式を使用できます。

=INDEX(D2:D11,MATCH(1,(G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0))

複数の列にわたる検索に INDEX 関数と MATCH 関数を使用する

複数のデータ列を扱うシナリオを考えてみましょう。最初の列はキーとして機能し、他の列のデータを分類します。特定のエントリのカテゴリや分類を特定するには、データ列全体を検索し、参照列内の該当するキーと関連付ける必要があります。

たとえば、以下の表で、生徒のショーンさんをそのクラスと一致させるにはどうすればよいでしょうか？INDEX 関数と MATCH 関数を使えば数式で実現できますが、その数式は非常に長く、理解するのはもちろん、記憶したり入力したりするのは困難です。

=IFERROR(INDEX($A$2:$A$4,MATCH(IF(SUM(MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0)))>0,1,-1),MMULT(--($B$2:$E$4=G2),TRANSPOSE(COLUMN($B$2:$E$4)^0))^0,0)), "")

ここで役立つのが、Kutools for Excelの複数の列をインデックスとマッチします機能です。この機能を使えば、特定のエントリを対応するカテゴリに素早く簡単に一致させることができます。この強力なツールでショーンさんのクラスを手軽に一致させるには、まずKutools for Excel アドインをダウンロードしてインストールし、次のように操作してください。

1. 一致するクラスを表示する目的のセルを選択します。
2. Kutoolsタブで、関数ヘルパー > 検索と参照 > 複数の列をインデックスとマッチしますをクリックしてください。
3. 
3. 表示されたダイアログボックスで、次のように操作します：
   1. 最初のボタンをLookup_colの横でクリックし、返したいキー情報（ここではクラス名）を含む列を選択してください。（選択できるのは単一の列のみです。）
   2. 2 番目のボタンをTable_rngの横でクリックし、選択したLookup_col 内の値（ここでは学生名）と照合するセル範囲を指定してください。
   3. 3 番目のボタンをLookup_valueの横でクリックし、クラスと照合したい学生名（ここでは Shawn）が含まれるセルを選択します。
   4. OKをクリックしてください。
   5. 

結果

Kutools が自動的に数式を生成し、ショーンさんのクラス名が即座に目的のセルに表示されます。

最初の空白以外の値を検索するための INDEX 関数と MATCH 関数

列または行からエラーを無視して最初の空白以外の値を取得するには、INDEX 関数と MATCH 関数に基づいた数式を使用できます。ただし、範囲内のエラーを無視したくない場合は、ISBLANK 関数を追加してください。

• エラーを無視して列または行で最初の非空白セルの値を取得する：

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B15<>0),0),0))

• エラーを含めて列または行で最初の非空白セルの値を取得する：

• =INDEX(B4:B15,MATCH(FALSE,ISBLANK(B4:B15),0))

  

最初の数値を検索するための INDEX 関数と MATCH 関数

列または行から最初の数値を取得するには、INDEX 関数、MATCH 関数、および ISNUMBER 関数に基づいた数式を使用します。

=INDEX(B4:B15,MATCH(TRUE,ISNUMBER(B4:B15),0))

最大値または最小値に関連付けられた値を検索するための INDEX 関数と MATCH 関数

範囲内で最大値または最小値に関連付けられた値を取得する必要がある場合は、MAX 関数または MIN 関数を INDEX 関数および MATCH 関数と組み合わせて使用できます。

• 最大値に関連付けられた値を取得するための INDEX 関数と MATCH 関数：
• =INDEX(array, MATCH(MAX(lookup_array), lookup_array, 0))
• 最小値に関連付けられた値を取得するための INDEX 関数と MATCH 関数：
• =INDEX(array, MATCH(MIN(lookup_array), lookup_array, 0))
• 上記の数式には2 つの引数があります：
• • arrayは、関連情報を取得したいセル範囲を指します。
  • lookup_arrayは、特定の条件（たとえば最大値や最小値など）に基づいて検索・評価される値のセットを表します。

たとえば、誰が最も高いスコアを持っているかを調べるには、次の数式を使いましょう。

=INDEX(A2:A11,MATCH(MAX(C2:C11),C2:C11,0))

ヒント：独自の#N/A エラーメッセージをカスタマイズできます

Excel の INDEX 関数と MATCH 関数を使用する際、一致する結果がない場合に#N/A エラーが発生することがあります。たとえば、以下の表でサマンサという名前の生徒のスコアを検索しようとすると、データセット内に彼女が存在しないため#N/A エラーが表示されます。

スプレッドシートをよりユーザーフレンドリーにするには、IFNA 関数で INDEX 数式を囲み、このエラーメッセージをカスタマイズできます。

=IFNA(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

以上が、Excel における INDEX 関数と MATCH 関数に関するすべての関連コンテンツです。このチュートリアルがお役に立てれば幸いです！さらに多くのExcel ヒントやテクニックを知りたい方は、こちらをクリックしてください。数千件以上のチュートリアルを集めた豊富なコレクションをぜひご活用ください！