Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Mike
 
Posts: n/a
Default 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   Report Post  
Posted to microsoft.public.excel.worksheet.functions
Max
 
Posts: n/a
Default 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
Search this Thread:

Advanced Search
Display Modes

Posting Rules

Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On


Similar Threads
Thread Thread Starter Forum Replies Last Post
Last 2 values in range JL Excel Discussion (Misc queries) 2 April 11th 05 06:22 PM
How many values appear more than once in a range? Bruce Norris Excel Worksheet Functions 12 April 5th 05 12:44 PM
Checking ALL values in a range nospaminlich Excel Discussion (Misc queries) 13 February 10th 05 09:29 AM
How to move Y-axis values when X range is -a to +b [email protected] Charts and Charting in Excel 4 January 31st 05 11:54 PM
Can you average data in 1 column based on a range of values in another? kman24 Excel Worksheet Functions 2 November 17th 04 02:09 PM


All times are GMT +1. The time now is 05:00 PM.

Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 ExcelBanter.
The comments are property of their posters.
 

About Us

"It's about Microsoft Excel"