VLOOKUPX

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.

 

Purpose

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

 
Download

vlookupx.zip

oooo

     
 Syntax VLOOKUPX(lookup_value1,column_value1,lookup_value2,
column_value2,lookup_value3,column_value3,table_array,col_index_num)		  
   
  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  
     

oooo

Here's what the output from VLOOKUPX looks like:

oooo

Code 
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)
Application.Volatile

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

Error_VLOOKUPX:
    VLOOKUPX = Err

End Function

 
 
Published: 17-Nov-2004
Last edited: 01-Apr-2007 19:11