Excel loop for data

Question

Let's assume this is my table

kit component
aa  a
aa  b
aa  c
aa  d
aa  e
ab  b
ab  f
ab  e
ab  l

What I am trying to achieve is some kind of formula that allows me to query the whole table and pull every single component for that particular kit so it would look like this;

kit    
aa

Component    
a
b    
c    
d    
e

Answer 1

If the data is always sorted on kit then the following formula will work:

=IF(ROW(1:1)>COUNTIF(A:A,$D$2),"",INDEX(B:B,MATCH($D$2,A:A,0)+ROW(1:1)-1))

Copy it down as far as desired.

If your data is not sorted you will need to use this formula:

=IFERROR(INDEX($B$1:$B$10,AGGREGATE(15,6,ROW($A$1:$A$10)/($A$1:$A$10=$D$2),ROW(1:1))),"")

Aggregate is an array type formula and therefore full column reference should be avoided. If you data set is large this formula will slow down the calculations times.

It is better to use the first and sort the data, but if not possible the second will work.