Look Up Values Within a Range

[Guest]

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?

 

[Max]

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,
D215 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,Sheet2!$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,Sheet2!$1:$1,0)-2),1))

Select D4:E4, fill down