How to split a multivalued column, do a lookup and update another column?
I was able to achieve the update of all records my table, using the below set of commands :

DECLARE @MemberNames TABLE(ID VARCHAR(50), Person VARCHAR(255))
INSERT INTO @MemberNames 
SELECT P.ID, u.NAME
FROM dbo.tblProjects AS P 
CROSS apply dbo.fnSplitString(P.TeamMembers, ',') AS S      
     LEFT OUTER JOIN Users u ON s.splitdata = u.EMAILID     

DECLARE @Names TABLE(ID VARCHAR(50), Name VARCHAR(MAX) ) 
INSERT INTO @Names
SELECT t.ID, STUFF(ISNULL((SELECT '; ' + x.Person
            FROM @MemberNames x
           WHERE x.ID = t.ID
        GROUP BY x.Person
         FOR XML PATH (''), TYPE).VALUE('.','VARCHAR(max)'), ''), 1, 2, '') 
    FROM @MemberNames t
    GROUP BY t.ID 
UPDATE tblProjects SET Team = Name FROM tblProjects pr INNER JOIN @Names ON ID = pr.ID


I have a table-valued function fnSplitString for splitting a string based on a delimiter.

p.s. I couldn't get the updates using a single query, as I had mentioned in my question.