Glad you got something that you like!
While that formula might do what you want it can be improved upon in many ways that will help make it more robust, more versatile and more efficient.
=IF(ISERROR(INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),""),ROW(1:1))),2)),"",(INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),""),ROW(1:1))),2)))
Let's start with the error trap:
It would be more efficient to use a "helper" cell to hold a formula that returns the count of records that meet the criteria. The result of this formula is used to compare the total number of cells the extraction formula is copied to against the total number of records that meet the criteria. If you use this to extract records based on a variable criteria the number of records that are extracted can/will vary. An error will be generated when the number of formulas entered exceeds the number of records that meet the criteria. When this happens, the error will be generated within this expression:
SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),""),ROW(1:1))
So, you only need to trap that expression:
=IF(ISERROR(INDEX($D$1:$E$9,(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),""),ROW(1:1))),2))
Now becomes:
=IF(ISERROR(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9),""),ROW(1:1)))
You don't need to define a value_if_false argument for the IF function. The default of FALSE will be ignored in the SMALL function.
=IF(ISERROR(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROW(1:1)))
When there is no error generated the formula has to "execute" twice. This formula may not look it but it is somewhat calculation instensive, especially on large amounts of data.
The formula first has to see if an error is generated and if not, then basically repeat itself. Since this is already a calculation intensive formula we need to try to eliminate the formula having to execute twice.
That's why it's more efficient to use the helper formula.
Using the helper cell formula that returns the count of records that meet the criteria:
B1: =COUNTIF(D1:D9,A1)
Incorporating this as our error trap:
=IF(ROW(1:1)<=B$1
That is signifincantly more efficinet than:
=IF(ISERROR(SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROW(1:1)))
Ok, let's look at another item.
The use of: ROW(1:1)
Try inserting a new row 1 in the sheet where you have the formula entered and see what happens to your results.
If you will never insert new rows above your data/formula then no harm will be done. However, you can prevent that problem from ever happening with just a very minor tweak that doesn't add any undue complexity or complication to the already complex formula.
Use the ROWS function and use cell references not just row references.
=IF(ROWS(A$1:A1)<=B$1
Try making that very minor change and then try inserting a new row 1 and note the difference. Use cell references in the ROWS function that correspond to the first cell that will hold the formula. For example, if you want the first result to appear in cell G1 then use:
=IF(ROWS(G$1:G1)<=B$1
Use the ROWS function as the K argument in the SMALL function, also.
SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROWS(G$1:G1))
Ok, let's look at another item.
Your formula will work fine as long as the data starts on row 1. If your data was in the range D10:E18 then it wouldn't work. The row numbers that are passed to the SMALL function must correspond to the positions of the indexed array. When the data starts on row 1 this happens naturally but when the data does not start on row 1 then we have to apply an offset correction so the row numbers correspond to the positions of the indexed array.
Again, a minor inexpensive tweak will take care of this and make the formula more versatile.
Some people apply this offset to the ROW function within the SMALL function. Lately, I've been applying this offset to the SMALL function itself. The idea is, calculate a single offset rather than calculating an array of offsets.
SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROWS(G$1:G1))-ROW(E$1)+1
The offset correction is simply subtracting the row number of the starting location of the data then adding 1. For example, your data starts in cell E1 so the offset correction is: -ROW(E$1)+1. If your data started in cell E101 then the offset correction would be: -ROW(E$101)+1
Here's the entire formula with those changes implemented so far:
=IF(ROWS(G$1:G1)<=B$1,INDEX($D$1:$E$9,SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROWS(G$1:G1))-ROW(E$1)+1,2),"")
Ok, one more thing!
Since you're only interested in extracting data from a one column array, just index that single column:
=IF(ROWS(G$1:G1)<=B$1,INDEX($E$1:$E$9,SMALL(IF($D$1:$D$9=$A$1,ROW($D$1:$D$9)),ROWS(G$1:G1))-ROW(E$1)+1),"")
That's it!
Efficient and robust!