Improve VLOOKUP function (VLOOKUP2)

Suppose we have such table of loan agreements:

We need to know, for instance, which was the amount of the third Mike's agreement or when John has made his second deal. Embeded function VLOOKUP able to search for only first occurance of surname in the table and cannot be useful.

Let write our function, which will be search for not only first occurrence, but generally, N-th. Let name it, for instance, VLOOKUP2.

Open menu Service - Macros - Visual Basic Editor, insert new module (menu Insert - Module) and copy to there text of this function:

Function VLOOKUP2(Table As Range, SearchColumnNum As Integer, SearchValue As Variant, _
                                        N As Integer, ResultColumnNum As Integer)        
    Dim i As Integer
    Dim iCount As Integer
       
    For i = 1 To Table.Rows.Count
            If Table.Cells(i, SearchColumnNum) = SearchValue Then
                iCount = iCount + 1
            End If
            If iCount = N Then
                VLOOKUP2 = Table.Cells(i, ResultColumnNum)
                Exit For
            End If
        Next i
End Function 

Close Visual Basic Editor and return to Excel.

Now, in the Function wizard in category User definded you can find our function VLOOKUP2 and use it. Syntaxes of the function follow:

=VLOOKUP2 (table; column number where we search for; searched value; N; column number where we take value)

For example, if we want to find amount of third order of Mike we should input:

=VLOOKUP2(A1:F19;1;"Mike";3;4)