ワークシート内を検索する
手動でワークシート内を検索するときは、検索機能などを使います。VBAでしたら Find メソッドなどですね。では、ワークシート関数でワークシート内を検索するときは、どうしたらいいでしょう。そんなときは、MATCH関数を使います。
引数「照合の種類」には「1」「0」「-1」のいずれかを指定します。
- 1 ：検索値以下で、最も大きい数値が見つかったものとします
- 0 ：完全一致で検索します
- -1：検索値以上で、最も小さい数値が見つかったものとします
「1」や「-1」を指定するときは、検索範囲が昇順や降順で並べ替えられていなければなりません。まぁ、通常は「0」の完全一致でしょう。ちなみに、引数「照合の種類」を省略すると「1」が指定されたものとみなします。
上記の例でMATCH関数が返す「5」は、見つかった"田中"がセルA5(ワークシートの5行目)だから「5」なのではなく、引数「検査範囲」で指定したセル範囲A1:A8内で上から5番目のセルという意味です。
「検査値」が見つからないと、MATCH関数はエラーを返します。
引数「照合の種類」に「0」を指定したときは、ワイルドカード(＊や？)を使えます。
MATCH関数の結果を使って参照する
さて、MATCH関数で「何番目にあるか」を知るだけでは、それほど意味がありません。ワークシート関数は組み合わせて使うものです。特にこのMATCH関数を単体で使うことなど、一般的にはほとんどありません。ここでは、MATCH関数を使って"田中"の数値に該当するB列の「50」を参照してみましょう。「そんなの、(みんな大好き)VLOOKUP関数で、できるじゃん」という話ではありません。ここでは分かりやすく、VLOOKUP関数でも参照できる例で紹介していますが、VLOOKUP関数で対応できない場合にも、MATCH関数は大活躍します。ぜひ、選択肢を広く持ってください。
【方法1】INDEX関数と組み合わせる
有名な「MATCH・INDEX方式」です。INDEX関数に関する詳しい解説は割愛しますが、たとえば次のようなINDEX関数で、セルB5を参照できます。
そして、INDEX関数の引数に指定した「5」は、MATCH関数で調べることができます。したがって、次のようになります。
【方法2】INDIRECT関数と組み合わせる
INDIRECT関数は、文字列として指定したアドレスを参照する関数です。
上で指定した"B5"というのは、文字列の"B"と数値の"5"を結合したものです。つまり「"B" & 5」と同じですね。この「5」はMATCH関数で分かるのですから、文字列の"B"とMATCH関数の結果を＆で結合してやります。
【方法3】OFFSET関数と組み合わせる
最後は、ちょっと難しいOFFSET関数と組み合わせてみます。OFFSET関数については、下記のページをご覧ください。
OFFSET関数を使うと、セルB5は次のように参照できます。
OFFSET(B1,4,0) の「4」は、セルB1から4つ下のセルという意味です。MATCH関数を使うと、"田中"はセル範囲A1:A8内で「上から5個目」というのが分かります。INDEX関数に引き渡してやるのは、この「5」から1を引いた数値です。
MATCH関数は、単体で使う関数ではありません。MATCH関数の結果を別の関数に引き渡します。そういう発想を持ってください。