Need to identify where column C = cell in column B, and pull value from that row

[JoshuaElphee]

I have a workbook where there are many columns.  Project is the main/primary key for most elements of the workflow, however program may have a row specific to the program, while other projects fall under that value and show the project # as a program.

I need to identify the following solution where a value in "Program" is within  the row "Project":  If within that row, column "D" says "Y", I need to include the value "Y" within a new row, column E for all cells where "Program" is equal to that value from "Project".  *Not all projects have a program, many cells are null in this column. 

I am providing an example below:

ID	Project	Program	value for column D	*New Column* does "Value for column D" apply at program level
1	alpha	Seven		Y
2	bravo	Seven		Y
3	Seven		Y	Y
4	Happy	four	Y

In this example, when a value for "Program" shows in the column "Project", and the project level for the same value ="Y" in "Value for column D", The value in a new column should show as "Y for each value in the new column where "Program" shows that value.

[albert_alaluf]

hi @JoshuaElphee 

Please see my solution. I hope this is what you're looking for.

Please let me know if it works for you.

Albert

[flying008]

Hi, @JoshuaElphee 

FYI.

1- Formula 1st for [Common] field:

IIF(Contains([Concat_Project], [Project]) && Contains([Concat_Program], [Project]), [value for column D], Null())

2- Formula 1st for [Common] field:

IIF(IsEmpty([Common]), [value for column D2], [Common])