- 公開日：
- 更新日：
ExcelのINDEX関数とMATCH関数を組み合わせた使い方
INDEX関数とMATCH関数を組み合わせた使い方をご紹介します。INDEX関数は指定した行と列が交差する位置にあるセルの内容を返す関数ですが、MATCH関数を組み合わせることでより柔軟な検索が可能になります。
さらにSUMIF関数とINDEX関数、MATCH関数を組み合わせる方法もご紹介しています。関数の組み合わせ方を身につけましょう。
INDEX関数とは？
INDEX関数は、指定された行と列が交差する位置にあるセルまたはセルの参照を返します。読み方は「インデックス」です。
書式は以下の2種類があります。
- 「=INDEX(配列, 行番号, [列番号])」
- 「=INDEX(参照, 行番号, [列番号], [領域番号])」
以下の記事ではINDEX関数の使い方を網羅していますのでご参考ください。
MATCH関数とは？
MATCH関数は、指定された照合の種類に従って検査範囲内を検索し、検索値と一致する要素の、配列内での相対的な位置を表す数値を返します。読み方は「マッチ」です。
書式は「=MATCH(検索値, 検索範囲, [照合の種類])」のように記述します。
以下の記事ではMATCH関数の使い方を網羅していますのでご参考ください。
INDEX関数とMATCH関数の組み合わせ方
INDEX関数とMATCH関数を組み合わせて、入力された検索値を参照するテーブルから検索し、対応するデータを表示する手順を説明していきます。
まず最初に検索するための表（テーブル）形式のデータを用意します。例では「商品コード」「商品名」「単価」が書かれた価格テーブルを用意しました。受注シートの商品名から商品コードを検索して表示させてみましょう。
数式を入力したい【セル（例ではC10セル）】を選択し、『=INDEX(B4:D6』と入力します。セル範囲はドラッグして選択することもできます。
このままだと数式をコピーした時に参照先がズレてしまうので絶対参照にします。【F4】キーを押します。「B4:D6」が「$B$4:$D$6」となり絶対参照になりました。
参照先から「バナナ」の行がどこにあるかを指定します。「B4:D6」のセル範囲では2行目にバナナがあります。「=INDEX($B$4:$D$6」に続いて『,2,』と入力します。
検索したい商品コードがどの列にあるかを指定します。「B4:D6」のセル範囲では1列目に商品コードがあります。「=INDEX($B$4:$D$6,2,」に続いて『1)』と入力し、【Enter】を押します。
ここまででINDEX関数を用いて「バナナ」のみ固定で商品コードを検索できるようになりました。任意の商品名で検索できるようにするためにINDEX関数の行番号をMATCH関数に置き換えます。まずMATCH関数の検索値を指定します。「=INDEX($B$4:$D$6,」に続いて『MATCH(D10,』と入力します。
MATCH関数の検索範囲として価格テーブルの商品名の列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,」に続いて『$C$4:$C$6,』と入力します。
MATCH関数の照合の種類として完全一致の「0」を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,」に続いて『0),』と入力します。
最後にINDEX関数の列番号として価格テーブルの商品コードの列を指定します。「=INDEX($B$4:$D$6,MATCH(D10,$C$4:$C$6,0),」に続いて『1)』と入力し、【Enter】を押します。
数式を入力したC10セルにD10で入力した商品名を価格テーブルから検索して対応する商品コード「1002」を表示することができました。
本当に任意の商品名の検索に対応しているか確認するため、D10セルの商品名を『みかん』とすると商品コードに対応する「1003」と表示されました。
INDEX関数とMATCH関数を組み合わせることで自由度が高い検索をすることができます。
VLOOKUP関数でも同じように検索することはできますが、VLOOKUP関数は一番左の列を検索範囲とし、2列目以降の値を取得することができます。今回のように検索範囲の左側を検索することはできません。
VLOOKUP関数で左側（右から左に）の値を取得するのが難しいことを以下の記事で説明していますのでご参考ください。
INDEX関数とMATCH関数の処理の流れを確認すると以下のようになります。
- MATCH関数でD10セルに入力された商品名（バナナ）を価格テーブルから検索して位置を取得
- MATCH関数で取得した位置を行番号としてINDEX関数で1列目の商品コードを取得
SUMIF、INDEX、MATCH関数を複合利用する
SUMIF関数の合計範囲の自由度を高めるにはINDEX関数とMATCH関数を組み合わせます。固定の合計範囲ではなく任意の条件に当てはまったものだけ合計したい場合に活用できます。
2つのシートがあります。Sheet1は参照する元の表データが入っています。「商品コード」「商品名」「単価」の他に10月～12月までの各月ごとの売上個数が入力されています。
Sheet2には「商品コード」「対象月」を入力すると合計の売上個数を表示させるようにしていきます。
まずはSUMIF関数を書いてみましょう。Sheet2のD3セルを選択し、『=SUMIF(Sheet1!B3:B6,』と入力します。
SUMIF関数は指定された検索条件に一致するセルの値を合計する関数です。「=SUMIF(範囲,検索条件,[合計範囲])」のように記述します。
SUMIF関数の基本的な使い方を以下の記事で説明しています。ご参考ください。
SUMIF関数の検索条件としてSheet2の商品コードを指定します。「=SUMIF(Sheet1!B3:B6,」に続いて『B3,』と入力します。
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関数の検査範囲を指定します。「=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では、ここでご紹介できなかった関数の一覧ページもご用意しております。ぜひ、参考にしてください。