Return Value Under Blank Above Adjacent Cell

Hi Everyone!

I have a table, identical to the table displayed below that stretches roughly 800 rows. What I am aiming to do is add a formula in column E, beginning in E5 that searches upward from the adjacent cell in A, and return the "Street #" (second value under above blank) listed above it. I would typically just use an offset function, however there could be anywhere from 1 to 50 different names within the sections. There is always a blank in between the sections as displayed below.

Anyone have an idea?

Attendand	Lane	Time	Time Out	Formula
House 1
Street 1
John Smith		7/9/2018	10:00:49	Street 1
Aaron Rogers		6/2/2018	6:00:52	Street 1
Michael Jones		7/1/2018	16:22:22	Street 1
House 2
Street 2
Able Stable		7/4/2018	04:11:11	Street 2
Groot McRoot		5/27/2018	18:00:23	Street 2
House 3
Street 3
Jack Skellington		6/22/2018	03:41:23	Street 3

You have not added your expected outcomes to the table posted above - what and where are they?

Excel 2016 (Windows) 32 bit

	D	E	F	G	H
1		Lane	Time	Time Out	Formula
2
3	House 1
4	Street 1
5	John Smith		07/09/2018	10:00:49	Street 1
6	Aaron Rogers		06/02/2018	06:00:52	Street 1
7	Michael Jones		07/01/2018	16:22:22	Street 1
8
9	House 2
10	Street 2
11	Able Stable		07/04/2018	04:11:11	Street 2
12	Groot McRoot		5/27/2018	18:00:23	Street 2
13
14	House 3
15	Street 3
16	Jack Skellington		6/22/2018	03:41:23	Street 3

Sheet: Sheet1

Excel 2016 (Windows) 32 bit

	H
2	=IF(F2=0,"",IF(LEFT(D1,6)="Street",D1,IF(LEFT(H1,6)="Street",H1,"")))

Sheet: Sheet1

The results ARE presented exactly as in your table above ... How much closer can you get?

I'm sorry, but you are going to need to explain very clearly and specifically what you think is wrong with the solution I've offered.

Or are you saying you want a macro? If so, Ill move this to the correct section.

Particularly when completing this formula it does not fill in each section as displayed above. Instead, it leaves results in H5, H11, and H16 alone with the rest remaining blank. To answer your second question, No - I do not want a macro.

No, it doesn't. What I have shown you works exactly the way you asked. Please look again at my earlier post and at the attachment to this one.

I saw the rep - thank you!

Glad to have helped. I thought I was losing my marbles there for a moment ... You had me seriously worried!!!