Try this:

Code:

`{=INDEX(Sheet1!$E$4:$E$23,SMALL(IF(Sheet1!$B$4:$B$23=B$8,ROW($B$4:$B$23)),ROW()-8))}`

Paste in Sheet2!B9 and copy down.

To understand it, I suggest you copy my original example into a sheet.

Copy this formula into a cell and confirm with Ctrl-Shift-Enter:

=IF($A$1:$A$20=D$1,ROW($B$1:$B$20)

It should answer: FALSE

Click in the formula bar, and hit the F9 key. You will see:

{FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;FALSE;8;FALSE;FALSE;FALSE;12;FALSE;14;FALSE;FALSE;17;FALSE;FALSE;FALSE}

That is the result of the formula. Why? Because it is checking A1:A20 for 1. If there is a 1, it returns the row number. If there isn't a 1, it returns FALSE. Since the first 1 is in A8, it returns 7 falses, and then the 8th row enters an 8, 3 more falses, then row 12, one false, row 14, etc.

So to continue the example, this formula gives the row numbers for rows with a 1 (when a 1 is in cell D1):

=IF($A$1:$A$20=D$1,ROW($B$1:$B$20))

INDEX() returns a certain value in a range. In this case, INDEX is returning a value from the previous array of row numbers, and taking the corresponding row from B1:B20. The SMALL() function will take the nth smallest value from an array, which is determined by ROW()-1 (since we start from row 2, and we want the #1 smallest value), and then look it up in B1:B20:

=INDEX($B$1:$B$20,SMALL(IF($A$1:$A$20=D$1,ROW($B$1:$B$20)),ROW()-1))

Presto.

Hope it makes sense somewhat.