Duplicate Values and Index Match

[mrhuman]

I have a table with Column A and B.
Column A has unique values and B with repetitive or duplicate values.
I used index match (column B) to pull the values to another table but the formula is bringing only the first occurrence repeatedly
[TABLE="width: 300"]
<tbody>[TR]
[TD]A[/TD]
[TD]B[/TD]
[/TR]
[TR]
[TD]Test1[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Test2[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]Test3[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]Test4[/TD]
[TD]10[/TD]
[/TR]
[TR]
[TD]Test5[/TD]
[TD]10[/TD]
[/TR]
</tbody>[/TABLE]

Table2
[TABLE="width: 300"]
<tbody>[TR]
[TD]C[/TD]
[TD]D[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Test1 (=INDEX($A$1:$B$6,(MATCH($C1,$B$1:$B$5,0)),1))[/TD]
[/TR]
[TR]
[TD]9[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]8[/TD]
[TD][/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Test1[/TD]
[/TR]
[TR]
[TD]10[/TD]
[TD]Test1[/TD]
[/TR]
</tbody>[/TABLE]

Hope I explained myself clearly. Thanks

 

[mrhuman]

Mods I mistakenly posted in BI forum pls move to excel questions.