Using lookup function - HELP!!

[Guest]

I am trying to use the lookup function to display the price of a widget with
a given volume that I will use as an input cell. The issue the volume is a
range using two cells. See below


Volume Price
- 10,000 3.00
10,000 - 50,000 2.00
50,001 - 100,000 1.00

How do I setup a lookup formula to accomplish this? Keep in mind the ranges
of volume per price are in two separate cells. So 0 and 10,000 have their
own cell.

 

[Per Erik Midtrød]

IF the given volume is in A12 and your table is in
A1:C4 then the following formula should work:
= SUMPRODUCT((A2:A4<=A12)*(B2:B4>=A12)*(C2:C4))

Just keep in mind that I assumed there is a typo, since 10.000 is in
two ranges. If it is not and someone was to buy exactly 10.000 the
formula will return 5...

Best regards
Per Erik

 

[Ragdyer]

With you volume entered in A1, try this:

=LOOKUP(A1,{0,1,10001,50001;0,3,2,1})

 

[Guest]

it should be 10,001 in line two.

I do not need to calculate the revenue just need to bring up the correct
price based on the volume.

So would I just get rid of the multiplication?

 

[Per Erik Midtrød]

No, just enter the formula as it is.
And if you enter 15.000 in A12 the result will be 2.

Per Erik

 

[Per Erik Midtrød]

That was brilliant, much better than my solution!

Per Erik

 

[Guest]

Ragdyer I do not understand your formula? Can't I just put in the cell
reference of the values?

If you used my example starting in A1, I want to be able to type the volume
(lets say this is in A12) and what will pop out is the price.

So if I type in cell a12 40,000 units my price will be $2.00. The issue is
as well I have multiple price points so column be is for 1 year price, if you
add in an additional column for year 2 price which would be different that
column b

Year 1 Price Year 2 Price

0 - 10,000 $3.00 $2.50
10,001 - 50,000 $2.00 $1.50
50,001 - $1.00 $0.50

So my table where this will formula will go looks like this

Year 1 Price Year 2 Price

and I have the expected volume in a different cell that will be where the
user will input the volume.

 

[Guest]

eric, what if the last band has no end range so:

50,001 -

Thanks for you help!

 

[RagDyeR]

Year 1 Price volume entered in A12:
=LOOKUP(A12,{0,1,10001,50001;0,3,2,1})

Year 2 Price volume entered in B12:
=LOOKUP(B12,{0,1,10001,50001;0,2.5,1.5,0.5})


If you insist on a datalist, for future additions, you could try this:
In X1 to Z4, enter this:

X Y Z
1 0 0 0
2 1 3 2.5
3 10001 2 1.5
4 50001 1 0.5

Then, use this formula for Year 1 Pricing:
=VLOOKUP(A12,X1:Z4,2)

And use this formula for Year 2 Pricing:
=VLOOKUP(B12,X1:Z4,3)

--

HTH,

RD
=====================================================
Please keep all correspondence within the Group, so all may benefit!
=====================================================

Ragdyer I do not understand your formula? Can't I just put in the cell
reference of the values?

If you used my example starting in A1, I want to be able to type the volume
(lets say this is in A12) and what will pop out is the price.

So if I type in cell a12 40,000 units my price will be $2.00. The issue is
as well I have multiple price points so column be is for 1 year price, if
you
add in an additional column for year 2 price which would be different that
column b

Year 1 Price Year 2 Price

0 - 10,000 $3.00 $2.50
10,001 - 50,000 $2.00 $1.50
50,001 - $1.00 $0.50

So my table where this will formula will go looks like this

Year 1 Price Year 2 Price

and I have the expected volume in a different cell that will be where the
user will input the volume.