Look up on multiple fields with this alternative to VLOOKUP

Uses the two worksheet functions INDEX and MATCH within the VBA Evaluate method.
Use any columns in any order, from the source table, for the lookup values.



Look up a value based on up to three columns of a table.





 Syntax VLOOKUPX(lookup_value1,column_value1,lookup_value2,
  lookup_value1 is found in column_value1 of the table array  
  lookup_value2 is found in column_value2 of the table array  
  lookup_value3 is found in column_value3 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 Can use one, two or all three arguments (see examples)  
Based on http://support.microsoft.com/support/kb/articles/q59/4/82.asp  


Here's what the output from VLOOKUPX looks like:


Option Explicit

'' ***************************************************************************
'' Purpose  : Lookup function based on three columns / Demonstrate use of Evaluate
'' Written  : 17-Nov-2004 by Andy Wiggins, Byg Software Limited
'' Notes    : Based on VLOOKUP2
Function VLOOKUPX(pVal1, pCola As Integer, _
                  pVal2, pColb As Integer, _
                  pVal3, pColc As Integer, _
                  pRng As Range, pInd As Integer)

Dim lStr_Seek As String
Dim lStr_Col1 As String
Dim lStr_Col2 As String
Dim lStr_Col3 As String
Dim lStr_ColR 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_VLOOKUPX

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

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

    VLOOKUPX = Err

End Function
Published: 17-Nov-2004
Last edited: 20-Aug-2011 13:57