VBA Custom function to replicate INDEX MATCH MATCH with predefined fixed arrays

[ScotfiCZ]

TLDR - Create a VBA custom function (OurDataFx) replicating INDEX MATCH MATCH sourcing to another workbook which can be used in any Excel spreadsheet and you only need to set up MATCH lookup_value parameters in this OurDataFx because INDEX array and MATCH lookup_array are defined directly via VBA.

Full Detail:
I have product details in sheet "Data" in workbook "Database.xlsm" with file path "D:\Team\Products\Database.xlsm", which is an export from our online system.

Often, while working in different workbooks, I (and other members of the team) need to refer to data in this "Data" sheet in "Database.xlsm" workbook, to look up for prices, warehouse locations, etc.

Because both columns and rows may be changing their order (e.g. adding a new attribute or products in our online system), we refer to the data by using Index Match Match formula, knowing that our reference (product code) is always in the first column and attribute headers for the values we want to retrieve are always in the first row.

Therefore, if we try to retrieve "price" information for product code in cell A1, we can use this standard Index Match Match formula:

=INDEX('D:\Team\Products\[Database.xlsm]Data'!$1:$1048576,MATCH(A1,'D:\Team\Products\[Database.xlsm]Data'!$A:$A,0),MATCH("price",'D:\Team\Products\[Database.xlsm]Data'!$1:$1,0))

The problem is - not everyone in our team is comfortable with 'complicated' functions like this and it also takes a while to write.

So I would like to use VBA to code a custom function which would be:

1.1 Possible to use in any workbook you are in (regardless whether the source data file ("Database.xlsm") is open or not) - I imagine it as if MS Excel application is somehow connected to the libraries of VBA / custom functions and make them available to all Excel files, even if that particular VBA code is not saved in the file itself. Is that possible?

1.2 If the above is possible, can this "library with custom function" be shared among different computers (all of them have access to the same shared diskspace / same folder structure)?

Now, the above custom function (let's name it "OurDataFx") should have this syntax:

=OurDataFx(reference,attribute)

Where 'reference' is selected cell (A1) containing product code which is a lookup_value used to MATCH our row_num, and where 'attribute' is a product attribute header which is used as a lookup_value to MATCH our column_num. While writing this custom function by user,user does not need to specify INDEX array nor MATCH lookup_arrays because this is all pre-defined directly in VBA as it never changes.

Therefore, when user writes this custom function:

=OurDataFx(A1,"price")

We should receive same results as if we wrote this standard function:

=INDEX('D:\Team\Products\[Database.xlsm]Data'!$1:$1048576,MATCH(A1,'D:\Team\Products\[Database.xlsm]Data'!$A:$A,0),MATCH("price",'D:\Team\Products\[Database.xlsm]Data'!$1:$1,0))

If user was applying the same custom function with another parameters, it could like look this:

=OurDataFx(BA25,"primary_warehouse")

Bringing same results as this standard function would have:

=INDEX('D:\Team\Products\[Database.xlsm]Data'!$1:$1048576,MATCH(BA25,'D:\Team\Products\[Database.xlsm]Data'!$A:$A,0),MATCH("primary_warehouse",'D:\Team\Products\[Database.xlsm]Data'!$1:$1,0))

Anyone able to help with this? I have some basic understanding of VBA for macros but struggle with the custom functions - tried to watch some Youtube tutorials but could not find anything that would be useful for the above. Thank you for your help!

 

[DanteAmor]

It could be this way, but the "Database" workbook must be open.

Function OurDataFx(sRef As String, sAtt As String)
  Dim WbSh As String
  
  WbSh = "'D:\Team\Products\[Database.xlsm]Data'!"
  OurDataFx = Evaluate("=INDEX(" & WbSh & "$1:$1048576," & _
           "MATCH(""" & sRef & """," & WbSh & "$A:$A,0)," & _
           "MATCH(""" & sAtt & """," & WbSh & "$1:$1,0))")
End Function

 

[ScotfiCZ]

It could be this way, but the "Database" workbook must be open.

Function OurDataFx(sRef As String, sAtt As String)
  Dim WbSh As String
 
  WbSh = "'D:\Team\Products\[Database.xlsm]Data'!"
  OurDataFx = Evaluate("=INDEX(" & WbSh & "$1:$1048576," & _
           "MATCH(""" & sRef & """," & WbSh & "$A:$A,0)," & _
           "MATCH(""" & sAtt & """," & WbSh & "$1:$1,0))")
End Function

Thank you DanteAmor. It doesn't work when specifying full file path but when I set WbSh = "'[Database.xlsm]Data'!" everything works - and exactly as you say, the data source file must be open. By any chance, do you know any way how to bypass this need? It would be great if the custom function works regardless if the "Database.xlsm" is open or not.

 

[DanteAmor]

It can be with a macro. For example, you put the reference in A1, the attribute in F2, the macro could automatically set the formula in G2:

Sub PutFormula()
  Dim WbSh As String, sRef As String, sAtt As String
  sRef = Range("A1").Value
  sAtt = Range("F2").Value
  WbSh = "'D:\Team\Products\[Database.xlsm]Data'!"
  Range("G2").Formula = "=INDEX(" & WbSh & "$1:$1048576," & _
           "MATCH(""" & sRef & """," & WbSh & "$A:$A,0)," & _
           "MATCH(""" & sAtt & """," & WbSh & "$1:$1,0))"
End Sub

Or in the sheet events, if you modify the cell, for example cell F2, it automatically puts the formula in G2:

Private Sub Worksheet_Change(ByVal Target As Range)
  If Not Intersect(Target, Range("F2")) Is Nothing Then
    Dim WbSh As String, sRef As String, sAtt As String
    sRef = Range("A1").Value
    sAtt = Range("F2").Value
    WbSh = "'D:\Team\Products\[Database.xlsm]Data'!"
    Range("G2").Formula = "=INDEX(" & WbSh & "$1:$1048576," & _
             "MATCH(""" & sRef & """," & WbSh & "$A:$A,0)," & _
             "MATCH(""" & sAtt & """," & WbSh & "$1:$1,0))"
  End If
End Sub