Assigning Values to Third Column Based on Values in First Two Columns

Like most people, we use excel for some reporting outside of our normal system depending upon what management wants to know.

Many of the projects we do receive funding from multiple sources - a common question is, if a project receives any funding from a funding source, what are the associated costs and how does this compare with other funding sources assigned to that project.

We do most of this with formula, and should be simple, but having trouble with a basic step to assign projects to a program.

All I want to do for this step is populate a column based on the values in the first two.

As a simple example, if the values in the first two columns were:

And we wanted to develop the data for developing a report involving funding from NW we would want a third column to be populated with values like this:

Thanks

Using SUMPRODUCT()

• Formula used in cell C2

=IF(SUMPRODUCT(--($A$2:$A$13=A2)*--($B$2:$B$13="NW")),"NW","Not Funded")

Or, Using COUNTIFS()

• Formula used in cell C2

=IF(COUNTIFS($A$2:$A$13,A2,$B$2:$B$13,"NW"),"NW","Not Funded")

Or, If you want a spill array formula then use MAP()

• Formula used in cell C2

=MAP(
    A2:A13,
    B2:B13,
    LAMBDA(x, y,
        IF(
            COUNTIFS(
                x:A2, x,
                y:B2, "NW"
            ),
            "NW",
            "Not Funded"
        )
    )
)