VLOOKUP via UDF?

dplum

Board Regular
Joined
Nov 1, 2006
Messages
216
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.
 

Some videos you may like

Excel Facts

What do {} around a formula in the formula bar mean?
{Formula} means the formula was entered using Ctrl+Shift+Enter signifying an old-style array formula.

shg

MrExcel MVP
Joined
May 7, 2008
Messages
21,770
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
216
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,770
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
216
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,770
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.
 

Watch MrExcel Video

Forum statistics

Threads
1,109,331
Messages
5,528,056
Members
409,799
Latest member
camronmartin

This Week's Hot Topics

Top