duplicate values IF

[The Grim Discovery]

[TABLE="width: 500"]
<tbody>[TR]
[TD][/TD]
[TD]A
[/TD]
[TD]B
[/TD]
[TD]C
[/TD]
[TD]D
[/TD]
[/TR]
[TR]
[TD]1
[/TD]
[TD]United
[/TD]
[TD]Red
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2
[/TD]
[TD]United
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3
[/TD]
[TD]City
[/TD]
[TD]Blue
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4
[/TD]
[TD]United
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5
[/TD]
[TD]City
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]6
[/TD]
[TD]Spurs
[/TD]
[TD]White
[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]7
[/TD]
[TD]Spurs
[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]

Hiya

How do I get Col B to cells to populate with "Red" if the cell in Col A is 'United'? Likewise City in B5 should populate as "Blue"; and B7 should equal "White".

Thanks

 

[KennyGreens]

Hi there, try this:

=IF(A1="United", "Red", IF(A1="City", "Blue", IF(A1="Spurs", "White", "")))

 

[AlanY]

try this in B1 and copy down

=IF(COUNTIF($A$1:A1,A1)=1,LOOKUP(A1,{"City","Spur","United"},{"Blue","White","Red"}),"")

 

[The Grim Discovery]

thanks for your support both in answering my query- just realized it's a bit more complicated than I described. Have started a new thread.