VLOOKUP for combination according to two conditions (VLOOKUP3)


Let's consider an improved version of the function VLOOKUP - VLOOKUP3, which gives us the opportunity to substitute values when two conditions match. This function can be useful in cases where we do not have unique values in the field on which we need to perform a combination.

An example of a problem

Suppose we need to join two tables, but we have no unique values:

As you can see, we have borrowers with the same surnames and loan agreements with the same numbers. Combine with a normal function VLOOKUP will be problematic because it matches only one condition at a time and only the first value found.

Let's redo what we're used to VLOOKUP for substituting values according to two conditions.

VBA code for function VLOOKUP3

Open the menu Service - Macro - Editor Visual Basic , insert the new module (menu Insert - Module ) and copy the text of this one there functions:

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 the Visual Basic editor and return to Excel.

Using the function

Now in Function Wizards in the category User defined you can find our VLOOKUP3 function and use it. The syntax of the function is as follows:

=VLOOKUP3(range1 ; searched_value1 ; range2 ; searched_value2 ; the range from which we substitute values )

That is, in order to correctly substitute the loan amount by last name and agreement number, you will need to enter the following in cell E15:

=VLOOKUP3($A$2:$A$11 ; A15 ; $B$2:$B$11 ; B15 ; $C$2:$C$11 )

Also, do not forget to fix the ranges with a dollar sign ($), so that we do not miss the ranges when copying the formula (for quick fixing, you can also use the F4 key).

Related Articles: