VLOOKUP for combination by two condition (VLOOKUP3)

Suppose, we need to combine two tables, but we don't have unique values:

As we can see, we have borrowers with equal surnames and loan agreements with equal numbers. To combine using VLOOKUP will be difficult because it combine only by one condition and only by the first occurrence.

Remake VLOOKUP for substitution values by two condition.

Open menu Servise - Macros - Visual Basic Editor, insert new module (menu Insert - Module) and copy there this code:

Function VLOOKUP3(Table1 As Range, SearchValue1 As Variant, Table2 As Range, SearchValue2 As Variant, ResultColumn As Range)
        Dim i As Integer
        For i = 1 To Table1.Rows.Count
                If Table1.Cells(i, 1) = SearchValue1 Then
                If Table2.Cells(i, 1) = SearchValue2 Then
                VLOOKUP3 = ResultColumn.Cells(i, 1)
                    Exit For
                    End If
                End If
                Next i
    End Function 

Close Visual Basic Editor and return in Excel.

Now in Function Wizard in category User defined you can find our function VLOOKUP3 and use it. Syntaxes of this function follow:

=VLOOKUP3(range1; required_value1; range2; required_value2; substitution_range)

So, for correct substitution the loan amount by surname and loan agreement, in cell E15 you should input:


Also, don't forget to fix the range by dollar sign ($), in order to the ranges don't slide down during copying a formula (for quick fixing you can use hotkey F4).