Referencing data in the next row down that has a specific value in an adjacent cell

[Innoguide]

I have a simple 3 column worksheet as follows:

Column 1 contains either Y or N.
Column 2 contains dates
Column 3 needs to contain calculated dates as follows:

If the column 1 cell for a row = "Y", the column 3 cell in that row needs to contain the date from column 2 of that row

If the column 1 cell for a row = "N", the column 3 cell in that row needs to contain the date from column 2 of the next row down that has a "Y" in column 1.

I know how to make it conditional (=if(A2="Y",B2, ----)) but would really appreciate any help you could provide on what formula I need to use in the false argument to provide the date from the next row down that has a Y in column A.

Thanks!

 

[Marcelo Branco]

Hi,

Is this what you need?

A B C
<TABLE style="WIDTH: 160pt; BORDER-COLLAPSE: collapse" border=0 cellSpacing=0 cellPadding=0 width=214><COLGROUP><COL style="WIDTH: 48pt" width=64><COL style="WIDTH: 56pt; mso-width-source: userset; mso-width-alt: 2742" span=2 width=75><TBODY><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: #dce6f1; WIDTH: 48pt; HEIGHT: 15pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 height=20 width=64>Y/N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>Dates</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: #dce6f1; WIDTH: 56pt; BORDER-TOP: windowtext 0.5pt solid; BORDER-RIGHT: windowtext 0.5pt solid" class=xl63 width=75>Calc Dates</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>01/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>01/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>02/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>03/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>03/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>03/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>04/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>04/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>05/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>08/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>06/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>08/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>07/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>08/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>08/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>08/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>N</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>09/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10/01/2011</TD></TR><TR style="HEIGHT: 15pt" height=20><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext 0.5pt solid; BACKGROUND-COLOR: transparent; HEIGHT: 15pt; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl64 height=20>Y</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10/01/2011</TD><TD style="BORDER-BOTTOM: windowtext 0.5pt solid; BORDER-LEFT: windowtext; BACKGROUND-COLOR: transparent; BORDER-TOP: windowtext; BORDER-RIGHT: windowtext 0.5pt solid" class=xl65 align=right>10/01/2011</TD></TR></TBODY></TABLE>

Formula in C2
=IF(A2="Y",B2,INDEX(B2:$B$100,MATCH("Y",A2:$A$100,0)))

copy down

M.

 

[p45cal]

Excel Workbook
	A	B	C
4	Y	01 February 2012	01 February 2012
5	N	01 May 2012	09 April 2012
6	Y	09 April 2012	09 April 2012
7	N	08 February 2012	02 February 2012
8	N	10 November 2011	02 February 2012
9	Y	02 February 2012	02 February 2012
10	N	20 February 2012	06 October 2011
11	N	28 August 2011	06 October 2011
12	N	15 February 2012	06 October 2011
13	Y	06 October 2011	06 October 2011
14	N	08 August 2011	22 October 2011
15	N	24 April 2012	22 October 2011
16	N	18 August 2011	22 October 2011
17	N	08 August 2011	22 October 2011
18	N	22 November 2011	22 October 2011
19	Y	22 October 2011	22 October 2011
20	N	19 September 2011	06 February 2012
21	N	25 November 2011	06 February 2012
22	N	09 October 2011	06 February 2012
23	Y	06 February 2012	06 February 2012
24	N	04 August 2011	30 November 2011
25	Y	30 November 2011	30 November 2011
Sheet24

C4 formula copy down.

 

[Innoguide]

Marcelo - exactly what I need. Thanks so much!

 

[Innoguide]

Thanks P45cal - just what I needed!

 

[Innoguide]

How would I modify this formula if I wanted it to provide the date from the next row up (not down) that has a Y in column A?

In Marcelo's example, the modified formula would need to return 4/1/2011 in C6-C8 instead of 8/1/2011

Thanks again for all your help.

 

[p45cal]

adding a column:

Excel Workbook
	A	B	C	D
4	Y	01-Feb-12	01-Feb-12	01-Feb-12
5	N	01-May-12	09-Apr-12	01-Feb-12
6	Y	09-Apr-12	09-Apr-12	09-Apr-12
7	N	08-Feb-12	02-Feb-12	09-Apr-12
8	N	10-Nov-11	02-Feb-12	09-Apr-12
9	Y	02-Feb-12	02-Feb-12	02-Feb-12
10	N	20-Feb-12	06-Oct-11	02-Feb-12
11	N	28-Aug-11	06-Oct-11	02-Feb-12
12	N	15-Feb-12	06-Oct-11	02-Feb-12
13	Y	06-Oct-11	06-Oct-11	06-Oct-11
14	N	08-Aug-11	22-Oct-11	06-Oct-11
15	N	24-Apr-12	22-Oct-11	06-Oct-11
16	N	18-Aug-11	22-Oct-11	06-Oct-11
17	N	08-Aug-11	22-Oct-11	06-Oct-11
18	N	22-Nov-11	22-Oct-11	06-Oct-11
19	Y	22-Oct-11	22-Oct-11	22-Oct-11
20	N	19-Sep-11	06-Feb-12	22-Oct-11
21	N	25-Nov-11	06-Feb-12	22-Oct-11
22	N	09-Oct-11	06-Feb-12	22-Oct-11
23	Y	06-Feb-12	06-Feb-12	06-Feb-12
24	N	04-Aug-11	30-Nov-11	06-Feb-12
25	Y	30-Nov-11	30-Nov-11	30-Nov-11
Sheet

 

[Aladin Akyurek]

How would I modify this formula if I wanted it to provide the date from the next row up (not down) that has a Y in column A?

In Marcelo's example, the modified formula would need to return 4/1/2011 in C6-C8 instead of 8/1/2011

Thanks again for all your help.

In C2 enter and copy down:

=IF(A2="Y",B2,LOOKUP(9.99999999999999E+307,1/($A$1:A1="Y"),$B$1:B1))

 

[Innoguide]

Thanks Aladin - worked exactly as needed!

You guys all make this look easy

M

 

[T. Valko]

How would I modify this formula if I wanted it to provide the date from the next row up (not down) that has a Y in column A?

In Marcelo's example, the modified formula would need to return 4/1/2011 in C6-C8 instead of 8/1/2011

Thanks again for all your help.

Try this...

=IF(A2="y",B2,LOOKUP(2,1/(A$1:A1="y"),B$1:B1))