If and or nightmare

[thedeadzeds]

Hi Guys,

Is there a way to do the following. I basically want to Use the first name from Column C (Name) and populate Column D (Name 2) if the ref in column A is identical to the cell above or below and if column B shows yes. I hope this makes sense. So the formula in column D would look like this:

[TABLE="class: grid, width: 324"]
<colgroup><col width="81" span="4" style="width:61pt"> </colgroup><tbody>[TR]
[TD="width: 81"]Ref[/TD]
[TD="width: 81"]Yes or No[/TD]
[TD="width: 81"]Name[/TD]
[TD="width: 81"]Name 2[/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD] [/TD]
[TD]Dave[/TD]
[TD]Dave[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Bethan[/TD]
[TD]Bethan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Sonia[/TD]
[TD]Bethan[/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]Yes[/TD]
[TD]Hannah[/TD]
[TD]Bethan[/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD] [/TD]
[TD]Ceri[/TD]
[TD]Ceri[/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD] [/TD]
[TD]Hannah[/TD]
[TD]Hannah[/TD]
[/TR]
</tbody>[/TABLE]

 

[Marcelo Branco]

Try

D2 copied down
=IF(B2="Yes",INDEX(C:C,MATCH(A2,A:A,0)),C2)

M.

 

[wideboydixon]

If the Ref unique? If so then:

Book1
	A	B	C	D
1	Ref	Yes or No	Name	Name 2
2	1		Dave	Dave
3	5	Yes	Bethan	Bethan
4	5	Yes	Sonia	Bethan
5	5	Yes	Hannah	Bethan
6	4		Ceri	Ceri
7	3		Hannah	Hannah
Sheet1

Cell Formulas
Range	Formula
D2	=IF(AND($A2=$A1,$B2="Yes"),VLOOKUP($A2,$A:$C,3,FALSE),$C2)

WBD