First, define three separate dynamic ranges. One for the column
containing the ID, one for the column containing the Date, and one for
the columns containing your values (all five columns). Once you've done
that, try the following...

A1: enter your criteria/numeric value

B1:

=SUM(--(MMULT(--(Values=A1),TRANSPOSE(COLUMN(Values)^0))0))

....confirmed with CONTROL+SHIFT+ENTER

C1, copied down:

=IF(ROWS(C$1:C1)<=$B$1,INDEX(ID,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOSE(
COLUMN(Values)^0)),ROW(ID)-MIN(ROW(ID))+1),ROWS(C$1:C1))),"")

....confirmed with CONTROL+SHIFT+ENTER

D1, copied down:

=IF(ROWS(D$1:D1)<=$B$1,INDEX(Date,SMALL(IF(MMULT(--(Values=$A$1),TRANSPOS
E(COLUMN(Values)^0)),ROW(Date)-MIN(ROW(Date))+1),ROWS(D$1:D1))),"")

....confirmed with CONTROL+SHIFT+ENTER.

In addition, you can also define a reference (Insert Name Define)
for this part of the formula...

MMULT(--(Values=$A$1),TRANSPOSE(COLUMN(Values)^0))

....and replace it with the name you've given it.

Hope this helps!

In article <55bf8a838b3d7@uwe, "Sam via OfficeKB.com" <u4102@uwe
wrote:

Hi Domenic,

Thanks for reply.

I'm not sure what you mean by, "...and Numeric Value". Each row
contains 5 numeric values. Which one do you want? Or do you want the
total?

"...and Numeric Value"
This is the Numeric Criterion that is being matched.

Which one do you want?
The value of the individual Numeric Criterion that is being matched.

To summarise: whenever a match is found in a Row, 3 Cells of data (ID, Date
and Numeric Value) should be Returned to a NEW Row on Sheet2.

Cheers
Sam