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: