Excel では、特定のデータを正確に取得することが頻繁に必要になります。 INDEX 関数と MATCH 関数にはそれぞれ独自の長所がありますが、これらを組み合わせることで、データ検索のための強力なツールセットが得られます。 これらを組み合わせることで、基本的な水平方向および垂直方向の検索から、双方向、大文字と小文字を区別する検索、および複数基準の検索などのより高度な機能に至るまで、さまざまな検索機能が容易になります。 VLOOKUP と比較して強化された機能を提供する INDEX と MATCH の組み合わせにより、より広範囲のデータ検索オプションが可能になります。 このチュートリアルでは、彼らが一緒に達成できる可能性を深く掘り下げてみましょう。

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

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

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

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

ExcelのINDEX関数の使い方

この INDEX Excelの関数は、特定の範囲内の特定の場所の値を返します。 INDEX関数の構文は次のとおりです。

• 配列 （必須）は、値を返す範囲を指します。
• 行番号 (ただし、 列番号 が存在します) は配列の行番号を指します。
• 列番号 (オプションですが、次の場合は必須です) 行番号 は省略します）配列の列番号を指します。

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

=INDEX(C2:C11,6)

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

ここで少しテストしてみましょう。 式について =INDEX(A1:C1,2)、どのような値が返されるでしょうか? --- はい、戻ります 誕生日 2指定された行の nd 値。

ここで、INDEX関数が水平範囲または垂直範囲で完全に機能することを知っておく必要があります。 しかし、複数の行と列を持つより広い範囲の値を返す必要がある場合はどうなりますか？ この場合、行番号と列番号の両方を適用する必要があります。 たとえば、調べるために ジェフのスコア 単一の列ではなくテーブル範囲内で、彼のスコアを見つけることができます。 行番号6 フォルダーとその下に 列番号 3 セクションに A2 から C11 までのセル このような：

=INDEX(A2:C11,6,3)

ただし、複数の行と列を持つ非常に大きなデータベースの場合、正確な行番号と列番号を使用して数式を適用することは確かに不便です。 そしてこれは、MATCH関数の使用を組み合わせる必要がある場合です。

ExcelのMATCH関数の使い方

ExcelのMATCH関数は、指定された範囲内の特定のアイテムの場所である数値を返します。 MATCH関数の構文は次のとおりです。

• 参照値 (必須) は、一致する値を指します。 ルックアップ配列.
• ルックアップ配列 （必須）は、MATCHで検索するセルの範囲を指します。
• マッチタイプ （任意）： 1, 0 or -1.
  • 1 （デフォルト）、MATCHは以下の最大値を検索します 参照値。 の値 ルックアップ配列 昇順で配置する必要があります。
  • 0、MATCHは、 参照値。 の値 ルックアップ配列 任意の順序にすることができます。 （一致タイプが0に設定されている場合は、ワイルドカード文字を使用できます。）
  • -1、MATCHは、以上の最小値を見つけます。 参照値。 の値 ルックアップ配列 降順で配置する必要があります。

たとえば、知るために 名前のリストでのVeraの位置、次のようなMATCH式を使用できます。

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

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

ExcelでのINDEX関数とMATCH関数の基本的な使用法について理解したところで、袖をまくり上げてXNUMXつの関数を組み合わせる準備をしましょう。

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

以下の例を参照して、INDEX関数とMATCH関数を組み合わせる方法を理解してください。

見つけるには エヴリンのスコア、試験のスコアが 3XNUMX列目、できます MATCH 関数を使用して行の位置を自動的に決定します 手動でカウントする必要はありません。 その後、INDEX 関数を使用して、 特定された行と 3 列目の交点の値:

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

この式は少し複雑に見えるかもしれないので、各部分を見てみましょう。

この INDEX 式にはXNUMXつの引数が含まれています。

• 行番号: MATCH("エブリン",A2:A11,0) INDEX に値の行位置を提供します。エブリン" 範囲内 A2：A11ボタンの隣にある 5.
• 列番号: 3 を指定します 3配列内のスコアを見つけるための INDEX の XNUMX 番目の列。
• 配列: A2：C11 からの範囲内で、指定された行と列の交点に一致する値を返すように INDEX に指示します。 A2～C11。 最後に、結果が得られます 90.

上の式では、ハードコードされた値を使用しました。 「エヴリン」。 ただし、実際には、別の生徒の得点など、別のデータを検索するたびに値を変更する必要があるため、ハードコードされた値は実用的ではありません。 このようなシナリオでは、セル参照を利用して動的な数式を作成できます。 たとえば、この場合、私は次のようにします 「Evelyn」を F2 に変更します:

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

INDEX 式と MATCH 式の例

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

双方向ルックアップを適用するためのINDEXとMATCH

前の例では、列番号がわかっているので、MATCH 式を使用して行番号を見つけました。 しかし、列番号も不明な場合はどうすればよいでしょうか?

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

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

左ルックアップを適用するためのINDEXとMATCH

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

実際、左側の検索を実行できる機能は、INDEX と MATCH の組み合わせが VLOOKUP より優れている点の XNUMX つです。

見つけるには Evelynのクラス、次の式を使用して、 B2:B11でエヴリンを探す • A2:A11 から対応する値を取得します.

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

大文字と小文字を区別するルックアップを適用するためのINDEXとMATCH

MATCH 関数は本質的に大文字と小文字を区別しません。 ただし、数式で大文字と小文字を区別する必要がある場合は、 EXACT 関数。 INDEX 式で MATCH 関数と EXACT を組み合わせると、以下に示すように、大文字と小文字を区別した検索を効果的に実行できます。

• 配列 値を返したい範囲を指します。
• 参照値 は、文字の大文字と小文字を考慮して照合する値を指します。 ルックアップ配列.
• ルックアップ配列 MATCH を比較するセル範囲を指します。 参照値.

たとえば、知るために ジミーの試験のスコア、次の式を使用します。

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

√注：これは、次のように入力する必要がある配列数式です。 Ctrlキー + シフト + 入力します、Excel 365 と Excel 2021 を除きます。

INDEX と MATCH で最も近い一致を検索します

Excel では、データセット内の特定の値に最も近い値または最も近い値を検索する必要がある状況が発生することがあります。 このようなシナリオでは、ABS 関数と MIN 関数に加えて、INDEX 関数と MATCH 関数を組み合わせて使用​​すると、非常に役立ちます。

• 配列 値を返したい範囲を指します。
• ルックアップ配列 は、最も近い一致を検索する値の範囲を指します。 参照値.
• 参照値 値を参照して、最も近い一致を見つけます。

たとえば、調べるために スコアが85に最も近いのは誰ですか、次の式を使用して、 C85:C2 で 11 に最も近いスコアを検索します • A2:A11 から対応する値を取得します.

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

√注：これは、次のように入力する必要がある配列数式です。 Ctrlキー + シフト + 入力します、Excel 365 と Excel 2021 を除きます。

複数の基準でルックアップを適用するためのINDEXとMATCH

複数の条件を満たす値を検索するには、XNUMX つ以上の列にわたって検索する必要があり、次の式を使用します。 この数式を使用すると、さまざまな列にわたってさまざまな条件を指定して複数基準の検索を実行でき、指定されたすべての基準を満たす目的の値を見つけるのに役立ちます。

√注：これは、次のように入力する必要がある配列数式です。 Ctrlキー + シフト + 入力します。 中括弧のペアが数式バーに表示されます。

• 配列 値を返したい範囲を指します。
• (ルックアップ値=ルックアップ配列) 単一の条件を表します。 この条件は、特定の 参照値 の値と一致します ルックアップ配列.

たとえば、 7年2月2008日生まれのクラスAのココのスコア、次の式を使用できます。

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

複数の列にルックアップを適用するためのINDEXとMATCH

複数のデータ列を扱うシナリオを想像してください。 最初の列は、他の列のデータを分類するためのキーとして機能します。 特定のエントリのカテゴリまたは分類を決定するには、データ列全体で検索を実行し、それを参照列の関連キーに関連付ける必要があります。

たとえば、以下の表で、INDEX と MATCH を使用して学生 Shawn を対応するクラスと照合するにはどうすればよいでしょうか? 数式を使用してそれを達成することはできますが、その数式は非常に広範囲にわたるため、覚えて入力することはおろか、理解するのも困難になる可能性があります。

=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's 複数の列のインデックスと一致 機能が便利です。 これによりプロセスが簡素化され、特定のエントリを対応するカテゴリと迅速かつ簡単に照合できるようになります。 この強力なツールのロックを解除し、ショーンをクラスと簡単に一致させるには、次の手順を実行します。 Kutools for Excelアドインをダウンロードしてインストールします、次に次のようにします。

1. 一致するクラスを表示する宛先セルを選択します。
2. ソフトウェア設定ページで、下図のように クツール タブをクリックします。 フォーミュラヘルパー > ルックアップとリファレンス > 複数の列のインデックスと一致.
3. 
3. ポップアップ ダイアログ ボックスで、次の手順を実行します。
   1. 1番目をクリックしてください ボタンの隣に ルックアップ列 をクリックして、返したいキー情報、つまりクラス名を含む列を選択します。 (ここでは XNUMX つの列のみ選択できます。)
   2. 2番目をクリックしてください ボタンの隣に テーブルrng 選択したセルの値と一致するセルを選択します ルックアップ列、つまり生徒の名前です。
   3. 3番目をクリック ボタンの隣に 参照値 をクリックして、クラスと一致させる生徒の名前 (この場合は Shawn) を含むセルを選択します。
   4. クリック OK.
   5. 

結果

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

最初の非空白値を検索する INDEX および MATCH

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

• エラーを無視して、列または行の最初の空白以外の値を取得します。

• =INDEX(B4:B15,MATCH(TRUE,INDEX((B4:B150),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))

MAX または MIN の関連付けを検索するための 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))
• 上記の式には XNUMX つの引数があります。
• • 配列 関連情報を返したい範囲を指します。
  • ルックアップ配列 特定の基準について検査または検索される値のセット、つまり最大値または最小値を表します。

たとえば、次のことを判断したい場合は、 誰が最も高いスコアを持っていますか、次の式を使用します。

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

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

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

スプレッドシートをより使いやすくするために、INDEX MATCH 数式を IFNA 関数で囲むことで、このエラー メッセージをカスタマイズできます。

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

上記は、Excel の INDEX 関数と MATCH 関数に関連するすべての内容です。 このチュートリアルがお役に立てば幸いです。 Excel のヒントやコツをさらに詳しく知りたい場合は、 ここをクリックしてください 数千を超えるチュートリアルの広範なコレクションにアクセスします。