Copy INformation from Third Column of Data

[bhenlee]

Hi Experts,

Need your help.

I have 3 columns of data and I need a formula to find the first instance of the word Open in column 2 and then copy the date in Column 3 to the rest of the values in Column 4:


A Open 3/1/2016 3/1/2016
A text 3/2/2016 3/1/2016
A text 3/3/2016 3/1/2016
A text 3/3/2016 3/1/2016

B Open 3/1/2016 3/1/2016
B text 3/2/2016 3/1/2016
B text 3/3/2016 3/1/2016
B text 3/3/2016 3/1/2016
B text 3/4/2016 3/1/2016

So basically all data in column 1 are the same. So for every instance of data in Column 1 - I need to find the first instance of the word "Open" then copy the date associates to it in column 4.

Thank you for all your help experts!!!

[CAntosh]

Will 'Open' always be in first new entry for each letter in column 1?

EDIT:
If yes, put the formula below in D2 and fill down (and up to D1):

=IF(ISBLANK($B2),"",IF($B2 = "Open",$C2,$D1))

The formula below should work regardless of where "Open" falls. Enter it into D1 with a Ctrl + Shift + Enter and fill down:

=INDEX($D:$D,MATCH(1,($A:$A=$A1)*($B:$B="Open"),0))