Filling in a column based on value in other column

[Fyrefox]

Hi all,

It sounds easy but couldn't find the solution in excel (preferably without macro's).
I have two sheets.
Sheet 1 contains a list.
In sheet 2 I have to fill in the last column, based on the list in sheet 1 (preferably using a formula). First column is just 5 times A,B,C,D.

In sheet 1
[TABLE="width: 500"]
<tbody>[TR]
[TD]A
[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6
[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]3[/TD]
[/TR]
</tbody>[/TABLE]
[TABLE="width: 128"]
<tbody>[TR]
[TD="align: right"][/TD]
[/TR]
</tbody>[/TABLE]

So, in sheet 2 I have to fill in:
[TABLE="width: 500"]
<tbody>[TR]
[TD]A[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]1[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]A[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]7[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]4[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]B[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]9[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]C[/TD]
[TD]No data[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]8[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]12[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]6[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]D[/TD]
[TD]No data[/TD]
[/TR]
</tbody>[/TABLE]


Thank you very much!!

Kind regards,
K.

 

[Fluff]

How about
=IFERROR(INDEX(Sheet1!$B$2:$B$11,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$11=A2),COUNTIF($A$2:A2,A2))),"No data")

 

[Fyrefox]

How about
=IFERROR(INDEX(Sheet1!$B$2:$B$11,AGGREGATE(15,6,(ROW(Sheet1!$A$2:$A$11)-ROW(Sheet1!$A$2)+1)/(Sheet1!$A$2:$A$11=A2),COUNTIF($A$2:A2,A2))),"No data")

A real genius, thank you very very much!!

 

[Fluff]

Glad to help & thanks for the feedback