software

I am trying to use a lookup formula to calculate the price, The dollar
amount could be from zero on up to $150.00. Can this be done as a lookup?
Thanks so much for any tips.
Regards, Susan

range price
0 $5
0.01-1.00 $6
1.01-2.00 $7
2.01-5.00 $10
5.01-7.00 $12
7.01-10.00 $15
up to
145.01-150.00 $155 (table contains 36 values)

Best to use VLOOKUP instead of lookup for this. Use your table

--
Don Guillett
SalesAid Software

quot;Susanquot; gt; wrote in message
...
gt;I am trying to use a lookup formula to calculate the price, The dollar
gt; amount could be from zero on up to $150.00. Can this be done as a lookup?
gt; Thanks so much for any tips.
gt; Regards, Susan
gt;
gt; range price
gt; 0 $5
gt; 0.01-1.00 $6
gt; 1.01-2.00 $7
gt; 2.01-5.00 $10
gt; 5.01-7.00 $12
gt; 7.01-10.00 $15
gt; up to
gt; 145.01-150.00 $155 (table contains 36 values)

if the startuing values are entered a vlookup should suffice, something
like

05
0.016
1.017
2.0110
5.0112
7.0115

If your cell value to lookup is in c1 something like the following
should work

vlookup(c1,$a$1:$b$36,2)

Regards

Dav--
Dav
------------------------------------------------------------------------
Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
View this thread: www.excelforum.com/showthread...hreadid=535575You would have to split your range up into 2 columns. Ex. Starting Range and
Ending Range. Assuming your Starting range is in column A, Ending range in
column B, and price in Column C the below formula will work IF THE RANGES ARE
SORTED IN ASCENDING ORDER.

=VLOOKUP(LOOKUP(E2,A2:A7),A2:C7,3,TRUE)

I only went down to row 7.
Hope this helps.

Bill Horton

quot;Susanquot; wrote:

gt; I am trying to use a lookup formula to calculate the price, The dollar
gt; amount could be from zero on up to $150.00. Can this be done as a lookup?
gt; Thanks so much for any tips.
gt; Regards, Susan
gt;
gt; range price
gt; 0 $5
gt; 0.01-1.00 $6
gt; 1.01-2.00 $7
gt; 2.01-5.00 $10
gt; 5.01-7.00 $12
gt; 7.01-10.00 $15
gt; up to
gt; 145.01-150.00 $155 (table contains 36 values)

Bill- Thanks so much, the ascending order is the key. You have saved me hours!

quot;William Hortonquot; wrote:

gt; You would have to split your range up into 2 columns. Ex. Starting Range and
gt; Ending Range. Assuming your Starting range is in column A, Ending range in
gt; column B, and price in Column C the below formula will work IF THE RANGES ARE
gt; SORTED IN ASCENDING ORDER.
gt;
gt; =VLOOKUP(LOOKUP(E2,A2:A7),A2:C7,3,TRUE)
gt;
gt; I only went down to row 7.
gt; Hope this helps.
gt;
gt; Bill Horton
gt;
gt; quot;Susanquot; wrote:
gt;
gt; gt; I am trying to use a lookup formula to calculate the price, The dollar
gt; gt; amount could be from zero on up to $150.00. Can this be done as a lookup?
gt; gt; Thanks so much for any tips.
gt; gt; Regards, Susan
gt; gt;
gt; gt; range price
gt; gt; 0 $5
gt; gt; 0.01-1.00 $6
gt; gt; 1.01-2.00 $7
gt; gt; 2.01-5.00 $10
gt; gt; 5.01-7.00 $12
gt; gt; 7.01-10.00 $15
gt; gt; up to
gt; gt; 145.01-150.00 $155 (table contains 36 values)

Hi-
I thought Bill had it solved, but I have approximately 600 prices amp; while I
can sort them in ascending order, I cannot match them to a 36 element table.
Here is a sample of how the prices may look. Where the cost falls in the
range determines the price charged. I tried using IF statements (
0gt;A2lt;1.01,6, ) but could only use 7 amp; I have 36 price ranges. Thanks again,
Susan
..1718
..7103
1.4386
..0461
..1069
..2766
..0289
..0391
..1517
..0230
..0849
1.1668
..2588
..1215
..0598
..0080
..1040
5.4230
quot;Davquot; wrote:

gt;
gt; if the startuing values are entered a vlookup should suffice, something
gt; like
gt;
gt; 05
gt; 0.016
gt; 1.017
gt; 2.0110
gt; 5.0112
gt; 7.0115
gt;
gt; If your cell value to lookup is in c1 something like the following
gt; should work
gt;
gt; vlookup(c1,$a$1:$b$36,2)
gt;
gt; Regards
gt;
gt; Dav
gt;
gt;
gt; --
gt; Dav
gt; ------------------------------------------------------------------------
gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; View this thread: www.excelforum.com/showthread...hreadid=535575
gt;
gt;

Susan wrote...
gt;I am trying to use a lookup formula to calculate the price, The dollar
gt;amount could be from zero on up to $150.00. Can this be done as a lookup?
gt;Thanks so much for any tips.
gt;Regards, Susan
gt;
gt;range price
gt;0 $5
gt;0.01-1.00 $6
gt;1.01-2.00 $7
gt;2.01-5.00 $10
gt;5.01-7.00 $12
gt;7.01-10.00 $15
gt;up to
gt;145.01-150.00 $155 (table contains 36 values)

If the table above were in A1:B37 with the column labels in A1:B1 and
the ranges as text in A2:A37, then you could use LOOKUP as long as the
low ends of the ranges are in ascending order.

=LOOKUP(x,--MID(A2:A37,1,FIND(quot;-quot;,A2:A37amp;quot;-quot;)-1),B2:B37)Hi,

Using the dav idea of just starting values, you could use an array formula
for this. Supposing the prices are in column D, and the list with the
starting values and the prices goes from A1 to B36, you could use this array
formula in cell E1:
=MAX(IF(D1gt;$A$1:$A$36,$A$1:$A$36))
Remember to enter it with Ctrl Shift Enter. Enter it on the first cell and
then copy and paste to the others in the column.
You can after that use a Vlookup formula in column F
=VLOOKUP(E1,$A$1:$B$36,2)

Miguel.

quot;Susanquot; wrote:

gt; Hi-
gt; I thought Bill had it solved, but I have approximately 600 prices amp; while I
gt; can sort them in ascending order, I cannot match them to a 36 element table.
gt; Here is a sample of how the prices may look. Where the cost falls in the
gt; range determines the price charged. I tried using IF statements (
gt; 0gt;A2lt;1.01,6, ) but could only use 7 amp; I have 36 price ranges. Thanks again,
gt; Susan
gt; .1718
gt; .7103
gt; 1.4386
gt; .0461
gt; .1069
gt; .2766
gt; .0289
gt; .0391
gt; .1517
gt; .0230
gt; .0849
gt; 1.1668
gt; .2588
gt; .1215
gt; .0598
gt; .0080
gt; .1040
gt; 5.4230
gt;
gt;
gt;
gt; quot;Davquot; wrote:
gt;
gt; gt;
gt; gt; if the startuing values are entered a vlookup should suffice, something
gt; gt; like
gt; gt;
gt; gt; 05
gt; gt; 0.016
gt; gt; 1.017
gt; gt; 2.0110
gt; gt; 5.0112
gt; gt; 7.0115
gt; gt;
gt; gt; If your cell value to lookup is in c1 something like the following
gt; gt; should work
gt; gt;
gt; gt; vlookup(c1,$a$1:$b$36,2)
gt; gt;
gt; gt; Regards
gt; gt;
gt; gt; Dav
gt; gt;
gt; gt;
gt; gt; --
gt; gt; Dav
gt; gt; ------------------------------------------------------------------------
gt; gt; Dav's Profile: www.excelforum.com/member.php...oamp;userid=27107
gt; gt; View this thread: www.excelforum.com/showthread...hreadid=535575
gt; gt;
gt; gt;



quot;Harlan Grovequot; wrote:

gt; Susan wrote...
gt; gt;I am trying to use a lookup formula to calculate the price, The dollar
gt; gt;amount could be from zero on up to $150.00. Can this be done as a lookup?
gt; gt;Thanks so much for any tips.
gt; gt;Regards, Susan
gt; gt;
gt; gt;range price
gt; gt;0 $5
gt; gt;0.01-1.00 $6
gt; gt;1.01-2.00 $7
gt; gt;2.01-5.00 $10
gt; gt;5.01-7.00 $12
gt; gt;7.01-10.00 $15
gt; gt;up to
gt; gt;145.01-150.00 $155 (table contains 36 values)
gt;
gt; If the table above were in A1:B37 with the column labels in A1:B1 and
gt; the ranges as text in A2:A37, then you could use LOOKUP as long as the
gt; low ends of the ranges are in ascending order.
gt;
gt; =LOOKUP(x,--MID(A2:A37,1,FIND(quot;-quot;,A2:A37amp;quot;-quot;)-1),B2:B37)
gt;
gt; Thanks Harlan

A B C
range price
0 $51.4386
..0461
..1069
..2766
..0289
..0391
..1517
0.01-1.00 $6
1.01-2.00 $7
2.01-5.00 $10
5.01-7.00 $12
7.01-10.00 $15Hi Harlan
Sorry for the last post, wasn't finished

0 $5
0.01-1.00 $6
1.01-2.00 $7
2.01-5.00 $10
5.01-7.00 $12
7.01-10.00 $15
quot;Harlan Grovequot; wrote:

gt; Susan wrote...
gt; gt;I am trying to use a lookup formula to calculate the price, The dollar
gt; gt;amount could be from zero on up to $150.00. Can this be done as a lookup?
gt; gt;Thanks so much for any tips.
gt; gt;Regards, Susan
gt; gt;
gt; gt;range price
gt; gt;0 $5
gt; gt;0.01-1.00 $6
gt; gt;1.01-2.00 $7
gt; gt;2.01-5.00 $10
gt; gt;5.01-7.00 $12
gt; gt;7.01-10.00 $15
gt; gt;up to
gt; gt;145.01-150.00 $155 (table contains 36 values)
gt;
gt; If the table above were in A1:B37 with the column labels in A1:B1 and
gt; the ranges as text in A2:A37, then you could use LOOKUP as long as the
gt; low ends of the ranges are in ascending order.
gt;
gt; =LOOKUP(x,--MID(A2:A37,1,FIND(quot;-quot;,A2:A37amp;quot;-quot;)-1),B2:B37)
gt;
gt;