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:

Ref	Yes or No	Name	Name 2
1		Dave	Dave
5	Yes	Bethan	Bethan
5	Yes	Sonia	Bethan
5	Yes	Hannah	Bethan
4		Ceri	Ceri
3		Hannah	Hannah

<colgroup><col width="81" span="4" style="width:61pt"> </colgroup><tbody>
</tbody>

 

[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