Vlookup – VBAで複数の結果を表示する

テキストをVlookupする

標準のVlookup 関数は、テーブル内の値を検索するために使用できます。

そして、このようにVLOOKUPを使用します。

=VLOOKUP("Dog",A1:B10,2,FALSE)

を使って、値30を得ます。

しかし、このリストではDogが3回出現しています。標準のVLOOKUP関数は、このリストの最初のアイテムに関連する値のみを返し、2番目または3番目の “Dog “のインスタンスである125または9,250を返すことはありません。

Vlookupで複数の結果を得る

次の関数では、範囲、検索する式、インスタンス（結果番号）を指定し、それに対応する値を返すことができます。

Function Find_nth_Occurrence(Column_Range As Range, Expression As String, Occ As Integer) As Double
Dim Cell
Dim Occurrences_to_date As Integer

Find_nth_Occurrence = 1000000

Occurrences_to_date = 0

For Each Cell In Column_Range
   If Cell.Value = Expression Then
       Occurrences_to_date = Occurrences_to_date + 1
        If Occurrences_to_date = Occ Then
             Find_nth_Occurrence = Cell.Offset(0, 1).Value
        End If
   End If
Next Cell

End Function

標準のVLOOKUP関数との主な違いは、この場合、範囲はラベルの範囲のみで、データ範囲全体ではないことです。

以下は、コマンドボタンのクリックイベントに基づいて、この関数を呼び出すサブルーチンです。Sheet2のA1:A8の範囲で、Dogという単語の3番目のインスタンスを検索しています。

Private Sub CommandButton1_Click()
Dim Answer As Double
Answer = Find_nth_Occurrence(Sheets("Sheet2").Range("A1:A8"), "Dog", 3)
MsgBox Answer
End Sub

変数Answerには関数の結果が格納され、画面上のMsgboxに表示されます。

しかし、もしその単語がリストの中に見つからなかったり、指定した順番よりも少ない数しかない場合、例えば “Dog” という単語の5番目のインスタンスを指定した場合、1,000,000 という値が返されます 🙂

Answer = Find_nth_Occurrence(Sheets(“Sheet2”).Range(“A1:A8”), “Dog”, 5)

または

Answer = Find_nth_Occurrence(Sheets(“Sheet2”).Range(“A1:A8”), “Horse”, 2)

<<VBAのサンプルに戻る