Attribute VB_Name = "Module1"
'Syntax VLOOKUP2(lookup_value1, lookup_value2, table_array, col_index_num)
'
'  lookup_value1 is found in the first column of the table array
'  lookup_value2 is found in the second column of the table array
'  table_array is the table of information in which data is looked up. Use a reference to a range or a range name
'  col_index_num is the column number in table_array from which the matching value must be returned
'
'Remarks If VLOOKUP2 can't find either lookup_value, it returns the #N/A value.
'  All fields are required
'

'' ***************************************************************************
'' Purpose  : Lookup function based on two columns / Demonstrate use of Evaluate
'' Written  : 30-Oct-2001 by Andy Wiggins, Byg Software Limited
'' Amended  : 09-May-2002 by Andy Wiggins
'' http://www.bygsoftware.com/Excel/VBA/vlookup2.htm
Function VLOOKUP2(pVal1, pVal2, pRng As Range, pInd As Integer)
''The lookup values refer to columns 1 and 2 in the range
Application.Volatile

Dim lStr_Seek As String
Dim lStr_Col1 As String
Dim lStr_Col2 As String
Dim lStr_Col3 As String

''If an error occurs with "Evaluate" it isn't passed to this function's error handler
''This handler will pick up any other errors that may occur
On Error GoTo Error_VLOOKUP2

    ''The quotes enure strings are treated as such and NOT as range names
    lStr_Seek = """" & pVal1 & ":""&""" & pVal2 & """"
    lStr_Col1 = pRng.Columns(1).Address
    lStr_Col2 = pRng.Columns(2).Address
    lStr_Col3 = pRng.Columns(pInd).Address

    VLOOKUP2 = Evaluate("index(" & lStr_Col3 & ",match(" & _
          lStr_Seek & "," & lStr_Col1 & "&"":""&" & lStr_Col2 & ",0))")
    Exit Function

Error_VLOOKUP2:
    VLOOKUP2 = Err
End Function