lookup problem

  • Thread starter Thread starter Colin
  • Start date Start date
C

Colin

Hello

Could someone with better excel knowledge than me tell
when I have a list of data in one column and dates on the
left column, I want to be able to enter a start date in
one cell and an end date in another, I want to be able in
a diffrent sheet to be able to bring up all the data
between these two Dates.

Thanks

Colin
 
Hi,

Try =SUMPRODUCT(((A3:A13)>=D6)*((A3:A13)<=E6)*(B3:B13))

A is the column where the dates and B is the column where you have values

Start date is D6 and End date is E6

Regards

Govind.
 
Hi
the easies way would be to use 'Data - Filter - advanced Filter' for a
one-time / non-dynamic solution. for a formula approach try the
following:
1. Your sheet 1 contains in column a your dates and in column B your
data. Row 1 is a heading row

2. in sheet 2 you enter in A1 the starting date and in B1 the ending
date

3. Now enter the following array formula (entered with
CTRL+SHIFT+ENTER) in cell A2:
=INDEX('sheet1'!$A$1:$A$100,SMALL(IF(('sheet1'!$A$2:$A$100>=$A$1)*('she
et1'!$A$2:$A$100<=$B$1),ROW('sheet1'!$A$2:$A$100)),ROW(1:1)))

and in B2 enter (also as array formula):
=INDEX('sheet1'!$B$1:$B$100,SMALL(IF(('sheet1'!$A$2:$A$100>=$A$1)*('she
et1'!$A$2:$A$100<=$B$1),ROW('sheet1'!$A$2:$A$100)),ROW(1:1)))


copy both formulas down as far as needed
 
One option is to invoke Advanced Filter from within the destination sheet
where you set up a criteria range. Another option is to use an efficient
(non-array) formula system.
 
Back
Top