How to: free text cell, that can also assume a value depending on other cell?

In the workbook, you mention cell C9. However, that is not affected by the drop down selection. I think you mean cell G9, and that is what the code updates.

Right click on the Calculations tab
Select "View Code"
In the Main code pane, paste the following code:

Option Explicit

Private Sub Worksheet_Change(ByVal Target As Range)

' Define and set the "range of interest" = cell E9
Dim rInterest As Range
Set rInterest = Me.Range("E9")

' Check if the Target cell is the range of interest
If Intersect(Target, rInterest) Is Nothing Then Exit Sub
' Check if multiple cells have changed (say, if clearing several cells)
If Target.Cells.CountLarge > 1 Then Exit Sub

Application.EnableEvents = False    ' Stop Event handling
On Error Resume Next                ' Continue if any error(s)

If Target.Value = "" Then
    ' Clear the cell if the Target is empty
    Target.Offset(, 2).Value = ""
Else
    ' Drop the formula into the cell
    With Target.Offset(, 2)
        .Formula = _
            "=IFERROR(VLOOKUP(" & Target.Address & ",Data!B77:D87,3,FALSE),"""")"
        .Value = .Value
    End With ' Target.Offset(, 2)
End If ' Target.Value = ""

On Error GoTo 0                 ' Clear Error handling
Application.EnableEvents = True ' Resume Event handling

End Sub

Please note that you will need to save your workbook as macro enabled (.xlsm or .xlsb). And you will need to enable macros when you open the workbook.

Please see the attached updated sample workbook.