ExcelのVLOOKUP関数で左端列以外を検索したいときのメモ

VLOOLUP、真ん中の列で検索させてくれ

VLOOKUP関数、よく使いますね。
しかし、データ範囲の左端列しか検索してくれなくて困ることがあります。
勝手にデータレイアウトを変えるわけにはいかず、別シートにいい感じに参照させていい感じにする面倒なことをして凌いでいました。

調べてみると、INDEX関数とMATCH関数で代替できることがわかったのでメモします。
また、いちいち書くのは面倒なので自作関数を作りました。

それぞれどんな関数なの？

VLOOKUP関数

VLOOKUP(検索値, 検索値を含む範囲, 戻り値を含む範囲内の列番号, 近似一致には TRUE または完全一致には FALSE を必要に応じて指定)

「指定された範囲の１列目で特定の値を検索し、指定した列と同じ行にある値を返します。」だそうです。

INDEX関数

INDEX(参照, 行番号, [列番号], [領域番号])

「指定された行と列が交差する位置にある値またはセルの参照を返します。」だそうです。

MATCH関数

MATCH(検査値, 検査範囲, [照合の型])

「指定された照合の種類に従って検査範囲内を検索し、検査値と一致する要素の、配列内での相対的な位置を表す数値を返します。」だそうです。

で、どうやんの？

MATCH関数で検索値の行を取得
⇒取得した行に対して、INDEX関数で列を指定して値を取得する

=INDEX(データ範囲,MATCH(検索値,検索範囲,0),返却列)

実際にやってみる

やりたいこと

名字ランキングの表がある。
「カナ」（黄色部分）を入力すると、表を検索して「漢字」「順位」を取得したい。

こんな感じでできる

=INDEX(データ範囲,MATCH(検索値,検索範囲,0),返却列)
=INDEX(A:C,MATCH(E3,C:C,0),2)
「コバヤシ」(E3)をC列(C:C)から探し、その行に該当する表(A:C)の2列目にある「小林」(B11)をF3に返す

「表の検索」関数を作る

いちいち=INDEX...と入力するのは面倒なので、自作関数を作ります。
=表の検索(検索値,データ範囲,検索範囲,返却列番号)
とすれば、
=INDEX(データ範囲,MATCH(検索値,検索範囲,0),返却列)
と同じようにします。

表の検索

Function 表の検索(検索値 As Variant, データ範囲 As Variant, _
                            検索範囲 As Variant, 返却列番号 As Integer)

    表の検索 = WorksheetFunction.Index(データ範囲, _
                        WorksheetFunction.Match(検索値, 検索範囲, 0), 返却列番号)

End Function

参考リンク

▼WorksheetFunction.Match メソッド (Excel)
https://msdn.microsoft.com/ja-jp/library/office/ff835873.aspx

▼WorksheetFunction.Index メソッド (Excel)
https://msdn.microsoft.com/ja-jp/library/office/ff197581.aspx