Find nearest row above with value?

[Fritz23]

Is there a method/formula to find and return a value that is nearest above to the existing row?

I've seen plenty of examples of finding the first case, but I need the nearest.

See the table below for an example. If the "Type" column/cell is blank, then the "Connect Event" cell should look up until it finds the nearest row above that has the Type cell set to "Event", and return the value in Event Name. The challenge is that the number of rows are dynamic and will vary between the events.

Connected Event	Type	Event Name
First Event	Event	First Event
First Event
Second Event	Event	Second Event
Second Event
Second Event
Second Event

 

[dreid1011]

Maybe something like this:

=IF(B2="Event",C2,TAKE(FILTER($C$2:C2,$C$2:C2<>0),-1))

Book1
	A	B	C
1	Connected Event	Type	Event Name
2	First Event	Event	First Event
3	First Event
4	Second Event	Event	Second Event
5	Second Event
6	Second Event
7	Second Event
8	Second Event
9	Second Event
Sheet3

Cell Formulas
Range	Formula
A2:A9	A2	=IF(B2="Event",C2,TAKE(FILTER($C$2:C2,$C$2:C2<>0),-1))

Edit: One potential issue is if there is an Event Name in Col C without a corresponding Event Type in Col B, it will pick the latest event name either way. I'll have to play with the formula to get around that.

 

[dreid1011]

Edit: One potential issue is if there is an Event Name in Col C without a corresponding Event Type in Col B, it will pick the latest event name either way. I'll have to play with the formula to get around that.

Okay, this seems to work better:

=TAKE(FILTER($C$2:C2,$B$2:B2="Event"),-1)

 

[Fritz23]

Okay, this seems to work better:

=TAKE(FILTER($C$2:C2,$B$2:B2="Event"),-1)

That works beautifully. Thank you very much!

 

[dreid1011]

That works beautifully. Thank you very much!

You're welcome, happy to help.