Lookup value with same criteria

[ryan0521]

Happy new year!

Can anyone help me with this one.

I want to separate values base on their criteria, please see below for the data.

Look up value	Criteria
100	Apple
101	Apple
102	Apple
103	Apple
200	Ball
201	Ball
202	Ball
203	Ball
204	Ball
300	Cat
301	Cat
302	Cat

<tbody>
</tbody>

And I want to have a result like this

	Apple	Ball	Cat
1	100	200	300
2	101	201	301
3	102	202	302
4	103	203
5		204

<tbody>
</tbody>

Thanks in advance

 

[Aladin Akyurek]

Look up value	Criteria		4	5	3
100	Apple		apple	ball	cat
101	Apple		100	200	300
102	Apple		101	201	301
103	Apple		102	202	302
200	Ball		103	203
201	Ball			204
202	Ball
203	Ball
204	Ball
300	Cat
301	Cat
302	Cat

<tbody>
</tbody>

In D1 enter and copy across:

=COUNTIFS($B$2:$B$13,D$2)

In D3 control+shift+enter, not just enter, copy across, and down:

=IF(ROWS(D$3:D3)>D$1,"",INDEX($A$2:$A$13,SMALL(IF($B$2:$B$13=D$2,ROW($A$2:$A$13)-ROW($A$2)+1),ROWS(D$3:D3))))

 

[ryan0521]

Thank you so much. Happy new year.

 

[Aladin Akyurek]

Thank you so much. Happy new year.

You are welcome. Likewise...