Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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) |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
Best to use VLOOKUP instead of lookup for this. Use your table
-- Don Guillett SalesAid Software "Susan" wrote in message ... 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) |
#3
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=535575 |
#4
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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 "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) |
#5
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
Bill- Thanks so much, the ascending order is the key. You have saved me hours!
"William Horton" wrote: 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 "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) |
#6
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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 ( 0A2<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 "Dav" wrote: 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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=535575 |
#7
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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) 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) |
#8
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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. "Susan" wrote: 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 ( 0A2<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 "Dav" wrote: 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 -- Dav ------------------------------------------------------------------------ Dav's Profile: http://www.excelforum.com/member.php...o&userid=27107 View this thread: http://www.excelforum.com/showthread...hreadid=535575 |
#9
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
"Harlan Grove" wrote: 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) 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 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 |
#10
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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 "Harlan Grove" wrote: 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) 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) |
#11
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
how can I make this work? =LOOKUP(A2,{any number},{5,6,7,10})
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 "Harlan Grove" wrote: 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) 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) |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
How can I make Excel Macros created on PC work on Macintosh? | Excel Worksheet Functions | |||
Spin button in a work sheet - how do I make it work? | Excel Worksheet Functions | |||
Make Autofit row height work with merged cells | Excel Discussion (Misc queries) | |||
How do I make 'save as web page' with interactivity work properly. | Charts and Charting in Excel | |||
make a cell empty based on condition | Charts and Charting in Excel |