<<Look up table issue>>

[XL_2K3]

Hey i'm back!

Ok, here is the scenario of what i'm trying to do this time:

I have several workbooks, and each of the workbooks contains several spreadsheets... So updating the data in each of the spreadsheets of all workbooks would be an overwhelming task!

But, if i make use of the VLOOKUP or combining the INDEX & MATCH fucntions then that would be another story...

The problem is that i need that at least three values of three different columns in the same row match in order to the invoked value located in the Main table be retrived to the secondary table (cell) that is invoking the value...

That part is alredy (partially) solved. To achieve the scenario above i'm using a very great UDF which Look up a value based on up to three columns of a range of cells, BUT, it only retrieves values invoked from the same spreadsheet! in other words with this VBA code you can't invoke values from another table located in another spreadsheet, needless to say workbook. So i wonder if any of the MVP or high advanced users could help me by trying to modify this UDF and make it capable to work using spreadsheet and wokbook reference...

Well, here is the code:

Option Explicit

'' ***************************************************************************
'' Purpose  : Lookup function based on two columns / Demonstrate use of Evaluate
'' Written  : 17-Nov-2005 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

Any help will be greatly appreciated!

P.S.If you need more infomration or a spreadsheet sample just ask.

 

[Domski]

Hi,

You could add a helper column with the three values concatonated together to create a unique key to base your look up on e.g.:

=AI&CHAR(13)&B1&CHAR(13)&C1

It's a good idea to separate the values with a little used character, like CHAR(13), as, whilst it's unlikely, concatonating calues like 123 and 456 will give you 123456 whilst 12 and 3456 will also. Adding the extra character means that if a situation such as that arises you still have unique values to look up.

Dom

 

[XL_2K3]

Hi,

You could add a helper column with the three values concatonated together to create a unique key to base your look up on e.g.:

=AI&CHAR(13)&B1&CHAR(13)&C1

It's a good idea to separate the values with a little used character, like CHAR(13), as, whilst it's unlikely, concatonating calues like 123 and 456 will give you 123456 whilst 12 and 3456 will also. Adding the extra character means that if a situation such as that arises you still have unique values to look up.

Dom

Hi Domski:

Thanks for the input, i didn't know of this "strategy" to avoid two different calcs to be displayed in a single cell when concatenating formulas, and although this tip won't work for my purpose (in this case) it will work to solve another issue of other project i have been dealing with for a while, so thanks for your serendipitous solution to another problem! lol.

 

[XL_2K3]

Ok, i just found the link to site from where i downloaded the UDF

here is the link:

VLookupx

Hope you can figure out how to fix it and make it work with spreadsheet and workbook references.

 

[XL_2K3]

Still no results?