VLOOKUP2

	Look up on two fields with this alternative to VLOOKUP

	Uses the two worksheet functions INDEX and MATCH within the VBA Evaluate method.
	This version requires you to use columns one and two of the source table for the lookup values. With slight modification you can look up values based on any two nominated columns in the table.

Purpose	Look up a value based the first two columns of a table.

Download	vlookup2.zip

oooo


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

Based on	http://support.microsoft.com/support/kb/articles/q59/4/82.asp

oooo

Here's what the output from VLOOKUP2 looks like:

ColA	ColB	ColC	3	Value look up in column 1
One	4	4	35	Value look up in column 2
Two	5	10	9	Result
One	6	6
Two	Four	8	One	Value look up in column 1
One	5	5	5	Value look up in column 2
3	6	18	5	Result
3	5	15
33	5	12	Two	Value look up in column 1
3	35	9	Four	Value look up in column 2
Two	6	12	8	Result

oooo

Code

'' ***************************************************************************
'' 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
''
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