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.
|