VLOOKUP via UDF?

dplum

Board Regular
Joined
Nov 1, 2006
Messages
217
I'm wanting to simplify the creaion of a "lookup" by creating a UDF to do a VLOOKUP.

The only thing the user needs to supply is teh Lookup Value. Everything else is "hard coded".

Also, Range Names are assigned to the Table Array area and also a cell that is located in teh Col_index_num column.

Here's the code, but is doesn't work -- it returns a #VALUE error.

Code:
Function Page_Commission_Rate(Lookup_Value)
    'Worksheet Names are used to assign values to two variables that follow
    vTable_Array_Name = ['] & ThisWorkbook.Name & ['!] & "Table_Array"
    vCol_Index_CellName = ['] & ThisWorkbook.Name & ['!] & "Lookup_Commission"
    vCol_Index_Num = Range(vCol_Index_CellName).Column

    Page_Commission_Rate = WorksheetFunction.VLookup(Lookup_Value, _
                                                vTable_Array_Name, _
                                                vCol_Index_Num, False)
End Function

Help on what's needed to make this work is appreciated.
 

Excel Facts

Excel Can Read to You
Customize Quick Access Toolbar. From All Commands, add Speak Cells or Speak Cells on Enter to QAT. Select cells. Press Speak Cells.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
If you don't pass all the arguments the function needs, Excel will see no dependency between the contents of the lookup table and the cell your formula is in, and not recalculate when it changes.
 

dplum

Board Regular
Joined
Nov 1, 2006
Messages
217
Sorry... I'm not understnading what I may need to change.

As far as I know I am pAssing all of the vlookup arguments -- as shown in...

Page_Commission_Rate = WorksheetFunction.VLookup(Lookup_Value, _
vTable_Array_Name, _
vCol_Index_Num, False)
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
All the function receives as an argument is the lookup value -- not the table, not the column index.
 

dplum

Board Regular
Joined
Nov 1, 2006
Messages
217
But the Table and Column Index are specified as variables within the UDF. And the VALUE error is given from the initial insert of the UDF, not just on a recalc.
 

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,833
Office Version
  1. 2010
Platform
  1. Windows
None of that gives Excel a whit of a hint that the function is dependent on the values in the table or the column index.
 
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,164,305
Messages
5,836,539
Members
430,436
Latest member
Emilycr

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