Return a Value between dates based on another Value

[casey]

Sample data:

ColA ColB ColC
Row1 22 1/16/2009 55
Row2 23 1/18/2009 17
Row3 22 1/20/2009 33
Row4 25 1/23/2009 9

I have several thousand rows with every column a named range, i.e. rng1,
rng2, etc.

Example: I want to return '55' and '33' from ColC, based on '22' from ColA
between the dates 1/1/2009 and 1/31/2009 (ColB).

Thx,
casey

 

[T. Valko]

I have several thousand rows

Any formula solution for that many rows will be slow to calculate. Have you
considered using a filter?

 

[casey]

I do use the filter for ad hoc analysis. However, I want to 'list' the
answer (plus many others) to another area for static charts using dynamic
data. The extra time is ok with me as I have many reports that run
weekly/monthly using this data already.

thx,
casey

 

[T. Valko]

Try this...

Data in the range A1:C4.

A1:A4 = named range Rng1
B1:B4 = named range Rng2
C1:C4 = named range Rng3

Enter these column headers:

E1 = Lookup
F1 = From
G1 = To
H1 = Total Records
I1 = Results

E2:G2 are pretty much self-explanatory.

E2 = lookup value 22
F2 = start date 1/1/2009
G2 = end date 1/31/2009

Enter this formula in H2. This will return the count of records that meet
the criteria:

=SUMPRODUCT(--(Rng1=E2),--(Rng2>=F2),--(Rng2<=G2))

Enter this array formula** in I2. This will extract those records.

=IF(ROWS(I$2:I2)>H$2,"",INDEX(Rng3,SMALL(IF((Rng1=E$2)*(Rng2>=F$2)*(Rng2<=G$2),ROW(Rng3)),ROWS(I$2:I2))-MIN(ROW(Rng3))+1))

** array formulas need to be entered using the key combination of
CTRL,SHIFT,ENTER (not just ENTER). Hold down both the CTRL key and the SHIFT
key then hit ENTER.

Copy the formula in I2 down until you get blanks meaning all records have
been extracted. Depending on how many rows you copy the array formula to,
this could take several seconds to calculate.