メインコンテンツへスキップ

Kutools for Office — 1つのスイート、5つのツールで、もっと多くのことを成し遂げましょう。

Excel INDEX MATCH: 基本および高度な検索

Author Amanda Li Last modified

Excelでは、特定のデータを正確に取得することが頻繁に必要です。INDEX関数とMATCH関数はそれぞれ独自の強みを持っていますが、これらを組み合わせることでデータ検索のための強力なツールセットを解放します。これらを組み合わせることで、基本的な水平および垂直検索から、双方向、ケースセンシティブ、複数条件検索などの高度な機能まで、幅広い検索能力を提供します。VLOOKUPと比較して、INDEXとMATCHの組み合わせは、より広範なデータ検索オプションを可能にします。このチュートリアルでは、これらが一緒に達成できる可能性の深さを探ってみましょう。

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

INDEXとMATCH関数を使用する前に、まずINDEXとMATCHが値の検索にどのように役立つかを確認しましょう。

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

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

=INDEX(array, row_num, [column_num])
  • array（必須）は、値を返したい範囲を指します。
  • row_num（必須、column_numが存在する場合を除く）は、arrayの行番号を指します。
  • column_num（オプション、ただしrow_numが省略された場合は必須）は、arrayの列番号を指します。

例えば、リストの6番目の学生であるジェフのスコアを知るには、次のようにINDEX関数を使用できます。

=INDEX(C2:C11,6)

A screenshot of the result of the INDEX formula returning the score of the 6th student

√ 注: 範囲C2:C11はスコアがリストされている場所であり、数字6は6番目の学生の試験スコアを見つけます。

ここで少しテストをしてみましょう。数式=INDEX(A1:C1,2)では、どの値が返されるでしょうか？--- はい、これは指定された行の2番目の値である誕生日を返します。

今、INDEX関数が水平または垂直の範囲で完璧に機能することを知っているはずです。しかし、複数の行と列を持つより広い範囲で値を返す必要がある場合はどうでしょうか？この場合、行番号と列番号の両方を適用する必要があります。例えば、単一の列ではなくテーブル範囲内でジェフのスコアを見つけるには、A2からC11のセルで行番号6と列番号3を使用して彼のスコアを特定できます。

=INDEX(A2:C11,6,3)

A screenshot of the result of the INDEX formula returning Jeff's score from a table range

ExcelのINDEX関数について知っておくべきこと：
  • INDEX関数は垂直および水平の範囲で機能します。
  • row_numとcolumn_numの引数が両方使用される場合、row_numがcolumn_numの前に来て、INDEXは指定されたrow_numとcolumn_numの交差点で値を取得します。

しかし、複数の行と列を持つ非常に大きなデータベースの場合、正確な行番号と列番号で数式を適用するのは確かに便利ではありません。このとき、MATCH関数の使用を組み合わせるべきです。

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

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

=MATCH(lookup_value, lookup_array, [match_type])
  • 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の値は降順に配置されている必要があります。

例えば、名前リストでヴェラの位置を知るには、次のようにMATCH数式を使用できます。

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

A screenshot showing the result of the MATCH formula returning the position of Vera in the list

√ 注: 結果「4」は、名前「ヴェラ」がリストの4番目の位置にあることを示しています。

ExcelのMATCH関数について知っておくべきこと：
  • MATCH関数は、lookup_array内の検索値の位置を返し、値自体は返しません。
  • MATCH関数は重複の場合、最初の一致を返します。
  • INDEX関数と同様に、MATCH関数も垂直および水平の範囲で機能します。
  • MATCHはケースセンシティブではありません。
  • MATCH数式のlookup_valueがテキスト形式の場合、引用符で囲んでください。
  • lookup_valueがlookup_arrayに見つからない場合、#N/Aエラーが返されます。

ExcelのINDEXとMATCH関数の基本的な使用法を知ったので、袖をまくってこれらの関数を組み合わせる準備をしましょう。

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

以下の例を見て、INDEXとMATCH関数をどのように組み合わせるかを確認してください。

エヴリンのスコアを見つけるには、試験のスコアが3番目の列にあることを知っている場合、MATCH関数を使用して行位置を自動的に決定し、手動で数える必要はありません。その後、INDEX関数を使用して、特定された行と3番目の列の交差点で値を取得できます。

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

A screenshot showing the formula and the result for Evelyn's score

さて、数式が少し複雑に見えるかもしれないので、各部分を確認してみましょう。

A screenshot showing the breakdown of the formula for combining INDEX and MATCH to find Evelyn's score

INDEX数式には3つの引数があります：

  • row_num: MATCH("Evelyn",A2:A11,0)指定された範囲A2:A11内で値"Evelyn"の行位置をINDEXに提供します。5.
  • column_num:3は、INDEXが配列内でスコアを特定するための3番目の列を指定します。
  • array: A2:C11は、指定された行と列の交差点で一致する値を返すようにINDEXに指示します。最終的に、結果は90です。

上記の数式では、ハードコードされた値"Evelyn"を使用しました。しかし、実際には、ハードコードされた値は実用的ではありません。異なるデータを検索するたびに変更が必要になるためです。このようなシナリオでは、セル参照を使用して動的な数式を作成できます。例えば、この場合、"Evelyn"をF2に変更します。

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

(AD) Kutoolsで検索を簡素化: 数式の入力は不要！

Kutools for ExcelのスーパーLOOKUPは、あらゆるニーズに応じたさまざまな検索ツールを提供します。複数条件検索、複数シートにわたる検索、一対多の検索を行う場合でも、スーパーLOOKUPは数回のクリックでプロセスを簡素化します。これらの機能を探索して、スーパーLOOKUPがExcelデータとのやり取りをどのように変えるかを確認してください。複雑な数式を覚える手間を省きましょう。

A screenshot of Kutools for Excel's Super Lookup tools in Excel ribbon

Kutools for Excel - 必要なツールを300以上搭載し、Excelの機能を大幅に強化します。永久に無料で利用できるAI機能もお楽しみください！今すぐ入手

INDEXとMATCHの数式例

この部分では、異なるニーズを満たすためにINDEXとMATCH関数を使用するさまざまな状況について説明します。

INDEXとMATCHを使用して双方向検索を適用する

前の例では、列番号を知っていて、MATCH数式を使用して行番号を見つけました。しかし、列番号も不明な場合はどうでしょうか？

そのような場合、双方向検索、またはマトリックス検索を行うことができます。2つのMATCH関数を使用して、1つは行番号を見つけ、もう1つは列番号を決定します。例えば、エヴリンのスコアを知るためには、次の数式を使用する必要があります。

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

A screenshot showing a two-way lookup using INDEX and MATCH in Excel to find Evelyn's score

この数式の動作：
  • 最初のMATCH数式は、リストA2:A11内のエヴリンの位置を見つけ、INDEXに行番号5を提供します。
  • 2番目のMATCH数式はスコアの列を決定し、INDEXに列番号3を返します。
  • 数式は=INDEX(A2:C11,5,3)に簡略化され、INDEXは90を返します。
INDEXとMATCHを使用して左揃え検索を適用する

さて、エヴリンのクラスを決定する必要があるシナリオを考えてみましょう。クラス列が名前列の左に配置されていることに気づくかもしれません。これは、別の強力なExcel検索関数であるVLOOKUPの能力を超えています。

実際、左側の検索を実行する能力は、INDEXとMATCHの組み合わせがVLOOKUPを上回る側面の1つです。

エヴリンのクラスを見つけるには、B2:B11でエヴリンを検索し、A2:A11から対応する値を取得するために次の数式を使用します。

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

A screenshot showing how to use INDEX and MATCH to find Evelyn's class from a left-side lookup in Excel

注: Kutools for ExcelのLOOKUP from Right to Left機能を使用して、特定の値の左揃え検索を簡単に行うことができます。機能を実装するには、ExcelのKutoolsタブに移動し、数式グループでスーパーLOOKUP > LOOKUP from Right to Leftをクリックします。

A screenshot of the LOOKUP from Right to Left feature in Kutools for Excel

Kutools for Excel - 必要なツールを300以上搭載し、Excelの機能を大幅に強化します。永久に無料で利用できるAI機能もお楽しみください！今すぐ入手

INDEXとMATCHを使用してケースセンシティブ検索を適用する

MATCH関数は本質的にケースインセンシティブです。しかし、数式が大文字と小文字を区別する必要がある場合は、EXACT関数を組み込むことで強化できます。MATCH関数をEXACTと組み合わせてINDEX数式に組み込むことで、以下のようにケースセンシティブ検索を効果的に実行できます。

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array),0))
  • arrayは、値を返したい範囲を指します。
  • lookup_valueは、lookup_array内で文字のケースを考慮して一致させたい値を指します。
  • lookup_arrayは、MATCHがlookup_valueと比較したいセルの範囲を指します。

例えば、JIMMYの試験スコアを知るには、次の数式を使用します。

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

√ 注:これは配列数式であり、Excel365、Excel2021、および新しいバージョンを除いてCtrl + Shift + Enterで入力する必要があります。

A screenshot showing how to use INDEX and MATCH with EXACT for a case-sensitive lookup in Excel

この数式の動作：
  • EXACT関数は、リストA2:A11内の値と"JIMMY"を比較し、文字のケースを考慮します。2つの文字列が大文字と小文字を含めて正確に一致する場合、EXACTはTRUEを返し、そうでない場合はFALSEを返します。その結果、TRUEとFALSEの値を含む配列が得られます。
  • MATCH関数は、配列内の最初のTRUE値の位置を取得し、それは10であるべきです。
  • 最後に、INDEXは配列内でMATCHによって提供された10番目の位置の値を取得します。

注:

  • Excel365、Excel2021、および新しいバージョンを使用している場合を除き、Ctrl + Shift + Enterを押して数式を正しく入力してください。その場合は、Enterを押すだけです。
  • 上記の数式は、単一のリストC2:C11内で検索します。複数の列と行を持つ範囲内で検索したい場合は、A2:C11を指定し、INDEXに列番号と行番号の両方を提供する必要があります。
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • この修正された数式では、MATCH関数を使用して、文字のケースを考慮して範囲A2:A11内で"JIMMY"を検索し、一致が見つかったら、範囲A2:C11の3番目の列から対応する値を取得します。
INDEXとMATCHを使用して最も近い一致を見つける

Excelでは、データセット内の特定の値に最も近い一致を見つける必要がある状況に遭遇するかもしれません。そのようなシナリオでは、INDEXとMATCH関数の組み合わせに加えて、ABSとMIN関数を使用することが非常に役立ちます。

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 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))

√ 注:これは配列数式であり、Excel365、Excel2021、および新しいバージョンを除いてCtrl + Shift + Enterで入力する必要があります。

A screenshot demonstrating how to use INDEX and MATCH with ABS and MIN functions to find the closest match in Excel

この数式の動作：
  • ABS(C2:C11-85)は、範囲C2:C11内の各値と85の絶対差を計算し、絶対差の配列を生成します。
  • MIN(ABS(C2:C11-85))は、絶対差の配列内の最小値を見つけ、85に最も近い差を表します。
  • MATCH関数MATCH(MIN(ABS(C2:C11-85)),ABS(C2:C11-85),0)は、絶対差の配列内の最小絶対差の位置を見つけ、それは10であるべきです。
  • 最後に、INDEXは、範囲C2:C11内で85に最も近いスコアに対応するリストA2:A11の位置の値を取得します。

注:

  • Excel365、Excel2021、および新しいバージョンを使用している場合を除き、Ctrl + Shift + Enterを押して数式を正しく入力してください。その場合は、Enterを押すだけです。
  • 同点の場合、この数式は最初の一致を返します。
  • 平均スコアに最も近い一致を見つけるには、数式内の85をAVERAGE(C2:C11)に置き換えます。
INDEXとMATCHを使用して複数条件による検索を適用する

複数の条件を満たす値を見つけるために、2つ以上の列にわたって検索する必要がある場合、次の数式を使用します。この数式は、異なる列にわたってさまざまな条件を指定することで、複数条件による検索を行い、指定されたすべての条件を満たす望ましい値を見つけることができます。

=INDEX(array, MATCH(1, (lookup_value1=lookup_array1) * (lookup_value2=lookup_array2) * (…),0))

√ 注:これは配列数式であり、Ctrl + Shift + Enterで入力する必要があります。数式バーに一対の中括弧が表示されます。

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

例えば、クラスAのココのスコアを見つけるには、誕生日が2008年7月2日である場合、次の数式を使用できます。

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

A screenshot demonstrating the use of INDEX and MATCH for multi-criteria lookup in Excel

注:

  • この数式では、ハードコードされた値を避け、セルG2、G3、およびG4の値を変更することで異なる情報でスコアを簡単に取得できます。
  • 数式を入力するには、 Ctrl + Shift + Enter を押してください。 Excel365, Excel2021 または 新しいバージョンでは、 Enter.
    を押すだけです。 もし Ctrl + Shift + Enter を使用して数式を完了し、誤った結果を得ることを忘れてしまう場合は、次の少し複雑な数式を使用してください。これにより、簡単に Enter 
    =INDEX(D2:D11,MATCH(1,INDEX((G2=A2:A11)*(G3=B2:B11)*(G4=C2:C11),0,1),0))
  • キーで完了できます。 数式は複雑で覚えるのが難しいことがあります。手動で数式を入力する必要なく、複数条件による検索を簡素化するために、Kutools for Excel の 複数条件検索 機能を使用してください。Kutoolsをインストールしたら、Excelの Kutools タブに移動し、 > スーパーLOOKUP 複数条件検索 をクリックします。 数式

    A screenshot of the Multi-condition Lookup feature in Kutools for Excel

    Kutools for Excel - 必要なツールを300以上搭載し、Excelの機能を大幅に強化します。永久に無料で利用できるAI機能もお楽しみください！今すぐ入手

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)), "")

A screenshot of the formula used to apply a lookup across multiple columns

そこで、Kutools for Excelの複数の列のインデックスと一致機能が役立ちます。プロセスを簡素化し、特定のエントリを対応するカテゴリと簡単に一致させることができます。この強力なツールをアンロックして、ショーンを彼のクラスと簡単に一致させるには、Kutools for Excelアドインをダウンロードしてインストールし、次の手順を実行してください。

  1. 一致するクラスを表示したい宛先セルを選択します。
  2. Kutoolsタブで、数式ヘルパー > 検索 &参照 > 複数の列のインデックスと一致をクリックします。
  3. A screenshot of the Index and Match on Multiple Columns option on the Kutools tab in Excel
  4. ポップアップダイアログボックスで、次の手順を実行します。
    1. 1番目の A screenshot of the range selection button in the Fomrulas Helper dialog ボタンをクリックして 検索列 に戻したいキー情報を含む列を選択します。つまり、クラス名です。（ここでは単一の列のみ選択できます。）
    2. 2番目の A screenshot of the range selection button in the Fomrulas Helper dialog ボタンをクリックして テーブル範囲 で選択した 検索列の値を一致させるセルを選択します。つまり、学生名です。
    3. 3番目の A screenshot of the range selection button in the Fomrulas Helper dialog ボタンをクリックして 検索値 で学生の名前を選択し、彼のクラスと一致させます。この場合、ショーンです。
    4. OKをクリックします。
    5. A screenshot of the Fomrulas Helper dialog

結果

Kutoolsは自動的に数式を生成し、宛先セルにショーンのクラス名がすぐに表示されます。

A screenshot of the formula generated by Kutools locating Shawn's class name from a table

注: 複数の列のインデックスと一致機能を試すには、コンピュータにKutools for Excelをインストールする必要があります。まだインストールしていない場合は、今すぐダウンロードしてインストールしてください。今日からExcelをよりスマートに使いましょう！

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))

    A screenshot of the INDEX MATCH formulas used to lookup for first non-blank value

注:

  • 上記は配列数式であり、Excel365、Excel2021、および新しいバージョンを除いてCtrl + Shift + Enterで入力する必要があります。
  • このチュートリアルを参照して、列または行の最初の非空白値を取得する方法の詳細な説明を確認してください。
INDEXとMATCHを使用して最初の数値を検索する

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

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

A screenshot of the INDEX MATCH formulas used to lookup for the first numeric value

注:

  • これは配列数式であり、Excel365、Excel2021、および新しいバージョンを除いてCtrl + Shift + Enterで入力する必要があります。
  • このチュートリアルを参照して、列または行の最初の数値を取得する方法の詳細な説明を確認してください。
INDEXとMATCHを使用してMAXまたはMINの関連付けを検索する

範囲内の最大値または最小値に関連する値を取得する必要がある場合は、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))

A screenshot of the INDEX MATCH formula used to lookup for MAX associations

この数式の動作：
  • MAX(C2:C11)は、範囲C2:C11内の最高値を検索し、それは96です。
  • MATCH関数は、配列C2:C11内の最高値の位置を見つけ、それは1であるべきです。
  • 最後に、INDEXはリストA2:A11の1番目の値を取得します。

注:

  • 複数の最大値または最小値がある場合、上記の例で示されているように、2人の学生が同じ最高スコアを達成した場合、この数式は最初の一致を返します。
  • 誰が最低のスコアを持っているかを決定するには、次の数式を使用します。 
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))
ヒント: #N/Aエラーメッセージをカスタマイズする

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

A screenshot of the #N/A error result returned by an INDEX MATCH formula

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

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

A screenshot of the #N/A error replaced with a tailored message using INDEX and MATCH

注:

  • エラーメッセージをカスタマイズするには、「Not found」を任意のテキストに置き換えてください。
  • すべてのエラーを処理したい場合は、 IFERROR 関数を使用することを検討してください。 IFNA: 
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    数式の潜在的な問題を警告するためにエラーをすべて抑制することはお勧めできないかもしれません。

以上がExcelのINDEXとMATCH関数に関連するすべての内容です。このチュートリアルが役立つことを願っています。さらにExcelのヒントやトリックを探している場合は、ここをクリックして数千のチュートリアルの広範なコレクションにアクセスしてください。