Office Hack

ExcelのINDEX関数とMATCH関数を組み合わせた使い方

  • Release
  • Update

INDEX関数とMATCH関数を組み合わせた使い方をご紹介します。INDEX関数は指定した行と列が交差する位置にあるセルの内容を返す関数ですが、MATCH関数を組み合わせることでより柔軟な検索が可能になります。

さらにSUMIF関数とINDEX関数、MATCH関数を組み合わせる方法もご紹介しています。関数の組み合わせ方を身につけましょう。

INDEX関数とは?

INDEX関数

INDEX関数は、指定された行と列が交差する位置にあるセルまたはセルの参照を返します。読み方は「インデックス」です。

書式は以下の2種類があります。

  • 「=INDEX(配列, 行番号, [列番号])」
  • 「=INDEX(参照, 行番号, [列番号], [領域番号])」

以下の記事ではINDEX関数の使い方を網羅していますのでご参考ください。

このページを見ている人におすすめの商品


MATCH関数とは?

MATCH関数の結果

MATCH関数は、指定された照合の種類に従って検査範囲内を検索し、検索値と一致する要素の、配列内での相対的な位置を表す数値を返します。読み方は「マッチ」です。

書式は「=MATCH(検索値, 検索範囲, [照合の種類])」のように記述します。

以下の記事ではMATCH関数の使い方を網羅していますのでご参考ください。

INDEX関数とMATCH関数の組み合わせ方

INDEX関数とMATCH関数を組み合わせて、入力された検索値を参照するテーブルから検索し、対応するデータを表示する手順を説明していきます。

作業時間:12分

  1. 元となる表を準備する

    元となる表を準備する

    まず最初に検索するための表(テーブル)形式のデータを用意します。例では「商品コード」「商品名」「単価」が書かれた価格テーブルを用意しました。受注シートの商品名から商品コードを検索して表示させてみましょう。

  2. INDEX関数の参照を指定する

    INDEX関数の参照を指定する

    数式を入力したい【セル(例ではC10セル)】を選択し、『=INDEX(B4:D6』と入力します。セル範囲はドラッグして選択することもできます。

  3. 参照先を絶対参照にする

    絶対参照にする

    このままだと数式をコピーした時に参照先がズレてしまうので絶対参照にします。【F4】キーを押します。「B4:D6」が「$B$4:$D$6」となり絶対参照になりました。

  4. INDEX関数の行番号を指定する

    INDEX関数の行番号を指定する

    参照先から「バナナ」の行がどこにあるかを指定します。「B4:D6」のセル範囲では2行目にバナナがあります。「=INDEX($B$4:$D$6」に続いて『,2,』と入力します。

  5. INDEX関数の列番号を指定する

    INDEX関数の列番号を指定する

    検索したい商品コードがどの列にあるかを指定します。「B4:D6」のセル範囲では1列目に商品コードがあります。「=INDEX($B$4:$D$6,2,」に続いて『1)』と入力し、【Enter】を押します。

  6. MATCH関数の検索値を指定する

    MATCH関数の検索値を指定する

    ここまででINDEX関数を用いて「バナナ」のみ固定で商品コードを検索できるようになりました。任意の商品名で検索できるようにするためにINDEX関数の行番号をMATCH関数に置き換えます。まずMATCH関数の検索値を指定します。「=INDEX($B$4:$D$6,」に続いて『MATCH(D10,』と入力します。

  7. MATCH関数の検索範囲を指定する

    MATCH関数の検索範囲を指定する

    MATCH関数の検索範囲として価格テーブルの商品名の列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,」に続いて『$C$4:$C$6,』と入力します。

  8. MATCH関数の照合の種類を指定する

    MATCH関数の照合の種類を指定する

    MATCH関数の照合の種類として完全一致の「0」を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,」に続いて『0),』と入力します。

  9. INDEX関数の列番号を指定する

    INDEX関数の列番号を指定する

    最後にINDEX関数の列番号として価格テーブルの商品コードの列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,0),」に続いて『1)』と入力し、【Enter】を押します。

  10. INDEX関数とMATCH関数を組み合わせた結果

    INDEX関数とMATCH関数を組み合わせた結果

    数式を入力したC10セルにD10で入力した商品名を価格テーブルから検索して対応する商品コード「1002」を表示することができました。

商品名を変更した結果

本当に任意の商品名の検索に対応しているか確認するため、D10セルの商品名を『みかん』とすると商品コードに対応する「1003」と表示されました。

INDEX関数とMATCH関数を組み合わせることで自由度が高い検索をすることができます。

VLOOKUP関数でも同じように検索することはできますが、VLOOKUP関数は一番左の列を検索範囲とし、2列目以降の値を取得することができます。今回のように検索範囲の左側を検索することはできません。

VLOOKUP関数で左側(右から左に)の値を取得するのが難しいことを以下の記事で説明していますのでご参考ください。

INDEX関数とMATCH関数の処理の流れ

INDEX関数とMATCH関数の処理の流れを確認すると以下のようになります。

  1. MATCH関数でD10セルに入力された商品名(バナナ)を価格テーブルから検索して位置を取得
  2. MATCH関数で取得した位置を行番号としてINDEX関数で1列目の商品コードを取得

SUMIF、INDEX、MATCH関数を複合利用する

SUMIF関数の合計範囲の自由度を高めるにはINDEX関数とMATCH関数を組み合わせます。固定の合計範囲ではなく任意の条件に当てはまったものだけ合計したい場合に活用できます。

元となる表データ

2つのシートがあります。Sheet1は参照する元の表データが入っています。「商品コード」「商品名」「単価」の他に10月~12月までの各月ごとの売上個数が入力されています。

合計を出力するシート

Sheet2には「商品コード」「対象月」を入力すると合計の売上個数を表示させるようにしていきます。

SUMIF関数の入力

まずはSUMIF関数を書いてみましょう。Sheet2のD3セルを選択し、『=SUMIF(Sheet1!B3:B6,』と入力します。

SUMIF関数は指定された検索条件に一致するセルの値を合計する関数です。「=SUMIF(範囲,検索条件,[合計範囲])」のように記述します。

SUMIF関数の基本的な使い方を以下の記事で説明しています。ご参考ください。

条件の指定

SUMIF関数の検索条件としてSheet2の商品コードを指定します。「=SUMIF(Sheet1!B3:B6,」に続いて『B3,』と入力します。

INDEX関数の入力

SUMIF関数の合計範囲としてINDEX関数を指定します。「=SUMIF(Sheet1!B3:B6,B3,」に続いて『INDEX(Sheet1!B3:G6,』と入力します。

列番号、行番号の指定

INDEX関数の行番号には「0」を、列番号にはMATCH関数を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,」に続いて『0,MATCH(C3,』と入力します。

MATCH関数の入力

MATCH関数の検査範囲を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,0,MATCH(C3,」に続いて『Sheet1!B2:G2,』と入力します。

照合の種類の指定

MATCH関数の照合の種類を指定します。「=SUMIF(Sheet1!B3:B6,B3,INDEX(Sheet1!B3:G6,0,MATCH(C3,Sheet1!B2:G2,」に続いて『0)))』と入力し、Enterを入力します。

複合利用の結果

商品コード1002の10月の売上個数の合計がSheet2のD3セルに表示されました。

数式が長くなりましたが、SUMIF関数の引数にINDEX関数、MATCH関数を入れ子にすることでより柔軟な条件で合計することが可能です。

他の関数も合わせてチェック!

Office Hackでは、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。

よろしければ参考にならなかった点をお聞かせください

Page Top