# Vlookup closed workbook

#### mukeshnic

##### New Member
I've written this function to calculate premium amount based on common premium table

Rich (BB code):
``````Function Company_Cont(Basic As Currency, Class As Integer, DOB As Date, Date_effective As Date) As Currency
On Error GoTo FuncFail:
Dim Entitled As Double
Dim Age_Group As Integer
Dim Discount As Double
Dim r As Range
Entitled = Entitlement(Basic)
Age_Group = App_Group(Date_effective, DOB)
Select Case Class
Case 1
Discount = 2 / 3
Case Else
Discount = 3 / 4
End Select

Base_Premium = Application.WorksheetFunction.VLookup(Entitled, r, Age_Group) * 0.355
Exit Function
FuncFail:

Company_Cont = 0
End Function``````

With this code I have to keep same Premium Table in each workbook . I want to keep a common workbook for premium table. How can I replace VLookup function so that I can Vlookup the table stored in a closed workbook. Suggestion for improvement also invited.

### 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.

#### ravishankar

##### Well-known Member
HI
Assuming your common book is premium.xls and table is in sheet1, try these modifications
Code:
``````Set r = workbooks("Premium.xls").Worksheets("Sheet1").Range("premium_Table")
VLookup(Entitled, r, Age_Group,false) * 0.355``````
Ravi

Replies
2
Views
438
Replies
4
Views
364
Replies
2
Views
147
Replies
0
Views
775
Replies
21
Views
660

1,190,855
Messages
5,983,238
Members
439,833
Latest member
CDaviess

### 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.

### Which adblocker are you using?

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

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