VLOOKUP help

  • Thread starter Thread starter DK1314
  • Start date Start date
D

DK1314

We have several maintenance packages that we sell and we need to know
what we will be paid on a recurring basis.

I've created a Validation table to give me a drop down and beside it I
have placed a number of values relating to the different terms and
packages available. For example, if a customer takes a standard care
package on a three year term, I know the company will only realise
revenue for the 2nd and 3rd year so I want to enter a value for the
whole term and use LOOKUP to determine the package selected and then
the relative calculation.(3 year standard care contract value
multiplied by.6666667 will give me our achieveable revenue).

I have listed the options and the multiplying factors side by side on a
separate sheet and have named the ranges above. But when I try and
layout the VLOOKUP formula it gives me a name error.

Can anyone help?
 
could you give us a small example of maybe 5 rows,
your range names
and of course your formula
 
TERM[/B] CALC
Std 1 Yr 0
Std 3 Yr 0.6666667
Std 5 Yr 0.8
Pmpt 1 Yr 0.7143
Pmpt 3 Yr 0.2381
Pmpt 5 Yr 0.14286
Total 1 Yr 0.5
Total 3 Yr 0.1666667
Total 5 Yr 0.1

Above are the ranges named on a separate sheet.

A B C D
1 Std 1 Value Result
2
3

In the main sheet, in A1 would be a drop down box that I want to
multiply with the associated value in the Calc column. B1 will be a
value that is populated to give a total value in C1.

I'm probably that far away with the VLOOKUP formula I don't want to
confuse matters by displaying it.

Thanks for your prompt response.
 
ok I am not sure what this is, and I am going to assume that no cells
are merged so maybe this will help:
On one sheet (Sheet1)you have:
Column A is the list (as well as the drop down list)
Column B is a value (this is what you want to show up beside your
selection)

The next Sheet(Sheet2) you will have Cell A1 as the dropdown menu
Cell B1 will be the value that you will be multiplying to another Cell
Cell C1 will be the Product from that
I am going to assume you did the data validation properly or else it
wouldn't work
In Sheet1 highlite the total range of the two columns
goto to your top menu and select insert=>names=>define
type a one word name in there such as Data
Now that range is named

Now highlite the values in column A only goto insert,name,define lets
name that range:
Years

Now goto sheet2 cell A1
select data in the menu and goto validation, in the dropdown menu
select list, in the source box type this =Years

In B1 enter this formula
=Lookup(A1,Data)

There you go, now make a pick from your drop down list, you should get
your value in B1
In C1 you will have your formula =B1*'wherever

I can't stay to check you status, I have to go, I will check later
tonight to see how you have done
 
Thanks for this, I know where I haven't explained myself properly now.

In sheet 2, A1 is the dropdown which has been achieved by Validation.

A2 will be a variable contract value. This will be manually entere
line on line.

A3 Would be the result.

So what I want to do is pick an option from the drop down, enter an
number in A2 and then have the result of A2*whatever option is picke
from A1 dsiplayed in A3.

So if we picked STD 5 Yr, we want whatever we enter in A2 to b
multiplied by 0.8.

Hope this makes sense
 
So what I want to do is pick an option from the drop down, enter any
number in A2 and then have the result of A2*whatever option is picked
from A1 dsiplayed in A3.

So if we picked STD 5 Yr, we want whatever we enter in A2 to be
multiplied by 0.8.
 
In sheet 2, A1 is the dropdown which has been achieved by Validation.
A2 will be a variable contract value. This will be manually entered
line on line. A3 would be the result.
So what I want to do is pick an option from the drop down, enter any
number in A2 and then have the result of A2*whatever option is picked
from A1 displayed in A3.
So if we picked STD 5 Yr, we want whatever we enter in A2 to be
multiplied by 0.8.
Click to expand...

Assuming the reference packages / pricing
are listed in Sheet1's cols A and B

In Sheet2,

Put in A3:
=IF(OR(A1="",A2=""),"",VLOOKUP(A1,Sheet1!$A:$B,2,0)*A2)

As-is, the formula in A3 above can be copied across to return
correspondingly
for other DV selections/inputs in B1:B2, C1:C2, etc

A sample construct for the above is available at:
http://cjoint.com/?dkhZjed4ed
DK1314_newusers.xls

---
 
Back
Top