how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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)
 
if the startuing values are entered a vlookup should suffice, something
like

0 5
0.01 6
1.01 7
2.01 10
5.01 12
7.01 15

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

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

Regards

Dav
 
You 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
 
Hi-
I thought Bill had it solved, but I have approximately 600 prices & 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 (
0>A2<1.01,6, ) but could only use 7 & 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
 
Susan wrote...
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)
Click to expand...

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("-",A2:A37&"-")-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(D1>$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.
 
Harlan Grove said:
Susan wrote...

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("-",A2:A37&"-")-1),B2:B37)

Thanks Harlan
Click to expand...

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 $15
 
Hi 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
 
I give up! I am trying to compose & inadvertently am posting...time to go
home & sleep over this puzzle

Column A will have the price ranges, column B will have the fixed prices.
I'd like to drop in the costs from our vendor into column C and in column D,
have the formula applied from the 36 element table. I want to either hide
this table or keep it on a second sheet, the second sheet is my preference
since I want to have 2 individual price tables of about 600 items each.
I am only semi-literate w/ Excel, & hence these next questions: does x
refer to the cell with my vendor cost, in this case C? & The 2 statements of
"-", what am I asking here? or should I substitute something for those value?
My final sheet should read acq cost = 0.83, price=$6.00.
I really appreciate your help with this.
Susan
 
Back
Top