VLOOKUP関数とINDEX&MATCH関数の熱き戦い

Excelを使ってていて、VLOOKUP関数に１度もお世話になったことない人などいるだろうか。

VLOOKUP関数こそ、最強のワークシート関数だと思っていた時期が俺にもありました。

しかし、万能だと思っていたVLOOKUP関数が使えない、そんなときがあるのです。

そしてVLOOKUP関数が使えず、悩んでいるときにINDEX関数とMATCH関数を組み合わせて使ったことがある人もまた多いのではないでしょうか。

どちらも優秀、そして最強。

しかし、最強同士が相対したとき、どちらが本当の漢（おとこ）なのか、決めなくっちゃあいけません。

VLOOKUP関数と、INDEX関数とMATCH関数の組み合わせはどちら強い（速い）のか。

一度疑問に思ったら最後、どうしても戦わせてみたくなりました。

ここに異種格闘技戦が開幕！！

なんでもありの、無制限、一本勝負、ッファイ！！！

選手紹介

東、VLOOKUP関数

VLOOKUP関数は表を縦方向に検索し、特定のデータに対応する値を取り出す関数。

２つの異なる表からデータを検索するときなど、

シート狭しと縦横無尽に大活躍する。

ワークシート関数界でも指折りの猛者。

構文

=VLOOKUP(検索値, 範囲, 列番号, 検索の型)

引数の「検索値」は範囲の中から検索する値。

「範囲」は、検索する範囲。

そして、「列番番号」は範囲内の何番目の列を戻り値として返すか指定する。

引数の「検索の型」は今回は完全一致とする。

問題点

ビジネスの現場で大暴れしており、これが使えるかどうかがExcel関数使いの登竜門的関数ではあるが、実は、大きな弱点がある。

それは、列番号に指定できる値は正の数だけなので、戻り値として返すことができるのが、検索して見つかった値の右側にある値に限られるという点だ。

列の左側を返すことができないのだ。

一例を見てもらいたい。これは問題ないケース。ちゃんと検索できている。

これは問題のケース。検索値が範囲の右側にあるため、左側にある値を返すことができない。

列番号に負数を指定できないッ！

これはVLOOKUP関数の唯一といってもいい弱点だ。

ではこんなときどうしたらいいのか。

そんなときこそ、彼らの出番である。

西、INDEX関数＆MATCH関数

MATCH関数

指定した範囲内で探したい値の行や列を返す関数。

これ単体だと、正直使い道がわからない。

INDEX関数と組み合わせて使うべくして生まれてきたとしか思えない。

構文

=MATCH(検査値,検査範囲,[照合の種類])

VLOOKUP関数と風情が似ている。

「検査値」に探したい値を指定する。

「検査範囲」に検査したい範囲を指定する。

そして「照合の種類」これも今回は完全一致とする。

INDEX関数

指定された行と列が交差する位置にある値を返す関数。

これ単体で使った経験がない。

どういった場面で使うのか。

やはり、MATCH関数と組み合わされるべくして生まれてきたとしか思えない。

構文

=INDEX(配列,行番号,[列番号])

まさか関数で「配列」という引数が出てくるとは・・・。

普段使いであれば、範囲と同じと考えて差し支えない。

「行番号」は配列＝範囲の行番号＝行数を指定する。

「列番号」は範囲が２列以上の場合に指定する。

INDEXとMATCH組み合わせる

一見、何も関係がないように見えるこの二つの関数だが、組み合わせることで科学反応が起きる。

INDEX関数の行番号にMATCH関数で取得した行番号を入れると、VLOOKUP関数以上に柔軟な検索ができるようになる。

VLOOKUP関数の完全上位互換。大好き。

例

両者出揃ったところで勝負開始ッ！！

どちらが強い（速い）のか。

簡単なことだ。

戦わせてみればいいッ！！

ということで、両者の速度を検証してみたいと思います。

ワークシート上では速度の検証ができないので、VBAを使って検証してみたいと思います。

VLOOKUPは命令が一つだけですが、内部で複雑な処理が行われているのでは？

それに対して、MATCHとINDEX関数は単純な関数の組み合わせのため、だが、内部的には単純な関数かも知れません。

２つの関数を呼び出すとは言え、実はこっちの方が速いのでは？

なんて予想をしておりますが、

果たしてその結果やいかに？

環境

Windows10 Pro(64bit)
Excel2019 32bit

コード

先ほどのシート構成を対象に調査してみます。

 

Sub vlookupTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.VLookup(Range("D2"), Range("A2:B10"), 2, 0)
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "vlookup:" & endDouble - startDouble
End Sub

 

Sub indexTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.Index(Range("B2:B10"), WorksheetFunction.Match(Range("D2"), Range("A2:A10"), 0))
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "index:" & endDouble - startDouble
End Sub

結果

vlookup:1.1796875
index&match:2.125
(単位：秒)

なんと、VLOOKUPの方が２倍近い性能を見せつけました。

まさに圧倒的ッ！！

やはり、MATCHとINDEXは関数を２つ呼び出しているので２倍なのでしょうか？

ここから言えることは、VLOOKUP関数を使える場面では、おとなしくVLOOKUPを使った方がパフォーマンスがいい！！

ということですね。

番外編

MATCH関数だけだとどうなんだ？

ということであまり意味はないですが、やってみました。

Sub matchTest()
   Dim startDoubleAs Double: startDouble = Timer
   Dim var As String
   Dim i As Long
   For i = 1 To 100000
      var = WorksheetFunction.Match(Range("D2"), Range("A2:A10"), 0)
   Next i
   Dim endDouble As Double: endDouble = Timer
   Debug.Print "match:" & endDouble - startDouble
End Sub

結果

match:1.80859375

なんと、2秒近く掛かっています。

INDEX関数との組み合わせでは、ほとんどがMATCH関数の処理で時間を取られていたということが言えそうです。

ここでもやはり、VLOOKUP関数の性能の良さが浮き彫りになりました。

く、くやしい・・・。

終わりに

VLOOKUP関数とMATCH、INDEX関数の速度を比較してみました。

大抵の場合はそこまで性能を気にすることはないかと思いますが、大量のセルを処理する場合には、パフォーマンスに多少の影響が出ることが考えらえれます。

もし、VLOOKUP関数を使えるのに、MATCHとINDEX関数を使っている場合は、VLOOKUP関数への置き換えを検討されてみては・・・。

ということで、ここまでお読みいただきありがとうございました。