You need to replace ROW(1:1) with something else for robustness. The
idiom is not efficient when the range to process is very large, a point
I felt to point out at some occasions as in http://tinyurl.com/b6zk7.

Biff wrote:
Hi!

A couple of pointers:


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


There's no need to index both columns A and B since you're only interested
in and going to return data from a single column.

Also, you don't need to include the Index at all in the error trap. Any
error generated by the formula will come from this portion:

SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7)),ROW(1:1))

So, with those suggestions the formula would be:

IF(ISERROR(SMALL(IF($A$1:$A$7=$A$10,ROW($A$1:$A$7) ),ROW(1:1))),"",INDEX($A$1:$A$7,SMALL(IF($A$1:$A$7 =$A$10,ROW($A$1:$A$7)),ROW(1:1))))

Biff

"Ashish Mathur" wrote in message
...

Hi,

Assuming your list is in range A1:B7, enter the serial number for whivh
you
want the data returned in A10. Now enter the following array formula
(Ctrl+Shift+Enter) in B10 and copy downwards

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

Regards,

Ashish Mathur

"Jim" wrote:


If I have a worksheet with serial number and date, how can I lookup the
entries from another sheet and display ALL the entrys.

1234 01/08/2005
1235 02/08/2005
1236 04/08/2005
1234 06/09/2005
1236 03/09/2005
1236 10/10/2005

For example on a second sheet if I look up the serial I need this sheet
to
display ALL entries relating to that serial?
i.e
1234 01/08/2005
06/09/2005

or
1238 04/08/2005

or
1236 04/08/2005
03/09/2005
10/10/2005

etc









--

[1] The SumProduct function should implicitly coerce the truth values to
their Excel numeric equivalents.
[2] The lookup functions should have an optional argument for the return
value, defaulting to #N/A in its absence.