IF or vlookup formula
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:B31/12/2008,B:B<01/01/2010),A:A,0) |
IF or vlookup formula
In cell A1 and copy down as required
=IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: 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:B31/12/2008,B:B<01/01/2010),A:A,0) |
IF or vlookup formula
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 "Jacob Skaria" wrote: In cell A1 and copy down as required =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: 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:B31/12/2008,B:B<01/01/2010),A:A,0) |
IF or vlookup formula
Hi,
use =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, "") "Rob" wrote: 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 "Jacob Skaria" wrote: In cell A1 and copy down as required =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: 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:B31/12/2008,B:B<01/01/2010),A:A,0) |
IF or vlookup formula
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 ? "Eduardo" wrote: Hi, use =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, "") "Rob" wrote: 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 "Jacob Skaria" wrote: In cell A1 and copy down as required =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),A1, 0) If this post helps click Yes --------------- Jacob Skaria "Rob" wrote: 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:B31/12/2008,B:B<01/01/2010),A:A,0) |
IF or vlookup formula
An easy formulas play which can deliver it for you ..
Source data as posted running in A1:B1 down In C1: =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),ROW (),"") In D1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) )) Copy C1:D1 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" wrote: .. 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 there any way to automatically sort the data ? |
IF or vlookup formula
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" wrote: An easy formulas play which can deliver it for you .. Source data as posted running in A1:B1 down In C1: =IF(AND(B1DATE(2008,12,31),B1<DATE(2010,1,1)),ROW (),"") In D1: =IF(ROW()COUNT(C:C),"",INDEX(A:A,SMALL(C:C,ROW()) )) Copy C1:D1 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" wrote: .. 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 there any way to automatically sort the data ? |
IF or vlookup formula
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,R OWS($1:1)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Rob" wrote: 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 |
IF or vlookup formula
Working fine now. thank you all.
"Max" wrote: 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,R OWS($1:1)))) -- Max Singapore http://savefile.com/projects/236895 Downloads:27,000 Files:200 Subscribers:70 xdemechanik --- "Rob" wrote: 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 |
All times are GMT +1. The time now is 02:56 AM. |
Powered by vBulletin® Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
ExcelBanter.com