Update cell based on date criteria VBA

With this macro, you don't need to use the formula. In the workbook you posted, the date in B1 of the "Date" sheet is not found in column A of the "SALES DATA" sheet so no value will be returned to the "Dataset" sheet.

Sub UpdateCell()
    Application.ScreenUpdating = False
    Dim strdate As String, foundDate1 As Range, foundDate2 As Range
    strdate = Sheets("Date").Range("B1")
    Set foundDate1 = Sheets("Dataset").Range("A:A").Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    If Not foundDate1 Is Nothing Then
        Set foundDate2 = Sheets("SALES DATA").Range("A:A").Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
        If Not foundDate2 Is Nothing Then
            foundDate1.Offset(, 1) = foundDate2.Offset(, 1)
        End If
    End If
    Application.ScreenUpdating = True
End Sub

Try:

Sub UpdateCell()
    Application.ScreenUpdating = False
    Dim strdate As String, foundDate As Range
    strdate = Sheets("Date").Range("B1")
    Set foundDate = Sheets("Dataset").Range("A:A").Find(CDate(strdate), LookIn:=xlFormulas, lookat:=xlWhole)
    If Not foundDate Is Nothing Then
        foundDate.Offset(, 1).Formula = "=INDEX('SALES DATA'!B:B,MATCH(Dataset!A" & foundDate.Row & ",'SALES DATA'!A:A,0))"
    End If
    Application.ScreenUpdating = True
End Sub