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

tzenekkik

New Member
Joined
Jun 3, 2002
Messages
39
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.
:pray:
 

Excel Facts

Is there a shortcut key for strikethrough?
Ctrl+S is used for Save. Ctrl+5 is used for Strikethrough. Why Ctrl+5? When you use hashmarks to count |||| is 4, strike through to mean 5.

Aladin Akyurek

MrExcel MVP
Joined
Feb 14, 2002
Messages
85,209
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

Well-known Member
Joined
Feb 17, 2003
Messages
8,127
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

New Member
Joined
Jun 3, 2002
Messages
39
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 :pray:
 
Master Excel Bundle

Excel contains over 450 functions, with more added every year. That’s a huge number, so where should you start? Right here with this bundle.

Forum statistics

Threads
1,152,161
Messages
5,768,547
Members
425,481
Latest member
ihumanl

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Top