ExcelでのVLOOKUPの代用(MATCH,INDEXとMATCH,OFFSET)と速度について

よく忘れるのでメモ。

知ってる人も多いと思いますが、ExcelにはVLOOKUPという関数があります(参考：VLOOKUP 関数 – Office のサポート)。これは、表の中にある、特定の値(検索値)に対応する値を取得する関数なのですが、この関数は検索値よりも右側の値しか取得できません。最初、第三引数にマイナスの値を指定する右側の値を取得できると思って試したのですが、無理でした。このため、昔は表の右側に、検索値より左側の値を参照する式を追加していていました(検索値がB列でほしい値がA列の場合、C列に”=A列の値”といった式を入力する)。

ただ、最近になって代用方法があるということを知りました。どうやら、MATCH関数とINDEX関数の組み合わせ、もしくはMATCH関数とOFFSET関数の組み合わせで代用可能とのこと(参考：徹底解説(VLOOKUP,MATCH,INDEX,OFFSET)｜エクセル関数超技)。

簡単に説明すると、MATCH関数は指定の範囲から検索値が何番目にあるかを返す関す、INDEX関数は指定の範囲内にある指定の行数、列数から値を取得する関数、OFFSET関数は指定のセルと行数、列数から指定のセルの行数、列数分移動したところにある値を取得する関数です。

言葉だけでは分かりにくいと思うので、実際に試した結果の画像を下記に載せておきます。

F列がG列の式をセットした結果です。検索範囲はA列からC列、検索値はF1にある『近畿』です。見てもらえればなんとなく何をしているか分かるかと思います。F2,F5,F7では『近畿』という値をB列から検索し、その右側の値を取得して『7』という結果を得ています。F4,F6では『近畿』という値をB列から検索し、その左側の値を取得して『5』という結果を得ています。

直感的にはINDEX関数よりOFFSET関数のほうが意味合いが分かりやるいと思っています(INDEX関数って言われても、どういう関数なのかピンと来ない)。ただ、OFFSET関数は第二引数に-1を付けなきゃいけない(1引く必要がある)ので、自分は何度も間違えました。なので、慣れたらINDEX関数とOFFSET関数の組み合わせで対応するのがいいかと思います。

というより、そもそもこの組み合わせを分かっていたらVLOOKUP関数を使う必要はないかもしれません。たとえ探したい値が検索値より右側にあったとしてもINDEX関数とOFFSET関数さえ使っておけばいいんじゃないかと(もちろん、こっちのほうが少し冗長ですが)。

ただ、そこで気になるのが速度の問題です。関数が一つですむVLOOKUP関数にたいして、INDEX関数とOFFSET関数の組み合わせだと関数を二つ使うことになります。ということは、その分、速度も遅くなってしまうのではないかと。

というわけで、検証してみることにしました。用意したのはアルファベットをランダムに3文字並べた文字列50万個をSheet1とSheet2の二つのシートのA列に記載し、Sheet2のほうにはB列に行番号を追記するというもの。その後、式のままだと問題があるので、式を結果で上書きしました(全体をコピーした後、右クリックで値貼り付け)。
参考：アルファベットをランダムに表示したい−CHAR関数・RANDBETWEEN関数:Excel(エクセル)の関数・数式の使い方-文字列

作成後は下記のようになっています。
Sheet1

Sheet2

その後、VBAにて計測処理とSheet1のB列において検索式の追加処理、C1の値に処理を開始してから完了するまでの時間(秒)を記載する処理を追加。具体的には下記のような感じ。

Sub INDEX_MATCH実行()
'
    MaxRow = Range("A1").End(xlDown).Row
'
    Range("B1").Value = "=INDEX(Sheet2!B:B,MATCH(A1,Sheet2!A:A,0))"
    Range("B1").Select
    Selection.Copy
    Range("B" & MaxRow).Select
    Range(Selection, Range("B2")).Select
    ActiveSheet.Paste
    Application.CutCopyMode = False
End Sub

Sub INDEX_MATCH実行_計測()
    ' 再計算をオフ
    ActiveSheet.EnableCalculation = False
    
    befTime = Timer
    Call INDEX_MATCH実行
    
    ' 再計算をオン
    ActiveSheet.EnableCalculation = True
    
    afterTime = Timer
    
    Range("C1").Value = afterTime - befTime
    MsgBox (Range("C1").Value)
End Sub

上記はINDEXとMATCH関数の組み合わせのみ記載しましたが、VLOOKUP関数やOFFSETとMATCH関数の組み合わせでもほとんど同じです。5行目がVLOOKUP関数になるかOFFSETとMATCH関数になるかが違うぐらいです。

結果は下記のような感じになります(環境Excel2010です)。

以下、それぞれ5回ずつ試した結果。

ほぼ全く関係なさそうです。微妙に違いますが、誤差レベルと思われます。INDEX,MATCH関数の組み合わせよりOFFSET,MATCH関数の組み合わせのほうがちょっとだけ速いような気もしなくはないですが、気にするほどの差はないかと思われます(VLOOKUP関数の1回目が遅いのは、最初の実行だからかと思われます)。