Return multiple matches using index function

Hi,

I.m using index function to return multiple matches and the result is correct. when i try to insert a row before row no 1, the result returns error. pls help.

Thanks
babu

Since you are using Excel 2010, you can shorten the array formula to this:

=IFERROR(INDEX(A:A,SMALL(IF($B$2:$B$8=$E$1,ROW($B$2:$B$8)),ROWS($1:1))),"")

If you insert rows above, the counter - rows($1:1) needs to be re-set to read $1:1

OR
E2=IFERROR(INDEX($A:$A,SMALL(INDEX(($B$2:$B$8<>"Pending")*10^10+ROW($B$2:$B$8),0),ROWS(E$2:E2))),"")

Please Login or Register  to view this content.

Thanks all... its perfect..