Home |
Search |
Today's Posts |
#1
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up Values Within a Range
I have a spreadsheet that looks as follows:
A B C 1 Number Of Days 5 4 2 11/1/2005 11/8/2005 3 11/7/2005 11/14/2005 4 A###,Brian 75.14 50.83 5 A####,Terrance 0.00 0.00 6 B####,Casey 21.32 21.72 7 B###,Jason 82.73 68.99 In another worksheet I will have a table that will look like this: A B C D E F 1 Days 4 5 2 <13 28 <16 35 3 13-19 48 16-24 60 4 20-27 72 25-34 90 5 28-37 96 35-46 120 6 38-42 120 47-53 150 7 43-50 18.74 54-62 230 8 51-55 212.26 63-69 265 9 56-64 237.6 70-80 267 10 65-72 306 81-90 383 11 73-80 342 91-100 428 12 81-88 378 101-110 473 13 89-96 414 111-120 518 14 97-104 450 121-130 563 15 104 486 131 608 In column D of the first work sheet I want to write a formula that will look at Cells B1 and B4. Since the B1's value is 5 and B4's value is 75.14 I want the formula to go to the second worksheet and return the value in Cell E9. Because the contents of Column D in the second worksheet are ranges of days I don't know how to make this work. Any thoughts? |
#2
Posted to microsoft.public.excel.worksheet.functions
|
|||
|
|||
Look Up Values Within a Range
Here's a crack at it, Mike ..
See sample construct at: http://cjoint.com/?lsiNEDFvMY LookUpValuesWithin_a_Range_Mike_wks.xls With Sheet2 reconfigured as shown in the sample, essentially: B2:B15 housing the tier limits for "4" days, D2:D15 housing the tier limits for "5" days In Sheet1 ----------- Put in D4: =INDEX(Sheet2!$E$1:$E$15, MATCH(B4,OFFSET(Sheet2!$A$1:$A$15,,MATCH(B$1,Sheet 2!$1:$1,0)-2),1)) Put in E4: =INDEX(Sheet2!$C$1:$C$15, MATCH(C4,OFFSET(Sheet2!$A$1:$A$15,,MATCH(C$1,Sheet 2!$1:$1,0)-2),1)) Select D4:E4, fill down -- Rgds Max xl 97 --- Singapore, GMT+8 xdemechanik http://savefile.com/projects/236895 -- "Mike" wrote in message ... I have a spreadsheet that looks as follows: A B C 1 Number Of Days 5 4 2 11/1/2005 11/8/2005 3 11/7/2005 11/14/2005 4 A###,Brian 75.14 50.83 5 A####,Terrance 0.00 0.00 6 B####,Casey 21.32 21.72 7 B###,Jason 82.73 68.99 In another worksheet I will have a table that will look like this: A B C D E F 1 Days 4 5 2 <13 28 <16 35 3 13-19 48 16-24 60 4 20-27 72 25-34 90 5 28-37 96 35-46 120 6 38-42 120 47-53 150 7 43-50 18.74 54-62 230 8 51-55 212.26 63-69 265 9 56-64 237.6 70-80 267 10 65-72 306 81-90 383 11 73-80 342 91-100 428 12 81-88 378 101-110 473 13 89-96 414 111-120 518 14 97-104 450 121-130 563 15 104 486 131 608 In column D of the first work sheet I want to write a formula that will look at Cells B1 and B4. Since the B1's value is 5 and B4's value is 75.14 I want the formula to go to the second worksheet and return the value in Cell E9. Because the contents of Column D in the second worksheet are ranges of days I don't know how to make this work. Any thoughts? |
Reply |
Thread Tools | Search this Thread |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Forum | |||
Last 2 values in range | Excel Discussion (Misc queries) | |||
How many values appear more than once in a range? | Excel Worksheet Functions | |||
Checking ALL values in a range | Excel Discussion (Misc queries) | |||
How to move Y-axis values when X range is -a to +b | Charts and Charting in Excel | |||
Can you average data in 1 column based on a range of values in another? | Excel Worksheet Functions |