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

Question

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:

IDProjectProgramvalue for column D*New Column* does "Value for column D" apply at program level1alpha

Seven Y2bravoSeven Y3Seven YY4HappyfourY

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.

flying008 · Answer

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])

albert_alaluf · Answer

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

my solution.yxmd