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

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

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


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

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


ExcelのINDEX関数の使い方

最大XNUMXWの出力を提供する INDEX Excelの関数は、特定の範囲内の特定の場所の値を返します。 INDEX関数の構文は次のとおりです。

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

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

=INDEX(C2:C11,6)

エクセルインデックスマッチ01

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

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

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

=INDEX(A2:C11,6,3)

エクセルインデックスマッチ02

Excel の INDEX 関数について知っておくべきこと:
  • INDEX関数は、垂直範囲と水平範囲で機能します。
  • 両方の場合 行番号 > 列番号 引数が使用され、 行番号 より先に進みます 列番号、 INDEX は指定された値の交点にある値を取得します。 行番号 > 列番号.

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


ExcelのMATCH関数の使い方

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

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

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

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

エクセルインデックスマッチ3

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

Excel の MATCH 関数について知っておくべきこと:
  • MATCH関数は、値自体ではなく、ルックアップ配列内のルックアップ値の位置を返します。
  • MATCH関数は、重複する場合に最初の一致を返します。
  • INDEX関数と同様に、MATCH関数は垂直範囲と水平範囲でも機能します。
  • MATCH では大文字と小文字が区別されません。
  • Status 参照値 MATCH 式の部分はテキスト形式なので、引用符で囲みます。
  • Status 参照値 には見つかりません ルックアップ配列 #N / A エラーが返されます。

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


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

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

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

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

エクセルインデックスマッチ4

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

エクセルインデックスマッチ5

最大XNUMXWの出力を提供する 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)

(AD) Kutools で検索を簡素化: 数式の入力は必要ありません。

Kutools for Excel's スーパールックアップ は、大阪で さまざまな検索ツール あらゆるニーズに合わせてカスタマイズされます。 複数基準のルックアップを実行する場合でも、複数のシートにまたがる検索を実行する場合でも、XNUMX 対多のルックアップを実行する場合でも、 スーパールックアップ 数回クリックするだけでプロセスが簡素化されます。 これらの機能を詳しく見る どうやって見るか スーパールックアップ Excel データの操作方法が変わります。 複雑な数式を覚える煩わしさから解放されます。

Kutools検索ツール

Kutools for Excel - 300 を超える便利な機能で生産性を向上させます。 30 日間の全機能を無料で試してみるチャンスをお見逃しなく! 今すぐ始めましょう!


INDEX 式と MATCH 式の例

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


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

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

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

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

エクセルインデックスマッチ6

この式の仕組み:
  • 最初の MATCH 式は、リスト A2:A11 内のエブリンの位置を検索します。 5 INDEX への行番号として。
  • XNUMX 番目の MATCH 式は、スコアと戻り値の列を決定します。 3 INDEX への列番号として。
  • 式は次のように単純化されます =INDEX(A2:C11,5,3)、INDEX が返されます 90.

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

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

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

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

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

エクセルインデックスマッチ7

注: を使用すると、特定の値の左検索を簡単に実行できます。 右から左へのルックアップ の特徴 Kutools for Excel 数回クリックするだけで。 機能を実装するには、次の場所に移動します。 クツール Excel のタブをクリックし、 スーパールックアップ > 右から左へのルックアップ セクションに グループ。

右から左へのルックアップ

Kutoolsをインストールしていない場合は、ここをクリックしてください ダウンロードして、30 日間の全機能を無料でお試しください!


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

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

=INDEX(array, MATCH(TRUE, EXACT(lookup_value, lookup_array), 0))
  • 配列 値を返したい範囲を指します。
  • 参照値 は、文字の大文字と小文字を考慮して照合する値を指します。 ルックアップ配列.
  • ルックアップ配列 MATCH を比較するセル範囲を指します。 参照値.

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

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

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

エクセルインデックスマッチ8

この式の仕組み:
  • EXACT 関数は比較します。 「ジミー」 リスト内の値を使用して A2:A11、文字の大文字と小文字を考慮します。大文字と小文字の両方を考慮して XNUMX つの文字列が正確に一致する場合、EXACT は返されます。 TRUE; それ以外の場合は、 間違った情報。 その結果、次のようになります。 TRUE 値と FALSE 値を含む配列.
  • 次に、MATCH 関数は、 最初の TRUE 値の位置 配列内にあるはずです。 10.
  • 最後に、INDEX は次の値を取得します。 10配列内の MATCH によって提供される 番目の位置。

注意:

  • を押して数式を正しく入力することを忘れないでください。 Ctrl + Shift + Enterを使用していない限り、 Excelの365 or Excelの2021その場合は、単に を押してください。 入力します.
  • 上の式は単一のリスト内を検索します。 C2:C11。 複数の列と行を含む範囲内を検索したい場合は、次のようにします。 A2:C11、列番号と行番号の両方を INDEX に提供する必要があります。
  • =INDEX(A2:C11,MATCH(TRUE,EXACT("JIMMY",A2:A11),0),3)
  • この修正された数式では、MATCH 関数を使用して、文字の大文字と小文字を考慮して「JIMMY」を範囲内で検索します。 A2:A11一致するものが見つかったら、対応する値を 3範囲のXNUMX番目の列 A2:C11.

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

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

=INDEX(array, MATCH(MIN(ABS(lookup_array - lookup_value)), ABS(lookup_array - lookup_value),0))
  • 配列 値を返したい範囲を指します。
  • ルックアップ配列 は、最も近い一致を検索する値の範囲を指します。 参照値.
  • 参照値 値を参照して、最も近い一致を見つけます。

たとえば、調べるために スコアが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 を除きます。

この式の仕組み:
  • 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 はリスト内の位置の値を取得します。 A2:A11 に最も近いスコアに対応する 85 範囲内 C2:C11.

注意:

  • を押して数式を正しく入力することを忘れないでください。 Ctrl + Shift + Enterを使用していない限り、 Excelの365 or Excelの2021その場合は、単に を押してください。 入力します.
  • 同点の場合、この式は最初の一致を返します。
  • 見つけるには 平均スコアに最も近いスコア、交換 85 との式で 平均(C2:C11).

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

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

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

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

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

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

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

エクセルインデックスマッチ9

注意:

  • この式では、値のハードコーディングを回避し、セル内の値を変更することで異なる情報を含むスコアを簡単に取得できるようにしています。 G2, G3, G4.
  • を押して数式を入力してください。 Ctrl + Shift + Enter を除いて Excelの365 or Excelの2021を押すだけです。 入力します.
    いつも使い忘れる場合 Ctrl + Shift + 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をインストールしていない場合は、ここをクリックしてください ダウンロードして、30 日間の全機能を無料でお試しください!


複数の列にルックアップを適用するための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. エクセルインデックスマッチ11
  4. ポップアップ ダイアログ ボックスで、次の手順を実行します。
    1. 1番目をクリックしてください エクセルインデックスマッチアイコン ボタンの隣に ルックアップ列 をクリックして、返したいキー情報、つまりクラス名を含む列を選択します。 (ここでは XNUMX つの列のみ選択できます。)
    2. 2番目をクリックしてください エクセルインデックスマッチアイコン ボタンの隣に テーブルrng 選択したセルの値と一致するセルを選択します ルックアップ列、つまり生徒の名前です。
    3. 3番目をクリック エクセルインデックスマッチアイコン ボタンの隣に 参照値 をクリックして、クラスと一致させる生徒の名前 (この場合は Shawn) を含むセルを選択します。
    4. OK.
    5. エクセルインデックスマッチ12

Result

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

注: 試してみる 複数の列のインデックスと一致 この機能を使用するには、コンピューターに Kutools for Excel がインストールされている必要があります。まだインストールしていない場合は、お待ちください --- 今すぐダウンロードしてインストールし、制限なしの 30 日間の無料トライアルをご利用ください。 今すぐ 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))

注意:

  • 上記は、次のように入力する必要がある配列数式です。 Ctrlキー + シフト + 入力します、Excel 365 と Excel 2021 を除きます。
  • 詳細な説明については、このチュートリアルを参照してください。 列または行の最初の空白以外の値を取得します.

最初の数値を検索する INDEX および MATCH

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

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

注意:

  • これは配列数式であり、次のように入力する必要があります。 Ctrlキー + シフト + 入力します、Excel 365 と Excel 2021 を除きます。
  • 詳細な説明については、このチュートリアルを参照してください。 列または行の最初の数値を取得します.

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

この式の仕組み:
  • MAX(C2:C11) 範囲内の最大値を検索します C2:C11ボタンの隣にある 96.
  • 次に、MATCH 関数は配列内の最大値の位置を見つけます。 C2:C111.
  • 最後に、INDEX は 1リスト内の st 値 A2:A11.

注意:

  • XNUMX 人の学生が同じ最高点を達成した上記の例に見られるように、複数の最大値または最小値がある場合、この式は最初に一致したものを返します。
  • 誰が最もスコアが低いかを判断するには、次の式を使用します。
    =INDEX(A2:A11,MATCH(MIN(C2:C11),C2:C11,0))

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

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

エクセルインデックスマッチ15

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

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

エクセルインデックスマッチ16

注意:

  • を置き換えることにより、エラー メッセージをカスタマイズできます。 "見つかりません" お好みのテキストで。
  • #N/A だけでなくすべてのエラーを処理したい場合は、 IFエラー 代わりに機能 イフナ:
    =IFERROR(INDEX(C2:C11,MATCH(F2,A2:A11,0)),"Not found")

    エラーは数式内の潜在的な問題に対するアラートとして機能するため、すべてのエラーを抑制することはお勧めできない場合があることに注意してください。

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

Comments (0)
No ratings yet. Be the first to rate!
There are no comments posted here yet
Please leave your comments in English
Posting as Guest
×
Rate this post:
0   Characters
Suggested Locations