【ExcelVBA】VLOOKUP関数の使い方とは？よくあるエラーの対策も解説！

こんにちは、VBAエンジニアのやすこれです。

みなさんは、VBAでVLOOKUP関数を使ったことがありますか？

VBAでも検索キーを使ってセル範囲から対応する値を取得したい
ワークシート関数VLOOUPなら使えるけどVBAではやり方がわからない
VBAでもワークシート関数VLOOKUPを使う方法が知りたい！

という方もいるのではないでしょうか。

そこで今回は、VBA上で動作するVLOOKUP関数をご紹介します。

Excel　VBA実戦のための技術入門レベルでは決して足りない実務に必須のスキルとは [ 沢内晴彦 ]

posted with カエレバ

Amazon

楽天市場

Yahooショッピング

VBAでのVLOOKUP関数の使い方
1. VLOOKUP関数の基本的な使い方
2. VLOOKUP関数の「検索方法」について
VLOOKUP関数のエラー値と回避方法
1. VLOOKUP関数のエラー値
2. VLOOKUP関数エラー回避方法
INDEX関数とMATCH関数を組み合わせてVLOOKUP関数のように使う
VBAでのVLOOKUP関数まとめ

VBAでのVLOOKUP関数の使い方

VLOOKUP関数の基本的な使い方

VLOOKUP関数は、任意の検索キーで指定範囲1列目を検索し、値が一致した行の指定した列数に対応する値を返す関数です。

基本構文

WorksheetFunction.VLookup(検索値, 範囲, 列番号, 検索方法)

コード例1

WorksheetFunction.VLookup(5, ActiveSheet.Range("A1:B10"), 2, 0)

アクティブシート上「A1:B10」1列目のデータから、整数「5」で完全一致した行の「2」列目を返しています。検索方法の「0」は完全一致で検索することを意味しています。

画像のように上から5番目のA5セルに「5」があり、列番号で「2」を指定しているので、コード例の数式は「E」を返します。

また、検索値はセルの値で指定することもできます。その場合は次のように「.Value」は記載せず直接「Range」を指定するか、「.Value2」を指定するようにしましょう。

「.Value」を指定した場合、日付型などで正常に検索できないことがあるので覚えておいてください。

「.Value2」は、セルの表示形式に関わらず値そのものを返すプロパティ

コード例2

WorksheetFunction.VLookup(ActiveSheet.Range("D1"), ActiveSheet.Range("A1:B10"), 2, 0)

VLOOKUP関数はこのように、指定した範囲で1列目が検索キーと一致する行の指定列の値を返すことができます。また、検索方法には完全一致の他に近似一致（二分探索）があるので、詳しく解説していきす。

VLOOKUP関数の「検索方法」について

検索方法には2つの値を指定できます。それぞれの意味は下記の通りです。

0（FALSE）　→　範囲1列目を「完全一致」で検索
1（TRUE）　 →　範囲1列目を「近似一致」で検索

言葉だけでは伝わりづらいと思うので、範囲を「A1:B10」セルとした場合の具体的な例を順に紹介します。

検索方法「0」（完全一致）、検索値は「5」の場合

まず「完全一致で検索する場合」について解説します。

引数の値

検索値：5
範囲：ActiveSheet.Range(“A1:B10”)
列番号：2
検索方法：0

コード

WorksheetFunction.VLookup(5, ActiveSheet.Range("A1:B10"), 2, 0)

「完全一致」は範囲1列目の内、検索値と完全に一致する値の行から、指定した列番号の値を返します。範囲のデータは、昇順や降順で並んでいなくても問題ありません。

検索値「5」と完全に一致する「A5」セルは範囲1列目の5行目のセルで、列番号は「2」を指定しているので返り値は「E」となります。

検索方法「1」（近似一致）、検索値は「3」の場合

次に「近似一致で検索する場合」について解説していきます。

引数の値

検索値：3
範囲：ActiveSheet.Range(“A1:B10”)
列番号：2
検索方法：1

コード

WorksheetFunction.VLookup(3, ActiveSheet.Range("A1:B10"), 2, 1)

「近似一致」は範囲1列目の内、検索値に近似一致する値の行から、指定した列番号の値を返します。範囲のデータは、昇順で並んでいる必要があります。

検索方法に「近似一致」を指定した場合、対象の範囲1列目を「二分探索」で検索します。

二分探索とは

検索値が検索範囲の上にあるか下にあるか（左にあるか右にあるか）を繰り返し検索して目的の値を探す方式
データが昇順に並んでいる必要がある
データが昇順でない場合は、正しい結果が求められない

検索値「3」は範囲1列目の中央、A5セルの「5」より小さいので、A5セルより上にあると判定されます。

さらに、A1～A4セルの中央、A2セルの「2」より大きくA2セルより下にあると判定されます。最後にA3～A4セルの中央、A3セル「3」と比較した際に検索値と一致します。また、指定した列番号は「2」なので範囲3行目の2列目の値「C」を返します。

VLOOKUP関数のエラー値と回避方法

VLOOKUP関数のエラー値

便利なVLOOKUP関数ですが、気を付けなければいけないこともあります。それはエラーが起き、マクロ処理が中断されることです。

どういう時にエラーがでるのか、例を挙げます。

検索方法「0」・検索値と一致する値が範囲にない
検索方法「1」・データ降順

どちらも「VLookupプロパティを取得できません。」というエラーとなります。

エラーはつきものですので、次に解説する回避方法も覚えてしまいましょう！

VLOOKUP関数エラー回避方法

エラーが起きた際は、検索方法によって次の2点を確認すれば原因がわかります。

検索方法「0」なら、検索値に一致するデータがあるか
検索方法「1」なら、データが昇順か

しかし実務では、検索値がデータにあった場合のみ、対応する値を求めたいことも多いでしょう。検索結果の有無で処理を分けるには、次のようなコードで対応できます。

'エラーが起きたら次のステップへ進むよう設定
On Error Resume Next
'VLookup関数で検索したい範囲を変数に代入
Dim vRng As Range
Set vRng = ActiveSheet.Range("A1:B10")
'VLookup関数で求めた値を変数に代入
Dim vAns As Long
vAns = WorksheetFunction.VLookup(5, ActiveSheet.Range("A1:A10"), 2, 0)

'検索結果がない（エラーが発生した）場合はErr.Numberは「0」以外になる
If Err.Number <> 0 Then
    
    'エラー時の処理をこの位置に記載
    
    'Err.Numberを初期値に戻す
    Err.Number = 0
End If

'エラー時の処理を初期値へ戻す
On Error GoTo 0

2行目の「On Error Resume Next」でエラーが起きても次の処理へ進むよう設定しているのがポイントです。

何かエラーが起きた場合は「Err.Number」に0以外の値が設定されるので、11行目の「If」文の条件に「Err.Number <> 0」を指定しています。

最後に、エラーが起きても次の処理へ進む指定は、ピンポイントでないと異常処理へつながりかねないので、Err.Numberとともに初期値へ戻す（「On Error GoTo 0」）ことを忘れないようにしましょう。

INDEX関数とMATCH関数を組み合わせてVLOOKUP関数のように使う

検索範囲から任意の値でセルを探せるVLOOKUP関数ですが、VLOOKUP関数で検索できるのは、範囲の左端のみなので使い勝手が悪い面もあります。

そこで今回は、範囲のどの行・列からでも検索ができ、より自由度の高いINDEX関数とMATCH関数を組み合わせた方法を紹介します。

またこの方法は、VLOOKUP関数よりも処理が早いので、積極的に使ってみてくださいね。

まず、INDEX関数から解説していきます。

基本構文

WorksheetFunction.Index(範囲, 行番号, 列番号)

コード例

WorksheetFunction.Index(ActiveSheet.Range("A1:B5"), 4, 1)

アクティブシート上のセル範囲「A1:B5」4行目・1列目の値を返します。

画像のようにセル範囲にデータがある場合、4行目・1列目の「いちご」を返すコードとなります。

つづいて、MATCH関数について解説します。

基本構文

WorksheetFunction.Match(検索値, 検索範囲, 検索方法)

コード例

WorksheetFunction.Match(5, ActiveSheet.Range("A1:A10"), 0)

アクティブシート上「A1:A10」の範囲から、整数「5」で完全一致した値を返しています。検索方法の「0」は完全一致で検索することを意味しています。

画像のように上から5番目のA5セルに「5」があるので、コード例の数式は「5」を返します。

【ExcelVBA】MATCH関数の使い方とは？よくあるエラーの対策も解説！

こんにちは、VBAエンジニアのやすこれです。みなさんは、VBAでMATCH関数を使ったことがありますか？ VBAでも値によってセル範囲を取得したいワークシート関数MATCHなら使えるけどVBAではやり方がわからない

ここまでの内容をおさえた上でVLOOKUP関数のように使う方法を確認していきましょう。

VLOOKUP関数は、指定した検索値が範囲1列目の値と一致した行から指定した列の値を返す関数です。なので、INDEX関数の行指定をMATCH関数で行い、列を指定することで、VLOOKUP関数のように使えます。

次のコード例では「いちご」の「価格」を求めています。

'MATCH関数で使う検索範囲を変数「matchRng」に格納
Dim matchRng As Range
Set matchRng = ActiveSheet.Range("A1:A5")

'MATCH関数で「いちご」の行を取得して変数「matchRow」に格納
Dim matchRow As Long
matchRow = WorksheetFunction.Match("いちご", matchRng, 0)

'INDEX関数で使う検索範囲を変数に格納
Dim indexRng As Range
Set indexRng = ActiveSheet.Range("A1:B5")

'INDEX関数で先ほど取得した「いちご」の行（matchRow）と価格の列「2」を指定
Dim trgtPrice As Long
trgtPrice = WorksheetFunction.Index(indexRng, matchRow, 2)

少しコードが複雑になってきたので、順を追って解説します。

'MATCH関数で使う検索範囲を変数「matchRng」に格納
Dim matchRng As Range
Set matchRng = ActiveSheet.Range("A1:A5")

まず、変数「matchRng」にMATCH関数で検索したい範囲「A1:A5」を定義しています。

'MATCH関数で「いちご」の行を取得して変数「matchRow」に格納
Dim matchRow As Long
matchRow = WorksheetFunction.Match("いちご", matchRng, 0)

次に、MATCH関数を使って「いちご」が入力されているセルを検索し、変数「matchRow」に値を入れています。この時、MATCH関数の検索範囲「A1:A5」は、先ほど定義した変数「matchRng」を使って設定しています。

'INDEX関数で使う検索範囲を変数に格納
Dim indexRng As Range
Set indexRng = ActiveSheet.Range("A1:B5")

変数「indexRng」にINDEX関数で検索したい範囲「A1:B5」を定義しています。

'INDEX関数で先ほど取得した「いちご」の行（matchRow）と価格の列「2」を指定
Dim trgtPrice As Long
trgtPrice = WorksheetFunction.Index(indexRng, matchRow, 2)

変数「trgtPrice」に求めたい「いちご」の「価格」を定義しています。INDEX関数の値にはそれぞれ次の値を設定しています。

範囲：「indexRng」（＝「A1:B5」）
行：MATCH関数で求めた値を定義した「matchRow」（「いちご」の行=4）
列：「価格」列を表す「2」

これでVLOOKUP関数のように、値を求めることができます。また、INDEX関数、MATCH関数でも検索結果がない場合は、VLOOKUP関数と同様に「Index（MATCH）プロパティを取得できません。」というエラーが発生します。

VLOOKUP関数の時と同じ方法で、エラー時の処理も対応しておきましょう。

VBAでのVLOOKUP関数まとめ

今回はVBAでのVLOOKUP関数の使い方をご紹介いたしました。

VLOOKUP関数を使えば、任意のセル範囲の中で検索キーに対応する値を調べられるので、使いこなせば非常に便利です。また、今回紹介したエラー回避方法や、応用として解説したINDEX関数とMATCH関数の組み合わせや二分探索も理解しておくと、応用が効くのでおすすめです。

ぜひ、使ってみてくださいね！