Perhaps something along these lines might bring you
to a close approx of the desired end result?

In sheet: "Profit Data"
--------------
Assuming cols AA, AB, etc are all empty columns

Put in AA2: =IF(ISNUMBER(MATCH($A2,Labor!C:C,0)),Labor!C$3,"")

Copy AA2 across by 83* cols to to DE2,
fill down as many rows as there is data in col A
(*to cover the same range as in Labor, cols C to CH)

The above will return in cols AA to DE,
all the names from row3 of sheet: Labor
which match the job# in col A
(and that's the "close approx" results)

As for:

not sure how to return multiple names
into the cell which are separated by a
comma.

Think there was a subroutine recently posted by Bob Phillip
which could then take the results returned in cols AA to DE
and concat it nicely into col Z (I'll post the link later if I can find it
<g)

In the interim, only as a "demo" for the sample data posted
(assuming only cols C and D in "Labor")
you could put the formula below in Z2, and copy down:

=IF(COUNTIF(AA2:AB2,"")=2,"",IF(COUNTIF(AA2:AB2,"" )=1,TRIM(AA2&"
"&AB2),SUBSTITUTE(AA2&" "&AB2," ",", ")))

The above will return the results in Z2: Brian, Bill, and so on

--
Rgds
Max
xl 97
---
GMT+8, 1° 22' N 103° 45' E
xdemechanik <atyahoo<dotcom
----
"Dave" wrote in message
...
I have two worksheets named "Profit data" and "Labor". The Profit data
sheet
includes amongst other things, the following:

Col A
Job #
Row 2 1185
1186
1204
1207
1214
to row 48

The "Labor" sheet has Employee Names and Jobs they worked on. E.G.

Col C Col D etc all the way to Col CH
Row 3 Brian Bill
Row 4 1202 1185
Row 5 1185 1214
etc
to Row 81

As an example In the "Profit data" sheet in Cell Z2 I want to create a
function that takes job 1885 and looks in the labor sheet for 1885 and
then
returns the person's name that appears in Row 3, separated by a comma if
more
than one person worked on the job. E.G. For Job 1885 the function needs to
return Brian,Bill

What is the best way to do this? I thought of using a lookup table but was
not sure how to return multiple names into the cell which are separated by
a
comma.

Thanks.