<<Look up table issue>>

XL_2K3

New Member
Joined
Aug 16, 2007
Messages
39
Hey i'm back! :LOL:

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! :eek: in other words with this VBA code you can't invoke values from another table located in another spreadsheet, needless to say workbook. :unsure: 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:

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. :)
 

Some videos you may like

Excel Facts

Using Function Arguments with nested formulas
If writing INDEX in Func. Arguments, type MATCH(. Use the mouse to click inside MATCH in the formula bar. Dialog switches to MATCH.

Domski

Well-known Member
Joined
Jan 18, 2005
Messages
7,292
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

New Member
Joined
Aug 16, 2007
Messages
39
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: lol.
 

XL_2K3

New Member
Joined
Aug 16, 2007
Messages
39
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,123,281
Messages
5,600,705
Members
414,401
Latest member
grenona2020

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top