Finding LARGE value within range of lookup table
 

I have a table about 3,000 rows and two colums. Column A is a range of dates,
column B is a numeric value. I want to be able to look up multiple date
ranges within this table and be able to find the Starting and Ending values
as well as the LARGE and SMALL values within the specified date range.

Example of Table:
01/01/04 100.357
01/02/04 100.25
01/03/04 100.00
01/04/04 100.68
..
..
..
05/31/05 101.00

The other database that I am using supplies the starting and ending dates so
I am able to pull those values easily, but how do I isolate the data within
random periods within the list assign a LARGE and SMALL value.

Start Date End Date Begin End Large Small
01/31/04 02/15/04 100.23 100.45 ? ?

If your start date is in cell D6, for example, then large (in H6) would be:
=MAX(INDIRECT("B"&MATCH(D6,A1:A200,0)&":B"&MATCH(E 6,A1:A200,0)))
and small would be
=MIN(INDIRECT("B"&MATCH(D6,A1:A200,0)&":B"&MATCH(E 6,A1:A200,0)))

"WPA" wrote:

I have a table about 3,000 rows and two colums. Column A is a range of dates,
column B is a numeric value. I want to be able to look up multiple date
ranges within this table and be able to find the Starting and Ending values
as well as the LARGE and SMALL values within the specified date range.

Example of Table:
01/01/04 100.357
01/02/04 100.25
01/03/04 100.00
01/04/04 100.68
.
.
.
05/31/05 101.00

The other database that I am using supplies the starting and ending dates so
I am able to pull those values easily, but how do I isolate the data within
random periods within the list assign a LARGE and SMALL value.

Start Date End Date Begin End Large Small
01/31/04 02/15/04 100.23 100.45 ? ?

Hi!

With TRUE dates in A1:A3000 and D2:E2 .....

............D..................E...............F.. ...........G............H.............I
1 Start Date End Date Begin End Large Small
2 01/31/04 02/15/04 100.23 100.45 ? ?

Formula in H2 for Large:

=MAX(IF($A$1:$A$3000=D2,IF($A$1:$A$3000<=E2,$B$1: $B$3000)))

Formula in I2 form small:

=MIN(IF($A$1:$A$3000=D2,IF($A$1:$A$3000<=E2,$B$1: $B$3000)))

Both formulas are array formulas and need to be entered with the key combo
of CTRL,SHIFT,ENTER.

Biff

"WPA" wrote in message
...
I have a table about 3,000 rows and two colums. Column A is a range of
dates,
column B is a numeric value. I want to be able to look up multiple date
ranges within this table and be able to find the Starting and Ending
values
as well as the LARGE and SMALL values within the specified date range.

Example of Table:
01/01/04 100.357
01/02/04 100.25
01/03/04 100.00
01/04/04 100.68
.
.
.
05/31/05 101.00

The other database that I am using supplies the starting and ending dates
so
I am able to pull those values easily, but how do I isolate the data
within
random periods within the list assign a LARGE and SMALL value.

Start Date End Date Begin End Large Small
01/31/04 02/15/04 100.23 100.45 ? ?