MATCH関数_応用編
MATCH関数は指定した範囲で検索したい値が、何行目・もしくは何列目に入っているか、それぞれ数値で返してくれる関数です。
MATCH関数を単体で使用する機会は少ないかもしれませんが、他の関数と一緒に組み合わせて使用することで、とても便利な働きをしてくれます。
ここではMATCH関数を他の関数と一緒に組み合わせることでできることの中から、特に使用頻度の高いと思われるものを2つほどご紹介したいと思います。
MATCH関数がよく分からないという方は
先にMATCH関数の基本の動きをご確認くださいね
練習用エクセルファイルを用意したので、必要な方は下記よりダウンロードしてくださいね。
INDEX関数と一緒に組む方法
INDEX関数とMATCH関数を一緒に組む方法をご紹介します。
おそらく、使用頻度が一番高いのがINDEX関数と一緒に組むケースだと思います。
INDEX関数と一緒に組むことで、VLOOKUP関数では対応できなかった「検索値の左側を返す」ことが可能です。
今はXLOOKUP関数があるので、使用頻度は少し減ったと思いますが、XLOOKUP関数対応のバージョンでなかったり、過去に作られたファイルの修正などで、まだまだ目にする機会も多いので、ぜひ覚えておきましょう。
練習用ファイルの「INDEX&MATCH」シートを使います
C列の商品名をKeyにしてE1～F7の一覧から商品番号を返したいので、まず最初にINDEX関数を入れます。
INDEX関数を入れたら Tab キーで使用関数を確定させ、第1引数を指定します。
INDEX関数の第1引数は配列となり、検索値を探す範囲を指定するので、ここではE列全体を選択します。
もちろんE2～E7と指定しても良いですが
下に他のデータがなければ列指定がおすすめです！
第1引数を指定したら、カンマ , を打って第2引数を指定します。
INDEX関数の第2引数は行番号で、第1引数で指定した配列の何行目を検索するか指定します。
ここでは検索値が変動するため、何行目か具体的に指定ができません。
そのため、ここでMATCH関数を組み込みます。MATCH関数は「指定した範囲で検索したい値が、何行目・もしくは何列目に入っているかを返してくれる」関数なので、行番号はMATCH関数で指定しましょう。
MATCH関数を入力して Tab キーで確定させたら、MATCH関数の第1引数を指定します。
MATCH関数の第1引数は検査値なので、ここでは商品名が入っているC2セルになります。
第1引数を指定したら、カンマ , を打って第2引数に進みます。
第2引数は検査範囲となり、ここではF列に検索したい商品名が入っているので、F列全体を選択しましょう。
第2引数を指定したら、カンマ , を打って第3引数に進みます。
第3引数は照合の種類となり、ここでは完全一致にしたいので「０」と入力しましょう。
第3引数を指定できたら、まずカッコ閉じを1つ打ちます。このカッコ閉じはMATCH関数のカッコ閉じとなります。
そして、ここまでがINDEX関数の第2引数となりますので、もう1つINDEX関数のカッコ閉じを入力し、最後に Enter を押せば完成です。
1つ目はMATCH関数のカッコ閉じ、2つ目はINDEX関数のカッコ閉じですね
あとは、最終行までオートフィルで関数を反映させてあげればOKです
=INDEX(INDEX関数の第1引数,MATCH(MATCH関数の第1引数,MATCH関数の第2引数,MATCH関数の第3引数))
=INDEX(E:E,MATCH(C2,F:F,0))
VLOOKUP関数と一緒に組む方法
VLOOKUP関数とMATCH関数を一緒に組む方法をご紹介します。
VLOOKUP関数では第3引数で検索範囲の何列目を返すか指定しますが、複数の列にオートフィルで反映させたい場合や、参照先の列数に変動があった場合、数値で入力していると第3引数を手動で修正する必要があります。
そこで、MATCH関数を一緒に組むことで、複数列への数式コピーや参照先の列数に変動があった場合でも、常にMATCH関数が現在の列番号を返してくれるので、VLOOKUP関数の第3引数である列番号を修正する必要がなくなります。
列数や位置に変動がある場合は、ぜひMATCH関数と一緒に組んでおきましょう。
練習用ファイルの「VLOOK&MATCH」シートを使います
F列の商品名をKeyにA～C列を参照して商品番号と単価を返したいので、G2セルにVLOOKUP関数を入れましょう。
VLOOKUP関数を入れたら、第1引数を指定します。
VLOOKUP関数の第1引数は検査値となりますので、ここではF2セルを指定します。
後でH列にコピーするので、忘れずに列だけ固定の相対参照にしておきましょう
第1引数を指定したら、カンマ , を打って第2引数を指定します。
VLOOUP関数の第2引数は範囲なので、A～C列を指定しましょう。
ここも忘れずに絶対参照にしておきましょう
第2引数を指定したら、カンマ , を打って、いよいよ第3引数に進みます。
通常であれば、ここで列番号として「2」を入力しますが、この列番号をMATCH関数に返してもらうので、第3引数にMATCH関数を入れて Tab キーで確定させます。
Tab キーで確定させたら、MATCH関数の第1引数を指定します。
MATCH関数の第1引数は検査値なので、返したい列の見出しの項目名を選択します。
ここではB列の「商品番号」を返してほしいので、B1セルを選択し行だけ固定の相対参照にします。
今回は隣接したH列にコピーすることと
参照先も参照元も同じ配列なので行だけ固定としましたが
その時のフォーマットによって、絶対参照・相対参照を使い分けてくださいね
第1引数を指定したら、カンマ , を打って、第2引数に進みます。
第2引数は検査範囲なので、第1引数を検索する範囲としてA1～C1セルを選択し絶対参照にします。
第2引数を指定したら、カンマ , を打って、第3引数に進みます。
第3引数は照合の種類となり、ここでは完全一致にしたいので「０」と入力して、MATCH関数のカッコを閉じます。
ここまでがVLOOKUP関数の第3引数となり、これで列番号が指定されました。
VLOOKUP関数の第3引数まで指定が終わったので、カッコを閉じたらカンマ , を打ち、第4引数を指定します。
第4引数は検索方法なので、ここは完全一致の「０」を入力しましょう。
最後にVLOOKUP関数のカッコ閉じて Enter を押せば完成です。
G2セルの関数をH2セルにコピーしてみましょう。
VLOOKUP関数の第3引数を数値で入力した場合、コピーした後に列番号を手入力で修正しなくてはいけませんが、MATCH関数で列番号を指定したので、コピーしても何も修正せず、そのまま正しい値が返ってきました。
あとは、G2～H2セルを選択し、まとめて最終行までオートフィルで反映させればOKです
=VLOOKUP(VLOOKUP関数の第1引数,VLOOKUPの第2引数,MATCH(MATCH関数の第1引数,MATCH関数の第2引数,MATCH関数の第3引数),VLOOKUP関数の第4引数)
=VLOOKUP($F2,$A:$C,MATCH(B$1,$A$1:$C$1,0),0)
まとめ
MATCH関数の応用編として、使用頻度の高いと思われる組み合わせを2つご紹介しました。
この組み合わせ以外にも、同じ結果が出せる関数の組み合わせもあり、必ずこれでなくてはいけないというわけではないので、参考にしていただけたらと思います。