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