営業成績の順位表に氏名や支店名・店舗名などを取り出して表示するときに役立つのが、MATCH関数とINDEX関数です。
一般的に、検索系の関数にはVLOOKUP関数を使いますが、MATCH関数とINDEX関数を組み合わせて使うことで、より柔軟な処理が実現できます。
まず、MATCH関数とINDEX関数それぞれの書式や引数の意味を説明します。
MATCH関数は検索範囲の中で検索値の位置を求める
対応バージョン：Excel365 2019 2016 2013 2010
MATCH関数の書式です。
=MATCH(検索値,検索範囲,照合の種類)
※括弧内に指定する「検索値,検索範囲,照合の種類」を”引数”といいます。
MATCH関数は、引数〔検索値〕が引数〔検索範囲〕の中の上から数えて何番目にあるかを返します。
上の例では、C9セルの値が検索範囲C3:C11の中の何番目にあるかを調べています。
F2セルには、「=MATCH(C9,C3:C11,0)」の関数式が入力されています。
C9セルに入力されている検索値（72,080）が検索範囲（C3:C11）の何番目かを求めています。結果は、〔検索範囲〕の7番目にあることから「7」が返されます。
引数の説明
- 引数〔検索値〕：上から数えて何番目の位置にあるかを調べる値を指定。
- 引数〔検索範囲〕：どの位置にあるか調べたいセル範囲を指定。
- 引数〔照合の種類〕：0、1、-1のどれかを指定。
〔検索値〕は値が入力されているセル番号を指定します。
〔検索範囲〕には、〔検索値〕に指定した値が含まれている必要があります。
〔照合の種類〕は、以下のように指定します。
| 照合の種類 | 意味 |
|---|---|
| 0 | 〔検索値〕と一致するものを検索 |
| 1 | 〔検索値〕以下の値の最大値を検索。省略すると「1」が指定されたとみなします |
| -1 | 〔検索値〕以上の値の最小値を検索 |
〔照合の種類〕に「1」もしくは「-1」を指定する場合の注意点。
「1」を指定する場合は、〔検索の範囲〕のセル範囲は昇順である必要があります。また「-1」を指定する場合は、〔検索の範囲〕のセル範囲は降順に並んでいなければいけません。そのため、あらかじめ並び替えをしておく必要があります。
「0」完全一致を指定する場合は、並び替えの必要はありません。
INDEX関数は配列のなかの位置から値を求める
対応バージョン：Excel365 2019 2016 2013 2010
INDEX関数の書式です。
=INDEX(配列,行番号,列番号)
※括弧内に指定する「配列,行番号,列番号」を”引数”といいます。
INDEX関数は、引数〔配列〕のなかから引数〔行番号〕と〔列番号〕の交差するセルの値を抽出します。
引数の説明
- 引数〔配列〕：値を検索する配列やセル範囲を指定。
- 引数〔行番号〕：〔配列〕の先頭行から数えた行番号を指定。
- 引数〔列番号〕：〔配列〕の先頭列から数えた列番号を指定。
列番号は省略可能。省略した場合は、1列目を指定したことになります。
上記のサンプルでは、〔配列〕A3からA11の範囲から、F2セルに入力されている値の位置のセルの内容をG2セルに求めます。
サンプルの場合は、F2セルに「6」が入力されています。また、引数〔列番号〕には「1」が指定されていますので、これらから、先頭行から数えて6番目、先頭列から数えて1番目のセル内容がG2セルに求まります。
INDEX関数とMATCH関数を組み合わせて使う
INDEX関数とMATCH関数を組み合わせて使うことで、VLOOKUP関数で実現できなかった処理が可能になります。
以下のサンプルを見ながら解説します。
このサンプルでやりたいのは、営業成績のトップ5の順位表に、成績の良かった営業マンの氏名を表示させることです。
まず、営業成績トップ5の契約金額のトップ5は、以前に取り上げたLARGE関数で順位付けしています。
=LARGE($C$3:$C$11,E3)
セル範囲（$C$3:$C$11）の中から、順位（E3）と一致するセルの値を取り出しています。
営業成績ワースト5を求めたい場合は、契約金額の小さいものをSMALL関数で取り出しておきます。これ以降の手順や考え方はとトップ5の氏名を取り出す場合と同じです。
INDEX関数とMATCH関数でトップ5の氏名を取り出す
ここからが本番です。
上記の例では、以下の手順でトップ5の氏名を取り出します。
MATCH関数で〔検索値〕が〔検索範囲〕の何番目かを求める
書式は、=MATCH(G3,$C$3:$C$11,0)
〔検査値〕G3の値を〔検査範囲〕$C$3:$C$11の中から「完全一致（0）」方式で検索し、一致した値が何番目のセルにあるかを求めます。
INDEX関数で配列中の行と列で指定した位置の値を求める
書式は、=INDEX(配列,MATCH(検索値,検索範囲,照合の種類),列番号)の順で指定していきます。
=INDEX($A$3:$A$11,MATCH(G3,$C$3:$C$11,0),1)
〔配列〕$A$3:$A$11の中から、〔行番号〕MATCH(G3,$C$3:$C$11,0)で求めた位置と〔列番号〕1が交差したセルの値を求めます。
引数〔行番号〕の箇所にMATCH関数を指定することで、配列の中の何番目に検索値があるのかがわかります。
この関数式をトップ5の氏名を求めたいF3セルからF7セルに入力すれば、成績の良かった営業の氏名が表示されます。
なお、今回のようなケースでは、よく使われるVLOOKUP関数は使えません。
理由は、VLOOKUP関数は検索範囲の左端から検索値を探し、それよりも右側の列から値を取り出すことしかできないからです。今回のケースのように、左端から3列目のC列から契約金額を求め、それよりも左側にあるA列の氏名を取り出すのでVLOOKUP関数は使えないのです。
したがって、検索値よりも右側に求めたい値を含む列がある場合はVLOOKUP関数、検索値よりも左側に求めたい値を含む列がある場合は、今回のサンプルのようにINDEX関数とMATCH関数を組み合わせて使ってください。
MATCH関数・INDEX関数と同じ分類の関数
MATCH関数・INDEX関数と同じ「検索/行列」系の関数は以下のページで探せます。
