using vlookup in a custom function

[slug]

I have a database style spreadsheet that I use a lot and is stored on
our intranet.
I use a vlookup to find information about products in a database.
I look up the unique product name then choose the column that matches
a specific fieldname text.
It looks something like this:
=VLOOKUP(A2,ExternalRange,MATCH(F1,ExternalFieldname,0),FALSE)

There are only two changing variables in this formula so I've tried to
create a custom function that already has the external database and
fieldname range built in, but to no avail. Anyone suggest help?

I guess that the custom function would only need to know the A2 and
the F1 bit in the example above.

 

[merjet]

Try this. You may change Variant to something more specific.


Function MyLookUp(arg1 As Range, arg2 As Range) As Variant
MyLookUp = Application.VLookup(arg1, Range("ExternalRange"),
_ Application.Match(arg2, Range("ExternalFieldname"),
0), False)
End Function

Hth,
Merjet

 

[slug]

Thanks Merjet for your help. I've been struggling with this for a
while
,
I've tried this and am having trouble defining the external range. The
code below returns #VALUE! I wonder if there is something obvious that
I'm doing wrong?

Function MyLookUp(Acronym As Range, Fieldname As Range) As Variant
Dim R21Data As Range
Dim R21Fields As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open(Filename:= _
"http://nonsolus/GlobalReports/public/yesterday/Report21.xls")
Set R21Data = wbk.Worksheets("Data").Range("$A$7:$DV$1954") 'Data
Range in External Workbook
Set R21Fields = wbk.Worksheets("Data").Range("$A$7:$DV$7") 'Header
Range in External Workbook
MyLookUp = Application.VLookup(Acronym, R21Data,
Application.Match(R21Fields, Range("ExternalFieldname"), 0), False)
End Function

 

[slug]

Thanks for the advice. I'm now struggling to understand how to define
the external range. So far I have this but the result is "#VALUE!"

Function MyLookUp(Acronym As Range, Fieldname As Range) As Variant
Dim R21Data As Range
Dim R21Fields As Range
Dim wbk As Workbook
Set wbk = Workbooks.Open(Filename:= _
"http://intranet/yesterday/Report21.xls")
Set R21Data = wbk.Worksheets("Data").Range("$A$7:$DV$1954")
Set R21Fields = wbk.Worksheets("Data").Range("$A$7:$DV$7")
MyLookUp = Application.VLookup(Acronym, R21Data,
Application.Match(Fieldname, R21Fields, 0), False)
End Function