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).