This is absolutely wonderful. You have made my day.

Question -- Why function does "SMALL" do?

"RagDyeR" wrote:

I was hoping you wouldn't ask for that.<bg

Makes for a big formula:

=IF(ISERR(SMALL(IF(Sheet2!$A$1:$A$30=$A$1,ROW($A$1 :$A$30)),ROW(A1))),"",INDE
X(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$30=$A$ 1,ROW($A$1:$A$30)),ROW(A1)
)))

Watch out for word wrap!

--

HTH,

RD
================================================== ===
Please keep all correspondence within the Group, so all may benefit!
================================================== ===

"jh3016" wrote in message
...
This worked like a charm! Now how can I not display the #NUM! error. I
tried conditional formatting the column, but that didn't work.

Thanks a million.

"RagDyeR" wrote:

Try this *array* formula in A2 of Sheet1:


=INDEX(Sheet2!$B$1:$B$30,SMALL(IF(Sheet2!$A$1:$A$3 0=$A$1,ROW($A$1:$A$30)),RO
W(A1)))

And drag down to copy.
--
Array formulas are entered using CSE, <Ctrl <Shift <Enter, instead of
the
regular <Enter, which will *automatically* enclose the formula in curly
brackets, which *cannot* be done manually.

When this formula runs out of matches, it returns a #NUM! error.
--

HTH,

RD
==============================================
Please keep all correspondence within the Group, so all may benefit!
==============================================


"jh3016" wrote in message
...
I have an excel workbook with two sheets.

On Sheet2, in column A is employee name. In column B is comments about
that
employee. The employee's name could be repeated several times throughout
the
workbook. Sheet2 has 1000 rows with multiple employees on it.

On Sheet1 I want to put all the comments that match Employee1 (which is in
cell A1 on Sheet1). There will probably be 20 matches, so I want the
comments to be in rows 2-21.

Is this possible?

Thanks in advance.