substitute indirect(reference) with a UDF (for speed)

[tzenekkik]

I want to use a VLOOKUP where the second argument (beiing the table_array to examine) is the result of a UDF (user defined function). I can't get this to work because my UDF returns a string and not a range.

My UDF looks like:
Function RANGENAME(namedRANGEinAcell As String) As Range
RANGENAME = namedRANGEinAcell
End Function

The reason why I'm doing this is to avoid the use of the volatile function indirect() in the VLOOKUP.
By substituting
VLOOKUP(lookup_value, indirect(referenced cell with the named range as value);col_index_num)
with
VLOOKUP(lookup_value;RANGENAME(referenced cell with the named range as value);col_index_num)
I figure I do it without the volatility (to be used in 11000 records and over more than 15 columns makes the use of volatile functions a pain in the ***)

Any help would be very much appreciated.
ray:

 

[Aladin Akyurek]

It's doubtful whether VBA will relieve the pain...

Some options are:

INDIRECT.EXT of the morefunc.xll add-in and CHOOSE if you have a few tables.

Also, take care the VLOOKUP formula does suffer from computing the same thing twice in order to avoid #N/A.

 

[BrianB]

I think something like this is what you are looking for :-

'-----------------------------------------
Function MyLookup(MyVal As Variant, Rname As String)
MyLookup = Application.WorksheetFunction. _
VLookup(MyVal, ActiveSheet.Range(Rname), 2, False)
End Function
'-------------------------------------------

so in a worksheet you would use something like :-
=mylookup(A1,B1)
where A1 contains the value and B1 contains the range name.

 

[tzenekkik]

well, INDIRECT.EXT of the morefunc.xll add-in accepts only the full address of the cell in A1 style, so no named ranges (working on a workaround now)
the choose option is probably not fitting because I have about 15 tables to do a lookup on. The solution provided by BrianB works, but the UDF behaves like it's a volatiel function (I don't know why)
Anyway, thanks a lot for your help, both of you!

Thanks ray: