Populate Cell based on text in another cell

[NealGowdy11]

	A	B	C	D
1	Project Name	Country	Sub Region	Project Manager
2	Alpha	UK	NE	Homer Simpson
3	Bravo	Russia	EE	Snoop Dogg
4	Charlie	Italy	SE	Brad Pitt
5	Delta	France	CE	Viktor Reznov

<tbody>
</tbody>

I need help...

I would like column D to say:
Homer Simpson whenever column C says NE
Snoop Dogg whenever column C says EE
Brad Pitt whenever column C says SE
Viktor Reznov whenever column C says CE

The conditional formatting would apply to A2:C500.

 

[Joe4]

Conditional Formatting has to do with returning different cell formatting options, not returning different values.

Create a lookup table and use VLOOKUP.
See: https://www.techonthenet.com/excel/formulas/vlookup.php

 

[etaf]

you could setup a lookup list or an nested IF

=IF(C2="NE", "Homer Simpson", if(C2="EE","Snoop Dogg" , IF( C2="SE", "Brad Pitt", IF( C2 = "CE", "Viktor Reznov", "not in list"))))

OR
create a list say in F1 and G1 column rows 1 to 4 - for the 4 codes
F has the codes Ne, EE etc
and G has the names

now you can put
index($G$1:$G$4,match(C2, $F$1:$F$4,0))