Using xlookup inside an excel add-in

Question

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?

Using xlookup inside an excel add-in

0 Answers