A formula won't get you there. You need to be able to query the user Id based on the name. This is a perfect use case for the Before Save Flow. In this flow, you first query the user to use with a Get Records element, then use an Assignment to assign the ID based on the Get Records by name. As a bonus, the Before Save Flow does not trigger a recursive save ...
I am assuming 'ContactId' is a field in your DE.
It should be:
var @opportunityName, @opportunityCloseDate
set @subscriberid = _subscriberkey
set @opportunityName = Lookup("Opportunities","Name","ContactId", @subscriberid)
set @opportunityCloseDate = Lookup("Opportunities","CloseDate&...