Last date before a certain date

[m.cuccovillo]

Dear all,
I have been trying a solution to this jigsaw for ages... would save me years of boring work!

Basically I need a cell to return the latest date in a the first column before a certain date, if in the following column there is a "1".

Example:

A1... B..............C...............D
2..................... site1..........site2
3......last...........07-Feb.......03-Feb
4...... next.........13-Feb.......10-Feb
5...... update......09-Feb.......09-Feb
6...... 01-Feb
7...... 02-Feb.......................1
8...... 03-Feb.......................1
9...... 04-Feb
10.....05-Feb.......1
11.....06-Feb
12.....07-Feb.......1
13.....08-Feb
14.....09-Feb
15.....10-Feb.......................1
16.....11-Feb
17.....12-Feb
18.....13-Feb.......1
19.....14-Feb.......1

Basically I would like cell D3 to display the last date where there is a 1 in column D BEFORE the last update, wrote manually in cell D5.
Similar thing for cell D4, which should return the first date where there is a 1 in column D AFTER the last update.

Can anyone help me at all?

Thank you very much!

Michele

 

[DonkeyOte]

There are a few ways you could approach this - MIN & MAX Arrays etc etc, below is just one:

Excel Workbook
	B	C	D
3	last	07-Feb	03-Feb
4	next	13-Feb	10-Feb
5	update	09-Feb	09-Feb
6	01-Feb
7	02-Feb		1
8	03-Feb		1
9	04-Feb
10	05-Feb	1
11	06-Feb
12	07-Feb	1
13	08-Feb
14	09-Feb
15	10-Feb		1
16	11-Feb
17	12-Feb
18	13-Feb	1
19	14-Feb	1
Sheet1

note: with the above approaches you would still want/need to account for possibility of no match.

 

[m.cuccovillo]

Thank you so much for your help! I wasn't even that hopeful that a solution might have existed.

To be more accurate and using my actual spreadsheet, for the cell H18 I have used the formula:
=LOOKUP(9.99E+307,H$21:INDEX(H$21:H$280,MATCH(H$20,$D21:$D280)-1),$D$21:INDEX($D21:$D280,MATCH(H$20,$D21:$D280)-1))

For the cell H19 (which should return the first date after the update date that is in H20), the formula you suggested does not work. But I also have realised that it is because I have originally posted too little information...
Do you reckon is there any formula I can use with my spreadsheet?

Thank you again!

P.S.: The formula should just traces "1s" and not any other sign like "Cs" or whaterver... your H18 cell does it already!

 

[DonkeyOte]

The formula for F19 (adapted from C4 in earlier ex.) should read:

=INDEX(INDEX($D:$D,MATCH(F$20,$D:$D)+1):$D$280,MATCH(1,INDEX(F:F,MATCH(F$20,$D:$D)+1):F$280,0))
copied across

Using the above formula with your (visible) values the above would return the below:

Excel Workbook
	D	E	F	G	H	I
19			#N/A	11-Feb	24-Feb	17-Feb
20			10-Feb	10-Feb	10-Feb	10-Feb
21	03-Jan
22	04-Jan		C
23	05-Jan		C
24	06-Jan			C
25	07-Jan			C
41	31-Jan
42	01-Feb				1
43	02-Feb				1
44	03-Feb
45	04-Feb
46	07-Feb		1
47	08-Feb		1
48	09-Feb
49	10-Feb			1
50	11-Feb			1
51	14-Feb
52	15-Feb
53	16-Feb
54	17-Feb					1
55	18-Feb					1
56	21-Feb
57	22-Feb					1
58	23-Feb
59	24-Feb				1
60	25-Feb				1
Sheet1

Note:

-- the error in F is expected give no 1 appears post 10-Feb in the values I can see.
-- rows 26:40 are grouped/hidden per your screenshot

 

[m.cuccovillo]

It works perfectly! Thank you very much for your invaluable help!