Return multiple values

[WAAZUPEE]

I am getting an error when added rows to my formula

If you look at E1-E6 my formula works perfect with 36 rows - but soon as I add more rows to the formula it stops working (H1-H6) (1652 rows)

Am I adding too much data to the spreadsheet for it to work?

Formula with only 36 rows

=IF(ISERROR(INDEX($A$1:$B$36,SMALL(IF($A$1:$A$36=$E$1,ROW($A$1:$A$36)),ROW(1:1)),2)),"",INDEX($A$1:$B$36,SMALL(IF($A$1:$A$36=$E$1,ROW($A$1:$A$36)),ROW(1:1)),2))

Formula with 1652 rows

=IF(ISERROR(INDEX($A$1:$B$1652,SMALL(IF($A$1:$A$1652=$E$1,ROW($A$1:$A$1652)),ROW(1:1)),2)),"",INDEX($A$1:$B$1652,SMALL(IF($A$1:$A$1652=$E$1,ROW($A$1:$A$1652)),ROW(1:1)),2))

Excel 2007

Thanks!

Mike

[Special-K]

This is an array formula.

Formulas entered in column H have not been entered as an array, note the {} characters round the formula in column E.

Reeenter them in column H as an array formula and they work.

[Special-K]

Personally I prefer this shorter array formula which I think does the same thing

=IFERROR(INDEX($A$1:$B$1652,SMALL(IF(($A$1:$A$1652=$E$1),ROW($A$1:$A$1652)),ROW(A1))-(ROW(A$1)-1),2),"")

[63falcondude]

Going off of post #'s 2 and 3, here is an alternative:

=IFERROR(INDEX(B:B,SMALL(IF(A$1:A$1652=E$1,ROW($A$1:$A$1652)),ROWS($1:1))),"") Ctrl Shift Enter

[WAAZUPEE]

Perfect, that was the issue! Thanks so much!