0

I'm trying to build a simple addin for excel which does a specific xlookup when called. As a formula it looks like this

=XLOOKUP(C2,'[MasterAssetTypeTaxonomy.xlsx]Master Taxonomy'!$A:$A,'[MasterAssetTypeTaxonomy.xlsx]Master Taxonomy'!$E:$E,"_NF")

which works fine so I thought my add-in would therefor look like this

Public Function getDescription(ByVal pRange As Range) As String   
    getDescription = WorksheetFunction.XLookup(pRange.text, "'[C:\MasterAssetTypeTaxonomy.xlsx]Master Taxonomy'!$A:$A", "'[C:\MasterAssetTypeTaxonomy.xlsx]Master Taxonomy'!$E:$E", "_NF")    
End Function

There are no errors but it always returns the not found options "_NF" The data to match is there so I think its something to do with how I am referencing the search and results columns. Any ideas?

CA_Netsmith
  • 13
  • 3
  • Just some extra info I should add this bit of code lives in an Add-in file and the files referenced are external to the file that is calling the function and closed. Im basically trying to set up a 'hard coded' xlookup, which I only need to pass my search term to. – CA_Netsmith Sep 15 '22 at 12:51
  • Been trying to find a solution, but the results are unclear. Couple of avenues of investigation so far...Could this issue be that it is because the file reference is within a Function rather than a SUB? Or perhaps because the file that is being used for the lookup isn't open? (I don't really want it to be if possible) – CA_Netsmith Sep 16 '22 at 07:37

0 Answers0