Index and Match and Duplicate Values.

[omairhe]

Hello all,

I have an Index and Match Formula that looks like the following:-

=INDEX(Sheet2!A:A,MATCH(Sheet1!A1,Sheet2!B:B,0))

Data in Sheet 2 is :-
Col A......Col B
[TABLE="width: 128"]
<tbody>[TR]
[TD="width: 64"]hello[/TD]
[TD="width: 64"]hello[/TD]
[/TR]
[TR]
[TD]dear[/TD]
[TD]dear[/TD]
[/TR]
[TR]
[TD]my[/TD]
[TD]my[/TD]
[/TR]
[TR]
[TD]what[/TD]
[TD]what[/TD]
[/TR]
[TR]
[TD]is[/TD]
[TD]is[/TD]
[/TR]
[TR]
[TD]going[/TD]
[TD]going[/TD]
[/TR]
[TR]
[TD]on[/TD]
[TD]on[/TD]
[/TR]
</tbody>[/TABLE]



Data in Sheet 1 is :-
Col A......Col B.......Col C
[TABLE="width: 192"]
<colgroup><col width="64" span="3" style="width:48pt"> </colgroup><tbody>[TR]
[TD="width: 64"]hello[/TD]
[TD="width: 64"]hello[/TD]
[TD="width: 64"]hello[/TD]
[/TR]
[TR]
[TD]dear[/TD]
[TD]dear[/TD]
[TD]dear[/TD]
[/TR]
[TR]
[TD]hello[/TD]
[TD]hello[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]my[/TD]
[TD]my[/TD]
[TD]my[/TD]
[/TR]
[TR]
[TD]dear[/TD]
[TD]dear[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]what[/TD]
[TD]what[/TD]
[TD]what[/TD]
[/TR]
[TR]
[TD]what[/TD]
[TD]what[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]is[/TD]
[TD]is[/TD]
[TD]is[/TD]
[/TR]
[TR]
[TD]going[/TD]
[TD]going[/TD]
[TD]going[/TD]
[/TR]
[TR]
[TD]on[/TD]
[TD]on[/TD]
[TD]on[/TD]
[/TR]
[TR]
[TD]on[/TD]
[TD]on[/TD]
[TD][/TD]
[/TR]
</tbody>[/TABLE]


Column A is raw data. Column B is actual result of applying and dragging all the way down the match and index formula. Column C is my imaginary desired results from match and index formula.

So yes. How can I achieve the values in Column C please..

Thanks, will appreciate.

 

[Marcelo Branco]

Try

C1
=IF(COUNTIF(A$1:A1,A1)=1,INDEX(Sheet2!A:A,MATCH(A1,Sheet2!B:B,0)),"")
copy down

M.