VLOOKUP via UDF?

[dplum]

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.

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.

 

[shg]

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]

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]

All the function receives as an argument is the lookup value -- not the table, not the column index.

 

[dplum]

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]

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.