IF or vlookup formula

[Rob]

I am trying to return the values of column Ain column C if the dates in
column B are between 31/12/2008 and 01/01/2010

Column A Column B Column C
1040 24/12/2008 1042
1042 03/01/2009 1041
1041 21/06/2009 1043
1043 21/12/2009
1045 03/01/2010

Have tried =IF(AND(B:B>31/12/2008,B:B<01/01/2010),A:A,0)

 

[Jacob Skaria]

In cell A1 and copy down as required
=IF(AND(B1>DATE(2008,12,31),B1<DATE(2010,1,1)),A1,0)

If this post helps click Yes

 

[Rob]

This has worked fine, but is their any way i can just have the values where
this argument is applicable instead of 0 when it doesn't

 

[Eduardo]

Hi,
use

=IF(AND(B1>DATE(2008,12,31),B1<DATE(2010,1,1)),A1,"")

 

[Rob]

Thanks Eduardo, but i reraly need to see just the value not a blank. In the
example the first value in Column C is 1042 i.e the first value that meets
the criteria and excludes 1040 which is outside this range. Is their any way
to automatically sort the data ?

 

[Max]

An easy formulas play which can deliver it for you ..
Source data as posted running in A1:B1 down
In C1: =IF(AND(B1>DATE(2008,12,31),B1<DATE(2010,1,1)),ROW(),"")
In D1: =IF(ROW()>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW())))
Copy C11 down to cover the max expected extent of source data.
Hide/minimize col C. Col D will return the required results, all neatly
bunched at the top
Any good? hit the YES below
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Rob]

Thank you all for your answers they have all worked fine. One more questiuon
though using the formula that Max wrote, how can I change it to read row 2. I
have inserted a header in row 1 and therefore miss the first line of data
when i use the formula in D2 instead of D1

 

[Max]

Just replace ROW() with ROWS($1:1),
viz use in D2, copied down:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik

 

[Rob]

Working fine now. thank you all.

Just replace ROW() with ROWS($1:1),
viz use in D2, copied down:
=IF(ROWS($1:1)>COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROWS($1:1))))
--
Max
Singapore
http://savefile.com/projects/236895
Downloads:27,000 Files:200 Subscribers:70
xdemechanik